Results 1 to 4 of 4
  1. #1
    lookingforK is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Oct 2012
    Posts
    31

    Question 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"
    Code:
    ...
    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:
    Code:
    ...
    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!

  2. #2
    ssanfu is offline Master of Nothing
    Windows XP Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    If you run just this code, do you get errors?
    Code:
    ....
    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("Summary").Range("A1:Z2").Value = wkbSource.Sheets("Individual Performance").Range("A1:Z2").Value
    ....

  3. #3
    lookingforK is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Oct 2012
    Posts
    31
    Thank you ssanfu,

    If I run just the code you mentioned, I don't get errors.

    An expert told me that: I can't use DoCmd.OutputTo to export the 2 queries' data to the same excel file twice. Need to use {DoCmd.TransferSpreadsheet} ...

  4. #4
    ssanfu is offline Master of Nothing
    Windows XP Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Since they work individually, you could try breaking them up:
    Code:
    ...
    ' only destination open once
    Set wkbDest = xl.Workbooks.Open(ReportPath & "\Sales Template.xlsx")
    
    ' 1st spreadsheet
    DoCmd.OutputTo acOutputQuery, "Store Performance", acFormatXLS, ReportPath & "\Temp.xls"
    
    Set wkbSource = xl.Workbooks.Open(ReportPath & "\Temp.xls")
    wkbDest.Sheets("Data").Range("A1:Z21").Value = wkbSource.Sheets("Store Performance").Range("A1:Z21").Value
    ...
    
    ' Now the 2nd spreadsheet
    ....
    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("Summary").Range("A1:Z2").Value = wkbSource.Sheets("Individual Performance").Range("A1:Z2").Value
    ....

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 4
    Last Post: 02-13-2013, 02:35 PM
  2. "Subscript out of range" Error
    By yes sir in forum Access
    Replies: 21
    Last Post: 08-16-2012, 08:02 PM
  3. Replies: 4
    Last Post: 07-25-2012, 04:01 AM
  4. Replies: 3
    Last Post: 06-09-2012, 08:14 AM
  5. Replies: 2
    Last Post: 12-02-2010, 02:35 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums