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
LVL 1
gdunn59Asked:
Who is Participating?
 
Martin LissOlder than dirtCommented:
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
 
Martin LissOlder than dirtCommented:
I would seem that you don't have two worksheets. Is that possible?
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
gdunn59Author Commented:
No it definitely has 2 sheets.  I double checked.
0
 
NorieVBA ExpertCommented:
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 LissOlder than dirtCommented:
You're welcome and I'm glad I was able to help.

Marty - MVP 2009 to 2012
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.