VBA to send Access tables to individual worksheets in Excel...

[kyleno]

Hi all,

I was wondering if there was some VBA code that could be used to send tables from Access to multiple tabs to an existing Excel document?
Or create a new document. I suppose either would work.

Please and thank you.


Also posted in Excel forum here: https://www.mrexcel.com/forum/excel...-access-tables-populate-worksheets-excel.html

 

[ranman256]

yes, usage:
docmd.TransferSpreadsheet acExport ,acSpreadsheetTypeExcel12,vQry, vFile, true,vSheetName

sub XportXL()
dim vQry , vFile

vFile = "c:\temp\MyFile.xls"

for i = 1 to 3
  vQry = "qsMyQuery" & i
  docmd.TransferSpreadsheet acExport ,acSpreadsheetTypeExcel12,vQry, vFile, true,vQry
next 
end sub

NOTE: if tab1 exists and you export to tab1 again, it does not erase the existing tab1 data but rather
if tab1 had 1000 record already
and you export 500 records, then the 1st 500 will be new, but the last 500 will remain. you still have 1000.