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
In TemplateA a button is assigned to macro main.xlsm'!ThisWorbook.sys_open
Main workbook: \\server2\sub\area5\main.xlsm
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!!!