Export worksheets from Access to Excel

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a table with about 8,000 records. The key field is Region. There are
approximately 10 regions. I would like to generate a spreadsheet for each
region i.e. Mysheet01, Mysheet02, etc. Is there a way I can automate this
function? Should the number of regions increase/decrease to 8/12, I do not
want to have to change the code to generate these spreadsheets. Any help
would be appreciated.
 
Hi, I would do in this way:
I assume region is a number

set rec=currentdb.openrecordset("select region from your table group by
region",dbopendynaset)
docmd.setwarnings false
do while not rec.eof
docmd.runsql("select your source fields into tmp from your table where
region=" & rec![region] )
' so you create a tmp table containing the values of the first region
DoCmd.TransferSpreadsheet acexport, acSpreadsheetTypeExcel8
, "tmp", "the path where you wanna save your files\mysheet" & rec![region] &
".xls"
rec.movenext
loop
docmd.setwarnings true
msgbox "job done"

HTH Paolo
 
Back
Top