Issue with excel workbook_open

Question

0.00/5 (No votes)

See more:

I have a little problem with Excel - some steps outside my base-competency ...

I want to do some action if the Excel-Workbook opens. I found that the Workbook_Open-method (declared inside the Workbook-script) is responsible for this.
If I save my file in Excel97-Format as xyz.XLS it works properly.
But if I save it als xyz.XLSM (I use Excel 2010) this Event isn't raised - independand of what I'm doing inside the method. Every other Event-Method (inside the Workbook or a table) is working properly.

So ... where is my mistake ?
Or should I change a Property anywhere to another value ?

What I have tried:

Searching in Internet, severall tries ... ask a question here ... :)

Posted 11-Sep-19 1:28am

Ralf Meier

Updated 12-Sep-19 0:17am

Add a Solution

Comments

Maciej Los 11-Sep-19 8:30am

Ralf, show your code, which is "not working". What method do you use to save Excel file: via code or manually?

Ralf Meier 11-Sep-19 8:48am

Hi Maciej,
It's complete independant what I do in the Workbook_Open-method - it works if I save it at Excel97-XLS and it doesn't work if I save it as Excel-XLSM.
I save it manually - that means : the manual-saved XLS-File starts the method when opening the Workbook and does the action - the XLSM-File (with the complete same content) does it not !!!
In the moment I use the old XLS-Format to have the function - but this could not be the Solution ... :(

Maciej Los 11-Sep-19 8:52am

Have you tried to compile VBA code before saving excel file with xlsm extension?

Ralf Meier 11-Sep-19 8:55am

Sorry ... Excel and compile ???

Maciej Los 11-Sep-19 9:19am

Yeah! Go to VBA Code Pane (ALT + F11), Menu "Debug->Compile VBAProject".

Ralf Meier 11-Sep-19 9:27am

No change ...
But I think that Excel does this automaticly too ...

Maciej Los 11-Sep-19 9:33am

Well Excel doesn't do that automatically.
Have you tried to open this xlsm file on another machine? Have you accepted Excel warning while first run of xlsm file?

Ralf Meier 11-Sep-19 15:13pm

Yes ... same behaviour.
And Yes (of course) I switched the Macro-Security inside the Trustcenter to the lowest Level.
It doesn't come from the content of the method - it works properly if I run it in Debug-mode AND ALSO (once again) if I save the absolutely same file in Excel97-Format (*.XLS).
That isn't understandable for me ... but I suppose that there is a Property (inside Excel) which is allready disabled which forces this behaviour.
To make all complete : there is another method inside the Workbook-Object (Workbook_BeforeClose) - this method works allways (also inside the XLSM-File) - means : this Event is raised (or the Handler is connected to this method) and it seams for me that in case of the Workbook_Open-method the Eventhandler is not connected to the method. But this behaviour could not be manipulated by me ... :(

I think, we know each other a little and I hope you know that you need not to search for the basic-failures at me ;-)

Maciej Los 11-Sep-19 15:40pm

:)
Weel, sh...t happens... Can you do favour to me? Create new workbook, add Workbook_Open() method and save it with both formats. Then, can you share them (via Google Drive or something similar)? I'd look at them and let you know if the same strange behaviour is happend on my machine.

Ralf Meier 12-Sep-19 3:15am

Nice suggestion ...
If I create a new (naked) XLSM-File it works - I call a MsgBox inside Workbook_Open and this happens.
But that doesn't solve my problem - the question is still the same : why does it (my project) works as a XLS-File and why doesn't it work as a XLSM-File ?
What I haven't tried until now is (but I will do it soon) :
Save the project as XLS, then open the XLS and save (and convert) it to XLSM. We will see ...

Maciej Los 12-Sep-19 3:30am

Seems your original workbook is broken (and cannot be properly saved in OpenXML format). As i mentioned, sometimes it happens.

Ralf Meier 12-Sep-19 5:04am

I don't believe that it is broken - perhaps something different.
I made my test - negative result. It's still the same : XLS works (without any restrictions) and with XLSM the Workbook_Open doesn't work. Each other functionality (and there is a lof of it) is working correct ...
I suppose there is a Setting which does that - but I have no idea where ...

But thanks for your support, Maciej ... :-)

Maciej Los 12-Sep-19 5:47am

Sorry, but there's no setting. Think of it. If newly created workbook works as well, why your original workbook does not work properly when you save it as xlsm? It is broken and can't be save in OpenXML format.
Good luck!

Ralf Meier 12-Sep-19 5:54am

I agree (basicly) with you ... and also not (I tried the same with some other Workbooks I have - with the same result) ...

Maciej Los 11-Sep-19 15:41pm

No, i'm not searching basic-failures at you ;)

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Answer 1 · 2019-09-12T00:17:00

Solution 1

In reference to our discussion in comments to the question...

There's no setting (in Excel workbook), which swithces/forces execution of Workbook_Open() macro between *.xls and *.xlsm files. That macro is executed independant of version of Excel file. See: Workbook.Open event (Excel) | Microsoft Docs[^]

I think there's something wrong with MS Office installation/configuration. Please, follow the steps from: Excel Macro-Enabled workbook (.xlsm) macros disabled and no obvious way to re-enable - Stack Overflow[^]

Posted 12-Sep-19 0:17am

Maciej Los

Comments

Ralf Meier 12-Sep-19 6:45am

Very interesting ... I tried all without any real success.
But ... if I go to the Trustcenter and disable the Macro-Execution to "Select disable all macros with notification" and restart Excel I have to confirm the macro-execution. If I decide "Yes" it works (the Workbook_Open) - but only this one try. A 2nd call causes into the allready known result.
So it becomes more and more not understandable for me.
At home, where I tried it also, I have a different Office-Version than at work - with the same result. So it can't be Version-depandant.
But it could perhaps be a "Feature" from something different which is existing at work and also at home ...

So, sorry Maciej, I can't accept this Solution ...

Maciej Los 12-Sep-19 6:57am

I do not expect that you accept my solution. I wanted to provide information, which may help to people with the same issue.

It seems like you do not have enough rights to change Office settings permament. It might be caused by windows policies (re-creation of your profile is recommended) or Office installation (repair is required).

Good luck!

Ralf Meier 12-Sep-19 14:47pm

I have thought a bit about what you have written :
It is not logical that I have a problem with my Office-Installation : if you are right it would not be that a new Workbook could do it. It must be something inside the Workbook itself - but also it could not be so (because of all the things I wrote before).
But I understood - it seams to be a common issue ... but it also seams that there is no Solution. So I will work with the Workbooks in Excel97-Format and have the functionality. But that is a stupid work-around ... sorry ...

I gave you the 5+ for your assistance and your support ...
It's allways a pleasure to write and discuss with you Maciej ...

Maciej Los 12-Sep-19 17:22pm

Thank you, Ralf.
It's also pleasure to discuss with you.

Finally, i'd suggest to read this thread: excel - Is there a compatibility issue between macros written for XLS, XLSX and XLSM? - Stack Overflow[^]. There you may find something useful in resolving your issue (especially in a second answer).

Cheers!
Maciej