Export Access queries to excel

  • Thread starter Thread starter Stockwell43
  • Start date Start date
S

Stockwell43

Hello,

I found a database that I downloaded which exports access tables to an excel
work book creating a separate worksheet. It seems to work great but when I
try to change the names of the tables to the names of the queries, it gives
me the error msg. Here is the code in the click event:

Private Sub cmdExport_Click()

On Error GoTo Do_Nothing

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel7,
"inventory", txtExportFile
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel7,
"inventory_details", txtExportFile
'DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel7,
"qryprocessorname", txtExportFile
'DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel7,
"qryprocstatus", txtExportFile

MsgBox "The tables have been successfully exported to " & txtExportFile
& "."

Exit Sub

Do_Nothing:
MsgBox "Export has failed. An error occurred or the user terminated the
operation."

End Sub

Also, if someone can help with doing this with the queries instead of the
tables, could it be set up to delete the old data and export the new each
time the button is clicked? In other words, each day I click the button,
override the data from the day before.

Thanks!!!!!
 
Ok, I did get it to work. I forgot to import the table the queries pull from.
However, I would like to know how to delete the old to export the new.

Thanks!
 
Back
Top