Assign macro issue

[hoffmansg]

I'm running a macro on Excel 2003 and assigned it to a toolbar button via the standard "Assign Macro" form.
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o> </o>
This macro, in order to save the results of all the processing and not affect the original workbook, performs a SaveAs to another Excel file upon completion before closing the active excel workbook (with save=false). This works great except that the next time I open the original workbook and try to run the macro again from the toolbar button, it has been reassigned to the previous SaveAs workbook. In order to use the original workbook's macro, I have to reassign the macro back to the current active Workbook which defeats the whole idea of having a toolbar button. OR worse, if the SaveAs file has been moved, it produces a "can't find the file" error and I have to reassign anyway. It's not a problem if I were the only one using this workbook/macro but it will be an analysis tool for several engineers and any extra actions requires extra time to perform, adding extra procedure steps and training, etc., especially if it's not intuitively obvious that the macro has been reassigned rather than being left alone.
<o> </o>
Obviously I've tried to assign to just this workbook and also by name but to no avail rather than all workbooks but same results occur.
<o> </o>
How do I get excel to not assign the macro to the SaveAs workbook in the first place, leaving it assigned to the original workbook as one would expect following a close with no save?
<o> </o>

 

[Richard Schollar]

In which workbook does this macro reside? What module is the code in? What is the code (please post in full)?

 

[hoffmansg]

The macro code actually resides in both the original and in the SaveAs workbooks. However when opening the original workbook, its macro is pointing to the SaveAs workbook version, not its own.

Steps to create the issue:
1) Create the macro and button
1.1) Open a workbook
1.2) Create a macro
1.3) Create a button
1.4) Assign the macro to the button via the Assign Macro form
1.5) Save the workbook

2) Create the SaveAs Workbook
2.1) Re-open the original workbook
2.2) SaveAs to a new workbook
2.3) Close new workbook. At this point the original workbook should be unaffected!

3) Now for the issue
3.1) Re-open the original workbook
3.2) Open the Assign Macro form
3.3) View where the macro is being assigned where it'll be the SaveAs new workbook, not the original workbook.

If this is how Excel is designed (poorly design then IMHO), then how do I open the original workbook, press the button to run the macro, save the macro's results in another workbook and return the original workbook to its original condition with its macro pointing to itself?

 

[Richard Schollar]

OK, I think the easiest thing to do would be for you to amend your current code (I note you failed to post it, despite my request) from using the SaveAs method to using the SaveCopyAs method as this saves a copy of the workbook to a file but doesn't modify the open workbook in memory. This should avoid what you are seeing.

So:

'existing code:
ActiveWorkbook.SaveAs "SomeFilename"

'new code:
Activeworkbook.SaveCopyAs "SomeFilename"

Make sense?

 

[hoffmansg]

Sorry I didn't post code but it is well over 1000 lines long and I didn't know what portion you wanted.

Yes, the SaveCopyAs vs my SaveAs does the trick exactly as I want. Many thanks.