Solved

Excel 2000 > 2003 bulk macro editing tool.

Posted on 2010-11-18
8
433 Views
Last Modified: 2012-06-27
I have just migrated a user from Office 2000 to 2003. This user is a heavy macro user and after the upgrade it was discovered his macro's stopped working.
All the menu's are there as I copied over his personal.xls and excel.xlb files over.

The error refers to it not finding the file specified. See attached screenshot (error.jpg).
However if you modify the macro and strip out the full path C:\Program Files\Microsoft Office\Office\XLStart and just leave personal.xls in the file path field it then works.

This is a bit of a nightmare as there lots of these macros. Is there a way to bulk edit and strip out the file path to the personal.xls location in the assign macro window?
error.jpg
Assign-macro.jpg
0
Comment
Question by:Vai777
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 34163221
Is his personal.xls file in a different folder now?
0
 
LVL 4

Accepted Solution

by:
BrainB earned 500 total points
ID: 34163761
You may like to try my code here which I have used successfully many times.
It checks/changes macro paths in toolbar buttons and menus to the path of the file it is contained in. So the file needs to be in the right folder.
It is necessary to copy/paste the code into a module in the new personal.xls.
It requires no other action than to run it.
When run it does give a message box to check that it is running ok as well as recording details in a new worksheet.
As always it is a good idea to back up the original file first.

'============================================================================
'- CHANGE PATH OF EXCEL MACROS ATTACHED TO BUTTONS AND MENUS
'- include in Personal.xls (uses activeworkbook.path)
'============================================================================
'- Assumes this file is the one containing the macros. eg. Personal.xls
'- Note : Excel application button configuration is saved in a *.xlb file
'- Can also attach custom menu bars to the workbook.
'- Brian Baulsom October 2005 (buttons) & July 2006 (include menu items)
'============================================================================
Dim CB As CommandBar
Dim CTL As Object
Dim MySubMenu As Object
Dim MyCBControl As Object
'-
Dim wb As String
Dim OldPath As String
Dim OldAction As String
Dim MacroName As String
Dim NewAction As String
Dim ShowMessages As Boolean
Dim ToRow As Long  ' alternative to show details
'--------------------------------------------------------------------------
'-
'================================================================================
'- CHANGE CUSTOM BUTTON/MENU MACRO PATH TO THIS WORKBOOK
'================================================================================
Sub CHANGE_MACROPATH()
    wb = ThisWorkbook.FullName
    On Error Resume Next
    Application.GoTo reference:=Worksheets("Macros").Range("A1"), scroll:=True
    If Err.numer <> 0 Then
        Worksheets.Add
        ActiveSheet.Name = "Macros"
        Err.Clear
        Resume
    End If
    On Error GoTo 0
    '-----------------------------------------------------------------------------
    ToRow = 2
    ShowMessages = True
    rsp = MsgBox("This macro will change the path of all toolbar buttons to" & vbCr & wb, vbOKCancel)
    If rsp = vbCancel Then Exit Sub
    '----------------------------------------------------------------------------
    '- LOOP COMMANDBARS
    For Each CB In CommandBars
        '- *** To check all bars omit the following line and ... 'End If below' ***
        'If CB.Name = "Worksheet Menu Bar" Or UCase(Left(CB.Name, 5)) = "BRIAN" Then
            '--------------------------------------------------------------------
            '- LOOP COMMANDBAR CONTROLS
            For Each CTL In CB.Controls
                '----------------------------------------------------------------
                '- BUTTONS
                If CTL.Type = msoControlButton Then
                    Set MyCBControl = CTL
                    '==============
                    ChangeAction
                    '==============
                '---------------------------------------------------------------
                '- MENUS
                ElseIf CTL.Type = msoControlPopup _
                        And UCase(Left(CTL.Caption, 5)) = "BRIAN" Then
                    '- macro attached to main menu item
                    Set MyCBControl = CTL
                    '=============
                    ChangeAction
                    '=============
                    '- macro attached to sub menu
                    For Each MySubMenu In CTL.Controls
                        Set MyCBControl = MySubMenu
                        '============
                        ChangeAction
                        '============
                    Next
                End If
                '-----------------------------------------------------------------
            Next
        'End If         ' *** can omit this line. See above
    Next
    MsgBox ("Done")
End Sub

'================================================================================
'- SUBROUTINE TO CHANGE ONACTION PATH
'================================================================================
Private Sub ChangeAction()
    '***===================================================***
    '- alternative to show detail (to ActiveSheet)
    If CB.Visible = True Then
        With MyCBControl
            Cells(ToRow, 1).Value = CB.Name
            Cells(ToRow, 2).Value = .Caption
            Cells(ToRow, 3).Value = .ID
            Cells(ToRow, 4).Value = .OnAction
            Cells(ToRow, 5).Value = .TooltipText
        End With
        ToRow = ToRow + 1
    End If
    '***====================================================***
    OldAction = MyCBControl.OnAction
    '- eg. "F:\_SHARED\XLSTART\Personal+.xls!MyMacroname"
    If OldAction = "" Or InStr(1, OldAction, "Personal", vbTextCompare) = 0 Then Exit Sub
    MacroName = GetMacroName(OldAction)
    NewAction = "'" & wb & "'!" & MacroName
    If ShowMessages = True Then
        rsp = MsgBox("Do you want to continue without further messages ?" & vbCr _
            & "No continues. Cancel ends" & vbCr _
            & "Old   : " & OldAction & vbCr _
            & "New : " & NewAction, vbYesNoCancel)
        If rsp = vbCancel Then End
        If rsp = vbYes Then ShowMessages = False
    End If
    MyCBControl.OnAction = NewAction
End Sub
'================================================================================
'- FUNCTION TO GET MACRO NAME
'================================================================================
Private Function GetMacroName(mn)
    For C = Len(mn) To 1 Step -1
    If Mid(mn, C, 1) = "!" Then
        GetMacroName = Right(mn, Len(mn) - C)
        Exit Function
    End If
    Next
End Function
'---------------------------------------------------------------------------------

Open in new window

0
 
LVL 37

Expert Comment

by:TommySzalapski
ID: 34165422
Check
C:\Program Files\Microsoft Office\Office10\XLStart
and see if personal.xls is there too

I'm guessing it created a new personal.xls file when you installed the new version but all his macros are in the old one.

Make sure the personal.xls file with all his macros is in the location the error is looking for.
C:\Program Files\Microsoft Office\Office\XLStart
0
Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

 

Author Comment

by:Vai777
ID: 34171133
His personal.xls file is in both XLStart locations:
C:\Program Files\Microsoft Office\Office10\XLStart

and

C:\Program Files\Microsoft Office\Office\XLStart

It's in the right location as it's loading up his custom menu's. It just seems like 2003 doesn't require the whole path name.

I might pass the above code to the person and see what he thinks as he can make more sense with what it's doing than me.
0
 
LVL 4

Expert Comment

by:BrainB
ID: 34172224
Have you checked Tools\Options\General ... something like "At startup open all files in .." (was "alternative startup location")
0
 

Author Comment

by:Vai777
ID: 34173053
But surely if the macro's are stored in the personal.xls and that's in the right location that should be enough?
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 34173116
Yes, it should assuming that macro is actually in the file.
0
 

Author Closing Comment

by:Vai777
ID: 34229502
Had the user run this script and it worked a treat.

Thanks!
0

Featured Post

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

786 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question