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

Status
Not open for further replies.

lookingforK

Registered User.
Local time
Today, 14:14
Joined
Aug 29, 2012
Messages
48
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? :banghead:
Does the method DoCmd.OutputTo acOutputQuery only work for 1 query data?


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

Please don not double post, it won't get you a quicker response it will just get you off side with those likely to help.
 
Status
Not open for further replies.

Users who are viewing this thread

Back
Top Bottom