Export Access Table to Excel Temple using macro

[griffibr]

How would I generate a macro to export an access table or query to an existing excel template.

And, suppose I want to export two table to the same excel file, just different tabs. Can this be done using the macro option in access?

Thank you,

Bryant

 

[xenou]

Sounds like an invalid path all right.

What's the actual, full correct path to your file?

Sub GetMyPath()
'Run in Excel workbook in a folder of your choice.
'Be sure the file has been saved at least once.
     MsgBox ActiveWorkbook.FullName
End Sub

It might be something like:
C:\Documents and Settings\username\My Documents\Test.xls

Do yourself a favor, test the code out like this:

Public Sub ExportTblToExcel()
   strFullPath = "C:\TestExportToExcel.xls"
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel7, "tblCars", strFullPath, False
    MsgBox ("Export Complete")
End Sub

At least see if it works!

Also try:

Public Sub ExportTblToExcel()
   strFullPath = "C:\TestExportToExcel.xls"
    DoCmd.TransferSpreadsheet acExport, , "tblCars", strFullPath, False
    MsgBox ("Export Complete")
End Sub

Leaving the 2nd argument at its default value, which would be acSpreadSheetTypeExcel8 (or 9).

Edit: If you have a table called tblCars, you should now have a file called TestExportToExcel.xls on your C: drive. But please don't delete any other files on the C: drive - windows keeps some crucial files there. As a rule, I don't save things there and normally I'd create at least a temp folder for such a file. We need to get a successful routine here though!

 

[mdfromwell]

Hi,

I'm an accountant by trade, not a programmer, so I didn't quite understand the whole thread. But I have a similar problem. I managed to create the following VB code that extracts my multiple tables to one Excel File:

Public Sub ExportTblToExcel()
strFullPath = "C:\Documents and Settings\dupuism3\Desktop\Details.xls"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel7, "A561_All", strFullPath, False
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel7, "A561_DG", strFullPath, False
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel7, "A561_NCASS", strFullPath, False
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel7, "A561_OSS", strFullPath, False
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel7, "A561_PTPS", strFullPath, False
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel7, "A561_RPT 1", strFullPath, False
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel7, "A561_RPT 2", strFullPath, False
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel7, "A561_RPT 3", strFullPath, False
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel7, "A561_RPT 4", strFullPath, False
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel7, "A561_TAS", strFullPath, False
End Sub

​

However, I can't seem to be able to run it from the Access Macro using the "Runcode" command. I've done some research, and read that the "Runcode" command will only run Function procedures and that I have to write my Sub procedure as a Function procedure - except that none of the literature I've seen tells me how to do this.

Thank you.

 

[mdfromwell]

nevermind, i figured it out.

is there a way to format the table in excel using the macro i created in access? i need to create a subtotal for any changes in a specific field (like the "subtotal" option in excel) but i'd prefer to do it at the same time as the export.

 

[dhen21dx]

Hi,

I would like to know if its possible to customized the Filename of exported Excel. for example i want to have a monthly filename. 01 Jan (Filename), 02 Feb (Filename), etc....

 

[venkataraman312]

Hi,

I am in need to export a particular table say "AAA" from multiple access databases with file names "BBB (1), BBB (2)".....etc., into an excel worksheet with tall the tables appended.

It would be nice if someone can help me.

Regards