Path\File.xlsm could not be found

[Sprinter]

“Macro could not be found . . . “. I have created a large program (161 procedures) in VBA for a friend now running Excel 2010 in Windows 7 op sys. To make calling of Key routines simple, I adapted (shamelessly copied) Ron de Bruin’s how to create a popup menu (http://www.rondebruin.nl/popup.htm). Program works. I copied the original workbook, renamed it as WBookB and sent it to my friend (in another organization) only to find that the macros did not run because they had the path and file name of my original spreadsheet associated with it (“C:\Users\Sprinter\Documents\WBookA.xlsm could not be found . . . “). This happened even with the identical module and Personal.xlsm linked to the new spreadsheet. <?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
So, it appears that the path and file name containing the original macro are stored somewhere in WBookB but pointing WBookA. Questions then are: 1) Where is that path and file name stored? 2) Can it be accessed and the old path and name changed for a new path and name? <o></o>
I have looked for the path and name in the CustomUI Editor, and have tried unzipping the WBookB. The program worked in Excel 97 and 2007 but with Windows 7 and Excel 2010 too much has changed. I can do a work around by creating macros through the Developer tab, but there is a lot of typing and it still doesn’t tell me what’s going on. Four days and endless combinations of searches on Google and Bing have not gotten me closer. I have purchased MrExcel’s book VBA and Macros but was not able to find answers to my questions. Using Application.run “C:\Users\Sprinter\Documents\WBookA.xlsm” works on my machine because path is valid but not on remote machine. I would appreciate any insights or pointers as to what is going on and how to fix it. Thanks <o></o>

 

[Andrew Poulsom]

I think you had better post the relevant parts of your VBA code. For me Ron's macro CreateDisplayPopUpMenu works in any macro enabled workbook.

 

[Sprinter]

Thanks so much for reviewing my inquiry. I do not think I made a coding error, but rather a procedural error that caused the Excel operating environment to screw with code and so I really do not know what is generating the problem. I imagine that there is some two dimensional environmental array along the lines of “Macro Name, Macro Path” that would list all the active procedures. The problem is that Macro1 points to WorkbookA on drive C:\ and Macro2 points to WorkbookB on drive G:\. I would have like to have both Macros point to the same workbook on the same drive and thought that it would be relatively simple to change the path rather than delete and then re-enter the macros names. Right now I have fixed the problem by re-entering the macros and storing them in “ThisWorkBook”, what else would you do on a Sunday afternoon. I think that the problem occurred when I copied WorkbookA and renamed it WorkbookB as the project evolved. Walkenbach and Peltier (http://peltiertech.com/WordPress/list-vba-procedures-by-vba-module-and-vb-procedure/) have published routines that seem to go in the direction I imagine. Unfortunately, that part of the forest is not well marked or I am too dim to see the answer. So why don’t you do us all a favor and delete my inquiry.
Thanks again.