Results 1 to 7 of 7
  1. #1
    MatthewR is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2015
    Posts
    118

    Intermittent "Run Time Error 91: Object Variable with Block Variable Not Set"

    Hi, everyone. I'm trying to export a query to Excel, let the user save it with the Save As prompt, and then grab the file name with a variable.

    My code works just fine on every OTHER attempt. But after it works once, I get "Run Time Error 91: Object Variable with Block Variable Not Set" on the next try. Then, it works again. Then, I get the error again.

    The line that triggers the error is in bold below. I've already tried adding a "Wait" function that makes the program wait 5 seconds before running the line. Didn't work, unfortunately.

    I am befuddled. Please help me get to the bottom of this. Many thanks. -Matt


    Code:
    Private Sub Command155_Click()
    
    Dim MyDatabase As DAO.Database
    Dim xlApp As Object
    Dim ShortBookName As String
    Dim FullBookName As String
    
    Set MyDatabase = CurrentDb
    
    Set xlApp = CreateObject("Excel.Application")
    
    DoCmd.OutputTo acOutputQuery, "qryExportPerpQuestions", acFormatXLSX, , True
    
    FullBookName = xlApp.ActiveWorkbook.FullName
    
    Debug.Print FullBookName
    
    ShortBookName = Right(FullBookName, Len(FullBookName) - InStrRev(FullBookName, "\"))
    
    Debug.Print ShortBookName
    
    Set xlApp = Nothing
    Set MyDatabase = Nothing
    
    
    End Sub


  2. #2
    CJ_London is online now VIP
    Windows 8 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    since you do not have any error handling, my guess is that the code is restarted after you get the error

    just a couple of suggestions

    1. you don't use mydatabase so might as well remove
    2. try putting xlApp.close before you set it to nothing

    also recommend

    3. put 'Option Explicit' at the top of your module

  3. #3
    MatthewR is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2015
    Posts
    118
    Ajax,
    Thanks so much for the tips. Well, that solution worked just fine at first. But then, I added a few more lines of code to export another query to the same workbook, and I'm getting the problem all over again. Same error message, on the same line, every other time I run the code.

    Any suggestions on what I can do now? I've pasted the code below, with the added lines in bold.

    (FYI, xlApp.Close didn't work- I used xlApp.Quit instead. Is that correct?)

    Code:
    Private Sub Command155_Click()
    
    
    Dim xlApp As Object
    Dim ShortBookName As String
    Dim FullBookName As String
    Dim BookPath As String
    
    
    Set xlApp = CreateObject("Excel.Application")
    
    
    DoCmd.OutputTo acOutputQuery, "qryExportPerpQuestions", acFormatXLSX, , True
    
    
    FullBookName = xlApp.ActiveWorkbook.FullName
    
    
    Debug.Print FullBookName
    
    
    ShortBookName = Right(FullBookName, Len(FullBookName) - InStrRev(FullBookName, "\"))
    
    
    Debug.Print ShortBookName
    
    
    BookPath = xlApp.ActiveWorkbook.Path
    
    
    Debug.Print BookPath
    
    
    DoCmd.OutputTo acOutputQuery, "qryExportMeetingInfo", acFormatXLSX, BookPath & "\Dummy Workbook-" & ShortBookName, True
    
    
    xlApp.ActiveSheet.Move Before:=Workbooks(ShortBookName).Sheets(1)
    
    
    xlApp.ActiveWorkbook.Save
    
    
    xlApp.Quit
    
    
    Set xlApp = Nothing
    
    
    End Sub

  4. #4
    CJ_London is online now VIP
    Windows 8 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    save and quit after the first export as well?

    Also, is your bookpath correct?

  5. #5
    MatthewR is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2015
    Posts
    118
    I went ahead and tried to save and quit after the first export as well, and the problem remained the same.

    Yes, the path is correct. On every other run, the code works perfectly fine, including the path.

    Any other suggestions? Thanks again for your help.

  6. #6
    MatthewR is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2015
    Posts
    118
    Okay, I just discovered this thread: http://www.tek-tips.com/viewthread.cfm?qid=673915

    Their solution appears to be working. Hopefully, as I add more code, it will continue to work. If anything goes awry, I will let you know. Thanks again for your help. -Matt

  7. #7
    MatthewR is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2015
    Posts
    118
    Yeah, I added the rest of my code, and everything works fine.

    That takes a giant monkey off my back. Thanks again!

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Object variable or With block variable not defined
    By PorscheMan in forum Programming
    Replies: 3
    Last Post: 01-16-2013, 01:53 PM
  2. Replies: 13
    Last Post: 06-12-2012, 09:52 PM
  3. Replies: 0
    Last Post: 08-10-2011, 11:59 AM
  4. Object variable or With block variable not set
    By walter189 in forum Programming
    Replies: 1
    Last Post: 07-28-2011, 08:51 AM
  5. Replies: 4
    Last Post: 08-05-2010, 01:26 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums