Solved

Excel 2000 > 2003 bulk macro editing tool.

Posted on 2010-11-18
8
430 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
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

No matter the version of Windows you are using, you may have some problems with Windows Search running too slow or possibly not running at all. Before jumping into how you can solve this issue, just know there are many other viable alternative deskt…
Outlook Free & Paid Tools
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

747 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

16 Experts available now in Live!

Get 1:1 Help Now