exporting access tables to excel

Question

Hi I am trying to use the following code to export tables from access to excel

I am getting error object does not support property or method at the end

 Set objexcel = New Excel.Application
   objexcel.Visible = True

   If Dir("C:\reports\Data_Analysis1.xls") = "" Then
   objexcel.Workbooks.Add
   Set wbexcel = objexcel.ActiveWorkbook
   Set objSht = wbexcel.Worksheets("Sheet1")
   Else
   Set wbexcel = objexcel.Workbooks.Open("C:\Documents and Settings\TAYYAPP\Desktop\test folder\reports\ERROR REPORT4.xls")
   Set objSht = wbexcel.Worksheets("Sheet1")
   End If

   objSht.Activate


   objexcel.DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "UTYP_Anzahl", "C:\Reports\Data_Analysis1.xls", True

Answer 1

DoCmd as in "DoCmd.TransferSpreadsheet" is an MS Access command, not an excel command. I think that is your problem.

If this VBA is running inside MS Access then just refer to it as

DoCmd.TransferSpreadsheet

Not

objexcel.DoCmd.TransferSpreadsheet

Also, if that is what you're doing, you don't need to manually create an excel file with Execl Automation, Access will do that for you.

Apologies if I have misunderstood what you're doing, but I think all you need is the TransferSpreadSheet command.

Answer 2

This answer works with the Excel object and can be helpful, as it allows you to automatically format the excel file.