Results 1 to 13 of 13

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

Threaded View

  1. #1
    Registered User
    Join Date
    08-28-2012
    Location
    Denver
    MS-Off Ver
    Excel 2007
    Posts
    8

    Post 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"
    Last edited by Cutter; 08-28-2012 at 02:25 PM. Reason: Added code tags

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1