Solved

Excel 2000 > 2003 bulk macro editing tool.

Posted on 2010-11-18
8
431 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
 

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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

This article will show you how to use shortcut menus in the Access run-time environment.
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

914 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now