Subscript out of range

[Prattdakota]

I'm writing VBA code within Access that will open two Excel workbooks and then move a sheet from one book to the other book. Then it will close both workbooks and close the Excel object. The first time I run the code it works fine. Then if I try to run the code a second time I get a "subscript out of range" error on the statement with the .move method. Excel appears to be closing properly after the code runs the first time. If I completely close Access after running the code and then open Access and run the routine again I will not get the error.

I'd be grateful for any suggestions you could provide. I'm quite frustrated by this issue.

Thanks

Sub Test()

    Dim objExcelApp As Excel.Application
    Dim objWorkbook As Excel.Workbook
    Dim objWorksheet As Excel.Worksheet

        Set objExcelApp = New Excel.Application
        With objExcelApp
            .Visible = True
            Set objWorkbook = .Workbooks.Open(DBPath & _
                                "9-column summary.xls", , False)
            Set objWorkbook = .Workbooks.Open(DBPath & "Temp.xls", , False)
[b]            Workbooks("Temp.xls").Sheets("AdminProjection").Move _
                    before:=Workbooks("9-column summary.xls").Worksheets(1)[/b]
            Set objWorkbook = Workbooks("9-column summary.xls")
         End With
        
        objWorkbook.Close True, DBPath & "9-column summary.xls"
        Set objWorkbook = Workbooks("Temp.xls")
        objWorkbook.Close False
        objExcelApp.Quit
        
    Set objExcelApp = Nothing
    Set objWorkbook = Nothing
    Set objWorksheet = Nothing
    
End Sub

 

[PHV]

Always qualify all the Excel objects, eg:
[highlight].[/highlight]Workbooks("Temp.xls").Sheets("AdminProjection").Move _
before:=[highlight].[/highlight]Workbooks("9-column summary.xls").Worksheets(1)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Prattdakota]

Hey PH. Thanks for the good advice. That did fix the issue. It should have been obvious to me given my statement was within a With..End With construct. I'm not real clear on why it works the first time but not the second but I really appreciate the info.

 

[PHV]

Using non qualified Workbooks object instantiate a new instance of Excel not released at the end of the sub.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886