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

**fraanchtoast** · 08-28-2012, 12:02 PM

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.

**MarvinP** · 08-28-2012, 12:08 PM

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?

**fraanchtoast** · 08-28-2012, 12:11 PM

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.

**Cutter** · 08-28-2012, 02:25 PM

@ 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.

**fraanchtoast** · 08-28-2012, 03:28 PM

Thank you, i'll do that.

**shg** · 08-28-2012, 03:34 PM

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?

**fraanchtoast** · 08-28-2012, 03:35 PM

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!

**shg** · 08-28-2012, 03:41 PM

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.

**fraanchtoast** · 08-28-2012, 04:03 PM

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!

**shg** · 08-28-2012, 04:12 PM

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?

**fraanchtoast** · 08-28-2012, 04:23 PM

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!

**Cutter** · 08-28-2012, 07:42 PM

@ 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.

**fraanchtoast** · 08-29-2012, 10:11 AM

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

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

LinkBack

Thread Tools

Rate This Thread

Display

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

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

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

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

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

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

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

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

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

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

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

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

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

Thread Information

Users Browsing this Thread

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions