Why I got "Run-time error '9': Subscript out of range" when using acOutputQuery? (1 Viewer)

[lookingforK]

Why I got "Run-time error '9': Subscript out of range" when using acOutputQuery?

Hi,

I am trying to export 2 queries' data (see the below) to a template Excel file [Sales Template.xlsx] through a temp Excel file [Temp.xls].
* Query 1: [Store Performance]
* Query 2: [Individual Performance]

The data transferring should be like:
1). Export the data of Query 1 [Store Performance] to the tab "Data" of [Temp.xls]; export the data of Query 2 [Individual Performance] to the tab "Summary" of [Temp.xls]
2). Use VLOOKUP to transfer the data from the tab "Data" & the tab "Summary" to the template Excel file [Sales Template.xlsx]
3). Save the template Excel file with a pre-defined new name


However, when using the following code, I got "Run-time error '9': Subscript out of range"
...
DoCmd.OutputTo acOutputQuery, "Store Performance", acFormatXLS, ReportPath & "\Temp.xls"
DoCmd.OutputTo acOutputQuery, "Individual Performance", acFormatXLS, ReportPath & "\Temp.xls"

Set wkbDest = xl.Workbooks.Open(ReportPath & "\Sales Template.xlsx")
Set wkbSource = xl.Workbooks.Open(ReportPath & "\Temp.xls")

wkbDest.Sheets("Data").Range("A1:Z21").Value = wkbSource.Sheets("Store Performance").Range("A1:Z21").Value
wkbDest.Sheets("Summary").Range("A1:Z2").Value = wkbSource.Sheets("Individual Performance").Range("A1:Z2").Value
...


When using the code below only to transfer 1 tab (i.e. "Data"), no error message popped up and it could be done:
...
DoCmd.OutputTo acOutputQuery, "Store Performance", acFormatXLS, ReportPath & "\Temp.xls"

Set wkbDest = xl.Workbooks.Open(ReportPath & "\Sales Template.xlsx")
Set wkbSource = xl.Workbooks.Open(ReportPath & "\Temp.xls")

wkbDest.Sheets("Data").Range("A1:Z21").Value = wkbSource.Sheets("Store Performance").Range("A1:Z21").Value
...


Why did I get "Run-time error '9': Subscript out of range" when transferring 2 queries' data but it was OK for exporting only 1 query's data?

Does the method DoCmd.OutputTo acOutputQuery only work for 1 query data?


Thank you in advance!

 

[AccessMSSQL]

Re: Why I got "Run-time error '9': Subscript out of range" when using acOutputQuery?

Try using Docmd.TransferSpreadsheet instead of OutputTo. You can't OutputTo the same excel file twice.