Changing path of button macro assignment

[Otto Moehrbach]

Excel XP & WinXP
I am helping an OP with a rather large project he has with a client. The
file, say TheFile.xls, has many sheets and most of the sheets have at least
one button with many sheets having multiple buttons. Obviously, the macros
assigned to the buttons are varied. All the macros are contained in another
file, "XX Personal.xls".
The problem comes about when he copies his file (TheFile.xls) from his
computer and takes it and installs it into his client's computer. The
problem is that Excel maintains the path to the assigned macro (for each
button) as that path that was valid in the OP's computer. Obviously, that
path has to be changed. Please note that the file name in the path remains
constant (XX Personal.xls). The macro name changes for each button.
One way that I see to make the necessary path change is to write & run a
macro that loops through all the buttons on each sheet, looks at the path
assignment for each button, searches for the constant file name (XX
Personal.xls), notes the macro name that follows in the path, and changes
the total path to that file name (XX Personal.xls) and macro name.
My question is: Is there a better way to do this? Thanks for your help.
Otto

 

[Jim Rech]

Would Edit, Links, Change Source do the job?

--
Jim
| Excel XP & WinXP
| I am helping an OP with a rather large project he has with a client. The
| file, say TheFile.xls, has many sheets and most of the sheets have at
least
| one button with many sheets having multiple buttons. Obviously, the
macros
| assigned to the buttons are varied. All the macros are contained in
another
| file, "XX Personal.xls".
| The problem comes about when he copies his file (TheFile.xls) from his
| computer and takes it and installs it into his client's computer. The
| problem is that Excel maintains the path to the assigned macro (for each
| button) as that path that was valid in the OP's computer. Obviously, that
| path has to be changed. Please note that the file name in the path
remains
| constant (XX Personal.xls). The macro name changes for each button.
| One way that I see to make the necessary path change is to write & run
a
| macro that loops through all the buttons on each sheet, looks at the path
| assignment for each button, searches for the constant file name (XX
| Personal.xls), notes the macro name that follows in the path, and changes
| the total path to that file name (XX Personal.xls) and macro name.
| My question is: Is there a better way to do this? Thanks for your help.
| Otto
|
|

 

[Otto Moehrbach]

Jim
I gave your suggestion to the OP and it worked like a charm. I knew
there had to be a better way than the code I was thinking about writing.
Thanks for your help and for your time. Otto