custom toolbars

robwestcott

unread,

Jan 12, 1998, 3:00:00 AM1/12/98

to

i recently reinstalled ms office on a reformatted drive. this time i
installed to the default folder \program files\microsoft office

last time i installed to a different folder \msoffice

i had many custom toolbars that referenced to a personal.xls file from my
last installation

following the current installation, the toolbars would not function. i am
assuming that the toolbars are trying to call personal.xls from its
previous location...

how can i go into the toolbars and tell them the new location of the
personal.xls file ??

thanks

rob westcott

Bill Manville

unread,

Jan 13, 1998, 3:00:00 AM1/13/98

to

Please say which version of Office - the solution will be different
according to which you have. It is likely to be a macro that scans
the Toolbars / CommandBars looking at the OnAction property of custom
items and changing it where appropriate.

Alternatively, copy your PERSONAL.XLS to the directory it is looking
for, load Excel and then save PERSONAL.XLS back to the directory it
ought to be in. May work; haven't tried it.

Bill Manville
Oxford, England
Microsoft Excel - MVP

robwestcott

unread,

Jan 13, 1998, 3:00:00 AM1/13/98

to

>>following the current installation, the toolbars would not function. i am
>>assuming that the toolbars are trying to call personal.xls from its
>>previous location...
>>
>>how can i go into the toolbars and tell them the new location of the
>>personal.xls file ??
>
>Please say which version of Office - the solution will be different
>according to which you have. It is likely to be a macro that scans
>the Toolbars / CommandBars looking at the OnAction property of custom
>items and changing it where appropriate.
>

oops, sorry, it is office97.

interesting suggestion, how exactly would i accomplish this??

>Alternatively, copy your PERSONAL.XLS to the directory it is looking
>for, load Excel and then save PERSONAL.XLS back to the directory it
>ought to be in. May work; haven't tried it.
>

this did not work.

rob westcott

Bill Manville

unread,

Jan 17, 1998, 3:00:00 AM1/17/98

to

On Tue, 13 Jan 1998 22:18:16 -0500, "robwestcott"
<robwe...@hotmail.com> wrote:

>>>following the current installation, the toolbars would not function. i am
>>>assuming that the toolbars are trying to call personal.xls from its
>>>previous location...
>>>
>>>how can i go into the toolbars and tell them the new location of the
>>>personal.xls file ??
>>
>>Please say which version of Office - the solution will be different
>>according to which you have. It is likely to be a macro that scans
>>the Toolbars / CommandBars looking at the OnAction property of custom
>>items and changing it where appropriate.
>>
>
>oops, sorry, it is office97.
>
>interesting suggestion, how exactly would i accomplish this??
>

Something like this:

Sub Test()
UpdateOnactions "C:\EXCEL\XLSTART", "C:\OFFICE97\XLSTART"
End Sub

Sub UpdateOnactions(stFrom As String, stTo As String)
Dim CB As CommandBar
For Each CB In Application.CommandBars
UpdateCommandBar CB, stFrom, stTo
Next CB
End Sub

Sub UpdateCommandBar(CB As CommandBar, stFrom As String, stTo As
String)
Dim CTRL As CommandBarControl
Dim stOnAction As String
For Each CTRL In CB.Controls
If CTRL.Type = msoControlPopup Then
UpdateCommandBar CTRL.CommandBar, stFrom, stTo ' recursive
End If
stOnAction = ""
On Error Resume Next
stOnAction = CTRL.OnAction ' cannot get OnAction for some
On Error GoTo 0
If stOnAction <> "" Then
If InStr(stOnAction, stFrom) > 0 Then
CTRL.OnAction = Application.Substitute(stOnAction, stFrom,
stTo)
End If
End If
Next CTRL
End Sub