• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 570
  • Last Modified:

Getting Subscript Out of Range Error When

I am getting "Subscript Out of Range" with the following code:

If DCount("*", "tblErrorDetails (for Report)") > 0 Then
xlObj.Workbooks.Add Templatefile
    With xlObj    
    .Worksheets(1).Select
    .Range("A1").Select
    Set qdf = CurrentDb.QueryDefs("qryErrorDetails_Summary")
    
    Set rs = qdf.OpenRecordset
    .Selection.CopyFromRecordset rs
    
    .Worksheets(2).Select
    .Range("A5").Select
    Set qdf2 = CurrentDb.QueryDefs("qryErrorDetails")
    qdf2.Parameters("[Forms]![frmReports]![txtBeginDT]") = [Forms]![frmReports]![txtBeginDT]
    qdf2.Parameters("[Forms]![frmReports]![txtEndDT]") = [Forms]![frmReports]![txtEndDT]
    
    Set rs2 = qdf2.OpenRecordset
    .Selection.CopyFromRecordset rs2
    
rs.Close

Open in new window



The error is happening on the following line:

   .Worksheets(2).Select

What is wrong?

Thanks,
gdunn59
0
gdunn59
Asked:
gdunn59
  • 3
  • 2
  • 2
  • +2
1 Solution
 
Martin LissRetired ProgrammerCommented:
I would seem that you don't have two worksheets. Is that possible?
0
 
Martin LissRetired ProgrammerCommented:
I guess that you might be creating the sheets in your code. If so then to see how many sheets you have at the point of the error you can add this line just before it.

MsgBox .Worksheets.Count
0
 
Rey Obrero (Capricorn1)Commented:
it would seem that the template you were using have only one sheet

If DCount("*", "tblErrorDetails (for Report)") > 0 Then
xlObj.Workbooks.Add Templatefile
    With xlObj    
    .Worksheets(1).Select
    .Range("A1").Select
    Set qdf = CurrentDb.QueryDefs("qryErrorDetails_Summary")
    
    Set rs = qdf.OpenRecordset
    .Selection.CopyFromRecordset rs
    
'add this line
     .worksheets.add after:=.worksheets(1)

    .Worksheets(2).Select
    .Range("A5").Select
    Set qdf2 = CurrentDb.QueryDefs("qryErrorDetails")
    qdf2.Parameters("[Forms]![frmReports]![txtBeginDT]") = [Forms]![frmReports]![txtBeginDT]
    qdf2.Parameters("[Forms]![frmReports]![txtEndDT]") = [Forms]![frmReports]![txtEndDT]
    
    Set rs2 = qdf2.OpenRecordset
    .Selection.CopyFromRecordset rs2
    
rs.Close

Open in new window

0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
gdunn59Author Commented:
No it definitely has 2 sheets.  I double checked.
0
 
NorieData ProcessorCommented:
What happens if you don't use the template?
If DCount("*", "tblErrorDetails (for Report)") > 0 Then
    xlObj.Workbooks.Add

    With xlObj
        Set qdf = CurrentDb.QueryDefs("qryErrorDetails_Summary")

        Set rs = qdf.OpenRecordset
        
        .Worksheets(1).Range("A1").CopyFromRecordset rs
        
        rs.Close

        Set qdf2 = CurrentDb.QueryDefs("qryErrorDetails")
        qdf2.Parameters("[Forms]![frmReports]![txtBeginDT]") = [Forms]![frmReports]![txtBeginDT]
        qdf2.Parameters("[Forms]![frmReports]![txtEndDT]") = [Forms]![frmReports]![txtEndDT]

        Set rs2 = qdf2.OpenRecordset
        
        .Worksheets(1).Range("A5").CopyFromRecordset rs2
        
        rst.Close

    End With

Open in new window

Or you create a reference to the new workbook when you add it and use that in the rest of the code.
    If DCount("*", "tblErrorDetails (for Report)") > 0 Then
        Set wbNew = xlObj.Workbooks.Add(Templatefile)

        With wbNew

            Set qdf = CurrentDb.QueryDefs("qryErrorDetails_Summary")

            Set rs = qdf.OpenRecordset
            
            .Worksheets(1).Range("A1").CopyFromRecordset rs
            
            rs.Close

            Set qdf2 = CurrentDb.QueryDefs("qryErrorDetails")
            qdf2.Parameters("[Forms]![frmReports]![txtBeginDT]") = [Forms]![frmReports]![txtBeginDT]
            qdf2.Parameters("[Forms]![frmReports]![txtEndDT]") = [Forms]![frmReports]![txtEndDT]

            Set rs2 = qdf2.OpenRecordset

            .Worksheets(2).Range("A5").CopyFromRecordset rs2

            rs2.Close
        End With

Open in new window

0
 
Rory ArchibaldCommented:
Is there any code in the workbook template?
0
 
Rey Obrero (Capricorn1)Commented:
what do you have in worksheet 2 ? a chart or something else?

try using

       .Sheets(2).select

instead of .worksheets(2)
0
 
gdunn59Author Commented:
By putting this line of code in, it did show that there was only 1 sheet which triggered my brain and I realized that I had changed the Template that was on my hard drive, but the code was looking at a Template on a Network drive/folder.  The Template on my hard drive did contain 2 sheets, but because it was looking at the one on the Network, it failed.  Once I copied the revised/correct Template to the Network, it worked fine.

Thanks,

gdunn59
0
 
Martin LissRetired ProgrammerCommented:
You're welcome and I'm glad I was able to help.

Marty - MVP 2009 to 2012
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 2
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now