New hack at an old Excel problem running macros in another open workbook


I am struggeling with a problem in Excel. I think we have discussed this before, but the problem remains, so I thought I'd give it another go.

I have an old VBA macro based system that uses a main workbook and different templates. Changes to this system is done in a separate test/development area where we keep old revisions etc.

The main workbook contains all the macros. When you start the system, you run a macro in the main workbook that depending on choices you make, opens a new workbook which is a copy of one of the templates. These templates all contains a sheet with a menu consisting of menu buttons that all points to macros in the main workbook. Since the main workbook remains open at all times, these macros are available and can be run just fine.

When we do changes to this system, either to the main book macros or one of the templates, we have a macro, also in the main workbook, that when we're done makes a backup copy of all the templates and workbooks with a new version number, and then copies the lot over to the "live" area where the users run it.

This used to work just fine until we upgraded to a new versjon of Office (2013). After that this copy process seem to produce a certain problem.

After we upgraded, when we try to run one of the macros from the main workbook from one of the opened template copies, Excel adds the path from the development area in front of the macro names.


Test and development area:

Main workbook: \\server1\area1\main.xlsm
TemplateA: \\server1\area1\templateA.xlsx

In TemplateA a button is assigned to macro main.xlsm'!ThisWorbook.sys_open

Live area:

Main workbook: \\server2\sub\area5\main.xlsm
TemplateA: \\server2\sub\area5\templateA.xlsx

When the main workbook opens a copy of templateA.xlsx, the button is all of a sudden assigned to macro \\server1\area1\main.xlsx!ThisWorkbook.sys_open

When the users try to run it it doesn't work until you manually go in and change the assigned macro to main.xlsx!ThisWorkBook.sys_open or in other words remove the automatically added path.

When we open the template from the live area and edit it manually and save it and then try to open and it from the main workbook, the erroneous path is still added. This old path seem to be barried really deep in the Excel format somewhere, but we haven’t been able to find it.

Can someone please help?

Thanks a lot!!!
Iver Erling ArvaSenior consultantAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Have you tried doing your roll-out preparation on your local hard drive instead of on a mapped (network) drive?
Iver Erling ArvaSenior consultantAuthor Commented:
No. I have not.

Iver Erling ArvaSenior consultantAuthor Commented:
I think I may have found the solution to my problem.

In Excel:

1. Open the Excel tablet that has the problem.
2. On the Developer tab, click Macro security.
3. In the window that opens, select External contents in the menu on the left side.
4. Select Deactivate automatic update...
5. Close window.
6. Edit all macro assignments (buttons connected to macros) to remove the added path.
7. Save.

Now it works as expected.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Iver Erling ArvaSenior consultantAuthor Commented:
Now it works as it should and as expected.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.