Intermittent Run-time Error 91: Object Variable or With Block variable not set

Hi all.

I'm a pretty new VBA user, hoping to appeal to you gurus out there for some help.

I'm using the following code to export an access query to excel, open an existing macro enabled workbook which contains a macro i need to modify the exported query, run that macro on the export, then import the modified query back into access as a new table. It works every other time. but alternately, the line "ActiveWorkbook.Close SaveChanges:=True" throws a run time error. I don't really understand why it works every odd run and fails every even run. Any help would be appreciated!

Here's my Access VBA code:

Private Sub RateGenerator_Click()
        Dim xlApp As Excel.Application
        Dim xlWB As Excel.Workbook
        Dim xlWS As Excel.Worksheet
        Set xlApp = New Excel.Application
        Dim rng As Excel.Range
        Dim CurrentSQL As String
        
         
        With xlApp
            .Visible = True
            Set xlWB = .Workbooks.Open("C:\documents and settings\nfranciose\desktop\qryFORECAST", , True)
        End With
 
        DoCmd.OutputTo acOutputQuery, "qryFORECAST", acFormatXLSX, "C:\TEMP\Export.XLSx", True
       
        xlApp.Run "qryFORECAST.xlsm!RateFinder"
        
        
        xlWB.Close (True)
        
       ActiveWorkbook.Close SaveChanges:=True
        
       
       xlApp.Quit
        DoCmd.TransferSpreadsheet acImport, , "tblFORECAST_TEST", "C:\TEMP\Export.XLSx", True, "qryForecast!"

       Set xlApp = Nothing
       
       Kill "C:\TEMP\Export.XLSx"
       Kill "C:\TEMP\Backup of Export.XLk"

Hi fraanchtoast and welcome to the forum,

In a quick read of your code, that is running behind Access, you have "ActiveWorkbook". I'm not sure Access knows what an ActiveWorkbook is.
Could that be the problem? Is there a way to close the file without using ActiveWorkbook?

Considering that you are opening a workbook in another Excel instance, and then closing that workbook, is there an workbook active when the code fails?

I don't use Access, but isn't that workbook opened in the xlApp instance of Excel? And if so, ActiveWorkbook does not refer to it.

ActiveWorkbook without a qualifying reference is the same as Application.ActiveWorkbook - the active workbook in the Excel instance that the code is running in.

If the workbook is open in the other instance of Excel, then you would refer to it as xlApp.ActiveWorkbook.

As I said, I don't use Access, so I don't know where the workbook is open.

When the code breaks, set xlApp.visible = false in the Immediate window; do you see an open workbook?

Thanks Cutter, i'll remember that. I look forward to building VBA skills and contributing more in the future!


Edit by Cutter: You're welcome