Error 9 subscript out of Range

[vba317]

I am running excel 2003. I typically get this error if the spreadsheet has been created and for some reason no data is populating the spreadsheet. I was hoping to get help on how to trap the error, so I can still save the bad spreadsheet, so the code will continue and keep on processing. The highlighted code is causing the error. The variables rstRpts![tabnm], strNewWkbk and iRpt have legitimate values in them, so I don't know where to proceed. Any help is appreciated.

Tom

 ' Get list of reports for this print set
            strSQL = "SELECT ord,tabnm,fname FROM PROC_TableOfContents ORDER BY ord;"
            Set rstRpts = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)
            If Not rstRpts.EOF Then
                With rstRpts
                    .MoveLast
                    .MoveFirst
                End With
                For iRpt = 1 To rstRpts.RecordCount ' Cycle through reports
                    goXL.Workbooks.Open Filename:=(strSrcPath) & (rstRpts![fname]) ' Open Report
                    ' *** Get number of pages and update list
                    ' Count Pages in Report
                    iPageCnt = ActiveSheet.HPageBreaks.Count + 1
                    ' Update with Pages
                    strSQL = "UPDATE PROC_TableOfContents SET pgs = " & (iPageCnt) & " WHERE ord = " & (rstRpts![ord]) & ";"
                    CurrentDb.Execute strSQL
                    ' Copy to print set and close
                    With goXL
           [Blue]             .Sheets("" & (rstRpts![tabnm]) & "").Copy After:=Workbooks(strNewWkbk).Worksheets((iRpt + 2)) [/Blue]
                        .Workbooks(2).Close SaveChanges:=False
                    End With
                    rstRpts.MoveNext
                Next iRpt
            End If
            rstRpts.Close
            Set rstRpts = Nothing

 

[PHV]

At least, replace this:
iPageCnt = ActiveSheet.HPageBreaks.Count + 1
with this:
iPageCnt = [!]goXL.[/!]ActiveSheet.HPageBreaks.Count + 1

and this:
.Sheets("" & (rstRpts![tabnm]) & "").Copy After:=Workbooks(strNewWkbk).Worksheets((iRpt + 2))
with this:
.Sheets("" & (rstRpts![tabnm]) & "").Copy After:=[!].[/!]Workbooks(strNewWkbk).Worksheets((iRpt + 2))

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[vba317]

I just noticed that tabnm did not have a Dim statement with it. I added that and the report continued. Now I am getting an error 50290 Method Close of Object Workbook Failed. The highlighted code is

.Workbooks(2).Close SaveChanges:=False

 

[PHV]

Did you apply the modifications I suggested 20 Mar 13 10:01 ?

 

[vba317]

Yes, I did apply those modifications.

 

[PHV]

I think you wanted this:
.ActiveWorkbook.Close SaveChanges:=False

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[vba317]

Thought I would add the Dim statements
Dim strSQL As String
Dim rstPQ As Recordset
Dim rstRpts As Recordset
Dim iQ As Integer
Dim iRpt As Integer
Dim strPathName As String
Dim strSrcPath As String
Dim strSaveName As String
Dim strNewWkbk As String
Dim iPageCnt As Integer
Dim iRw As Integer
Dim strMenuItm As String
Dim iPgBrkCnt As Integer
Dim tabnm As String


Also iRpt = 24
rstRpts.RecordCount=26
iPageCnt = 4
ord =24

 

[vba317]

I am noticing that when I get the error I have two similar named spreadsheets open at the same time. Could this cause the Error 9 if VBA got confused on which spreadsheet to put the data in? If so what wold fix it?