The macro "C:\...\Desktop\PERSONAL.XLS'!Macro_Filename' cannot be found.

[Mike Welch]

I have a custom button that worked fine until I saved PERSONAL.XLS to the desktop so that I could easily send it off in an email (with it's specialized macros). Now when I press the formerly working button I get the message in the header. MS has, in it's wisdumb, apparently told Excel to look for PERSONAL.XLS on the desktop (which has been deleted) and I can't find a way to tell it to go look in C:\Documents and Settings\user\Application Data\Microsoft\Excel\XLSTART where it belongs (right???). Any help appreciated!

Win XP Pro SP3 / Excel 2003 SP3

 

[BrianB]

Tools/Options .. General ... Alternate startup file location .... ?

 

[Mike Welch]

No luck (yet) ...

I went to Tools/Options .. General ... At startup, open all files in: (sincce I couldn't find Alternate startup file location) and set that field to point at the proper location (C:\Documents and Settings\[user]\Application Data\Microsoft\Excel\XLSTART\PERSONAL.XLS) but when I press the macro button I get the message "The macro "C:\Documents and Settings\[user]\Desktop\PERSONAL.XLS'!Macro_ZoomSelection' cannot be found".

I know it can't find PERSONAL.XLS there ... I saved it there (once) and have now saved it in C:\Documents and Settings\[user]\Application Data\Microsoft\Excel\XLSTART (where I think it belongs (a file with that name resided there already - I assume that it's the same file I just saved to the desktop...)) ... so ...

Excel is still looking to the desktop for PERSONAL.XLS and I can't make it change to look in C:\ ... \XLSTART Any other thoughts?

Thanks! / Mike

 

[Mike Welch]

Another finding ...

Went to Customize the macro button and looked to see where it was pointed. If the Macro name field it had 'C:\Documents and Settings\[user]\Desktop\PERSONAL.XLS'!Macro_ZoomSelection In the list of macros, most all the ones I use are simply listed as PERSONAL.XLS!Macro_[name].

After closing that window and opening up the macro window (Alt + F8) I see the same thing, macros in the list are marked as PERSONAL.XLS!Macro_[name] with none of them showing a reference to the Desktop. Yet it keeps looking on the desktop...

 

[Mike Welch]

Hmmm ... I thought someone would have had an aswer for this. I'll bump it once, maybe again next Monday (if not answered), then resign it to the great unknown ... (which will make this a knowable unknown...)

 

[ozbatov]

Exactly the same problem here. I'm using Excel 2003, I had to send personal.xls to my home computer so "saved as" it to a temporary folder. Now, no matter what I do (even saving back to the xlstart folder or make a copy of personal.xls again to that temporary folder) it doesn't seem to solve the problem.
So I decided to make a registry search cause I thought that Excel should be keeping that info as a variable in there. I found 2 words containing "personal.xls" and they were both showing the right xlstart location.
The only think seems to work is unhiding personal.xls. Then macros become runnable again. But, as you can guess, I don't want to unhide personal.xls and have another open sheet everytime.
Help would be greatly appreciated.

 

[RoryA]

You need to edit the buttons so that they point to the correct path for your personal.xls workbook.

 

[ozbatov]

Thanks Rory, that solved my problem.

 

[Mike Welch]

You need to edit the buttons so that they point to the correct path for your personal.xls workbook.

Hi rorya! Thanks, I have found that re-pointing does in fact work but what if you have dozens of custom macro buttons? I would think that, somewhere, there should be a place to make a single change that globally effects where the buttons are pointing to. I'm sure that Excel didn't open each button individually to "break" (redirect) my pointers...

 

[RoryA]

There isn't unfortunately. I wrote this ages ago when our IT department started messing up our network drives - it may help you (the first filepath is the old, incorrect one, the second is the new one you want used):

Sub LoopBars()
    Dim cbr As CommandBar, ctl As CommandBarControl
    For Each cbr In Application.CommandBars
'    Set cbr = Application.CommandBars("Standard")
        If cbr.visible Then
            For Each ctl In cbr.Controls
                ResetControls ctl, "T:\XLSTART\Personal.xls", "H:\XLSTART\Personal.xls"
            Next ctl
        End If
    Next cbr
End Sub

Sub ResetControls(ctlIn As CommandBarControl, strOldPath As String, strNewPath As String)
    Dim ctl As CommandBarControl, lngCount As Long
    On Error Resume Next
    lngCount = ctlIn.Controls.Count
    If lngCount > 0 Then
        For Each ctl In ctlIn.Controls
            ResetControls ctl, strOldPath, strNewPath
        Next ctl
    Else
        With ctlIn
            If Len(.OnAction) > 0 Then
                .OnAction = Replace$(.OnAction, strOldPath, strNewPath, , , vbTextCompare)
                Debug.Print .OnAction
            End If
        End With
    End If
End Sub