DoCmd.TransferSpreadsheet issue

[RagonichaFulva]

Hello,

I am trying to make a code that makes a query, exports it to a new excel file and then makes another query and adds the results in a new worksheet.

I am beginning my code the following way:

Sub EXCEL_TEST()
Dim ExcelPath As String
Dim ExcelSheet As Object
    Set ExcelSheet = CreateObject("Excel.Sheet")
    ExcelPath = "\\MATEUC4\GCSS_RPS_Mirror_Tool_PRODUCTION\Reports\RPS_Cases_Monthly_Report_" & Format(Now() - 1, "yyyymm-mmmyyyy") & ".xlsx"
    Debug.Print ExcelPath

ExcelSheet.Application.Visible = True
ExcelSheet.SaveAs ExcelPath

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "querystorestest", ExcelPath

ExcelSheet.Application.Quit
 
Set ExcelSheet = Nothing

End Sub

Unfortunately, MS Access sends a 3011 Run-time error says that it can't find the object "querystorestest", but the query is in the database...

I would much appreciate your assistance.

 

[lameid]

You do not need to automate Excel to export to an Excel file. Docmd.transferspreadsheet does everything. That is assuming of course you are running VBA from Access. BTW there are a number of Access specific forums for the various object types.

The constant to export XLSX files is acSpreadsheetTypeExcel12Xml, in case you are trying to export an Excel 2007 file.

Assuming your code is in Access, check the spelling of "querystorestest".

 

[RagonichaFulva]

Thank you for your reply lameid,

I am actually working on Access. The goal is to add sheets to a newly created excel file adding the results of various access queries.

I checked the spelling of "querystorestest" (I actually did a copy-paste of the name) but I get the run-time error.

I'll search for the access specific forums for this object type.

 

[lameid]

If you have the Excel object in code, you might not have a reference set to Excel which can cause problems. Also you can specify an output range to Excel... I believe it you put a $ at the end of the range it is a sheet so "Sheet1$" is a string to export to Sheet1. No need to automate Excel.

 

[RagonichaFulva]

Hello Lameid,

I have been testing and the fact is that the problem is that I am complicating too much the code.

I simplified the code to just:

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "querystorestest", "C:\\Test.xls"

And it works perfectly without creating an excel object.

That's a big lesson for me: KISS ("Keep It Simple Stupid").

Thanks for your efforts!