+ Reply to Thread
Results 1 to 13 of 13

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

  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:

    Please Login or Register  to view this content.
    Last edited by Cutter; 08-28-2012 at 02:25 PM. Reason: Added code tags

  2. #2
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Seattle, WA, USA
    MS-Off Ver
    MS Office 365 Excel 2016
    Posts
    13,912

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

    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?
    One test is worth a thousand opinions.
    Click the * below to say thanks.

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

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

    Hi Marvin, thanks for the quick response. Access has no trouble with it every other time, so i'm not exactly sure. Also, the VBA has Excel's library as a reference, so if it's a valid Excel object, it should work just fine in the Access VBA editor.

  4. #4
    Forum Expert Cutter's Avatar
    Join Date
    05-24-2004
    Location
    Ontario,Canada
    MS-Off Ver
    Excel 2010
    Posts
    6,454

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

    @ fraanchtoast

    Welcome to the forum.

    Please notice that code tags have been added to your post(s). The forum rules require them so please keep that in mind and add them yourself whenever showing code in any of your future posts. To see instructions for applying them, click on the Forum Rules button at top of the page and read Rule #3.
    Thanks.

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

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

    Thank you, i'll do that.

  6. #6
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    39,747

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

    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?
    Last edited by shg; 08-28-2012 at 05:00 PM.
    Entia non sunt multiplicanda sine necessitate

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

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

    Yes, there is. "DoCmd.OutputTo acOutputQuery, "qryFORECAST", acFormatXLSX, "C:\TEMP\Export.XLSx", True" opens the results of an access query in an excel workbook.

    Thanks for any ideas!

  8. #8
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    39,747

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

    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.

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

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

    i'm actually opening two workbooks. the first

    Please Login or Register  to view this content.
    is a workbook that contains the macro i need, and the second

    Please Login or Register  to view this content.
    is the query output that i need to apply the macro to. So there is still a workbook open after the "xlWB.Close (True)" runs.

    ---------- Post added at 02:01 PM ---------- Previous post was at 01:58 PM ----------

    Are you saying that i can't manipulate the same workbook using "xlApp" and "ActiveWorkbook"?

    ---------- Post added at 02:03 PM ---------- Previous post was at 02:01 PM ----------

    Because it works half the time...
    Thanks, shg, for any suggestions!

  10. #10
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    39,747

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

    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?

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

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

    You're totally right! that workbook is in another instance of excel. addressing the ActiveWorkbook as 'xlApp.ActiveWorkbook' did the trick! Thank you so much!

  12. #12
    Forum Expert Cutter's Avatar
    Join Date
    05-24-2004
    Location
    Ontario,Canada
    MS-Off Ver
    Excel 2010
    Posts
    6,454

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

    @ fraanchtoast

    Based on your last post it seems that you are satisfied with the solution(s) you've received but you haven't marked your thread as SOLVED. I'll do that for you now but please keep in mind for your future threads that Rule #9 requires you to do that yourself. If your problem has not been solved you can use Thread Tools (located above your first post) and choose "Mark this thread as unsolved".
    Thanks.

    Also, as a new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

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

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

    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
    Last edited by Cutter; 08-29-2012 at 01:10 PM.

+ Reply to Thread

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