When Opening File - Run-time Error '91': Object Variable or With block Variable not Set

After upgrading to Office 2010, when my Excel  files are downloaded from the Internet or opened as Office 2010 email attachments, after selecting the option to "Enable Editing" we get the following error message: "Run-time Error '91': Object Variable or With block Variable not Set".

This is only occurring in files where I have Worbook_Open code so I am sure it is a security issue. I do not have the problem when opening files with Office 2007, only 2010. I don't believe it is actually a problem with the code because all files do this even when some of the code simply turns on automatic calculations when opened.

When debug is selected it just stops at the first line of the code. I added error checking to resume next, but then it just goes to the next line. Disabling macros in Office is not an option.

Thanks in advance for your help.

Carl

 

Question Info


Last updated September 17, 2018 Views 18,969 Applies to:
Answer

The first thing I'd do is this:

In the Immediate Window, type this (and press return)

     Application.EnableEvents = False

Then open the workbook.    It sounds like you have workbook open code.   When the events are turned off, it won't run when you open the workbook.   Then step through the Workbook_Open code to see if you can find where the error occurs. 

When you are done, you can change it back to Application.EnableEvents = True

With regard to the controls issue (and I don't know this is it), take a look at this link to see if it may be related:

http://www.lessanvaezi.com/delete-exd-files-to-fix-object-library-invalid-error/

There is also a KB article (written after we first had the issue) that can be found here:

http://support.microsoft.com/kb/957924/en-us

We were on Office 2007 at the time and the workbook has been around for a long time. 

 

 

 

 


Thanks for your help. I visited the links you suggested and did not find any help there. You are correct that I have Workbook_Open code. This error is occurring in all of my files with any Workbook_Open code, no matter how simple, whenever the file is opened in Office 2010 Protected View (I had originally said it was not a problem with simple code in Workbook_Open but realized later that was only due to the fact I did not open the file in Protected View for that test).

 

I repaired Office 2010 and ran Windows Updates to ensure nothing is amiss and that did not help. I am running Visa. I do have Office 2003, 2007 and 2010 on the same computer but NEVER run more than one version at a time. There is no shortage of RAM.

 

I changed the extent on my Personal.xlsb file to .xls so I can use it in Office 2003 but went back to default just to see if that is the problem and that did not help. No one else that I know of is having this problem with my spreadsheets and I only have the problem when the file is opened in Protected View.

 

I following your good suggestion to add a step to disable events upon opening (I know that normally works) but it just skipped right over that and showed an error when opening (Run-time error ‘91’: Object variable or With block variable not set”). Theoretically, as you mentioned it should not have even run the Workbook_Open macro.

 

To rule out anything wrong with my workbooks, including any control problems, I just opened a blank workbook and pasted the following as a Workbook_Open macro, then saved the file as a 2010 macro-enabled workbook (.xlsm).

 

I still had the same error when opening it in Protected View. When I selected “Debug” the code, “ActiveWorkbook.PrecisionAsDisplayed = False” was highlighted.

 

However, I find that if I delete that line of code in my other workbooks, it just gives me an error on the very next line of code where it needs to reference some cell on some particular worksheet. It seems as if it is getting bogged down doing the security check when opening the file so it can’t find anything it needs when it needs it.

 

Thanks again for trying to help. Please let me know if any of this gives you any ideas.

 

Sub WorkBook_Open()

    Application.EnableEvents = False   

   ‘ Application.WindowState = xlMaximized   

    With Application

        .Calculation = xlAutomatic

        .MaxChange = 0.001

    End With

    ActiveWorkbook.PrecisionAsDisplayed = False

End Sub

I finally figured out the reason for all the errors in code that had previously worked fine for years in other versions of Office…I had a reference to “Microsoft Office Soap Type Library v3.0” from a previous installation of Office 2003.

 

When I upgraded to Office 2010 this reference was no longer valid, which was causing all the problems and error messages "Compile error: Can't find project or library". I corrected the bad reference and all works perfectly now.

 

Thanks for everyone's help!

Carl

Carl

1 person was helped by this reply

·

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.