[Solved] VBA : Problem using macros when moving excel files

Balangan · Jan 16th 2004

Hi all
Its a long while I have this problem and found no solution: I have an excel file containing macros that are run clicking a button from the toolbar. Somehow the macros are there identified with their absolute path ('C:\WINDOWS\Desktop\Cartel1.xls'!Macro1). So if I move the excel file somewhere else on the network or even on the same drive, and try to click the button to run the macro, I will get an error message (caused by excel looking for the old path). Is there any way to avoid this? For example telling excel to use relative paths?

Thanks in advance for your help.

**Andy Pope** · Jan 17th 2004

Hi Balangan,

I don't believe you can make it use relative references. Also not sure how it would know what to be relative too.

I think the best thing to do in order to keep the absolute references in sync is to open the workbook containing the macro and the save it in the new location.
With the, admittedly limited, testing I did the button updated its absolute link when the workbook was saved. Then you can tidy up the old workbook.

Cheers
Andy

Balangan · Jan 17th 2004

Yes, thanks for the hint. That would work perfectly in case you need to *move* the file.
I now made some other testing myself. Since the original problem for me was how to *copy* a file from its original location to another without having to change the path of all the macros linked to the toolbars (i have dozens of them sometimes), I still have not solved it.
Whenever I make a copy of the file I saw that I have 2 scenarios:
1) Make a copy somewhere else with the same file name (Ex: file "c:\cartel1.xls" copied to "d:\cartel1.xls").
In this case Excel will look for the macro project contained in the original file, that has the same name but is in another location, and since it cannot open 2 files with the same name, it will not work.
2) Make a copy with another file name (Ex: new file is called "Cartel2.xls", it doesnt matter if its in the same folder or in a different location).
In this case the macros will work, but only because Excel will still open the macro project of the first file in the old location! So if you (like I do) change pc (or network in case of a network), i.e. need to make a copy of a file and bring it home to continue working on the project, you will have to edit the buttons on the toolbars one by one and update the links.. very annoying :barf:

The point seems now to me: is there some way to "attach" a macro project and its ties with the toolbar to a specific file? Or maybe create a routine that reads the file location and updates the paths of the toolbars?

Thanks again.

Anonymous · Jan 18th 2004

Why not just have an Workbook Open event that relinks the buttons to the macros automatically as you suggest in your last line. It should not be hard to do. Is the tool bar one you created in the first place?

Balangan · Jan 18th 2004

Uhm, yes I did it. I am not so good in VBA though, but i'll try to do it tomorrow and post here the results (if any)

Balangan · Jan 19th 2004

do anyone know what methods I should use to relink buttons to macros in VBA? Thanks

Simplicity · Jan 20th 2004

Balangan,

I have the same issue - can't make a copy of my workbook without the toolbar macro assignments 'following' the original source of the VB routines they reference. In my case I have several users who will each use the workbook for a few weeks at at time to track project costs. Then they will copy it, blank out the data and start again the next project. As it stands, the workbooks toolbars will all end up referencing each other - impossible to manage.
I hope there is an easy solution that doesn't involve more coding. If I find an answer I'll let you know.

Steve.

Balangan · Jan 20th 2004

Yes thank you. I am afraid that some coding is required. Anyway lets keep in touch because I *have* to find a solution for this problem! I just dont know VBA too much, and usually write my macros starting with the macro recorder, and then readapting to my needs. But in this case, when I relink the toolbars, the macro recorder does not record anything at all, so I dont know which way to go!

Anonymous · Jan 20th 2004

Try the following (untested) macro, adjsting the menubar and menu name to your situation.

Code

Sub FixMenu()
    Dim c As Object
    For Each c In CommandBars(1).Controls("MyName").Controls
        c.OnAction = Mid(c.OnAction, InStr(c.OnAction, "!") + 1) 'get macro name
    Next c
End Sub

jjst34 · Jan 20th 2004

I am having this same issue, but mine are not on the menu bar.. there are just buttons w/in all 30+ worksheets that need to be updated.. I'd have to update 300 or more buttons.. grrrrrrr.. how can i apply this code to this situation.. have it look for buttons/macros in every sheet, and fix the link to no longer be G:blah blah...

Anonymous · Jan 20th 2004

Macros linked to buttons calling macros in the same workbook should adjust when the workbook is saved under a different name (don't they?). If the buttons are linked to macros in another workbook and both are transferred then some updating will be needed. Try tinkering with the following code. Correctname.xls is the workbook with the macros. Oldpath is the path of the original workbook before it was copied.

Code

Sub TryThis()
    Dim myLinks As Variant, i As Integer
    myLinks = ActiveWorkbook.LinkSources(xlExcelLinks)
    If Not IsEmpty(myLinks) Then
        For i = 1 To UBound(myLinks)
            If myLinks(1) = "oldpath\correctname.xls" Then
                ActiveWorkbook.ChangeLink Name:=myLinks(i), _
                NewName:=ThisWorkbook.Path & "\correctname.xls", _
                Type:=xlExcelLinks
            End If
        Next i
    End If
End Sub

Display More

jjst34 · Jan 20th 2004

Quote

Originally posted by Derk
Macros linked to buttons calling macros in the same workbook should adjust when the workbook is saved under a different name (don't they?).

I think they do.. the problem w/ mine is I have a macro that copies a bunch of worksheets from other workbooks,, kind of like a summary workbook. So when the sheet is copied over the macros and named ranges have problems...

I will mess w/ the code and hopefully it'll help. Thanks!

Anonymous · Jan 20th 2004

In your summary workbook, try this. Turn on the macro recorder. Go to the Edit>Links menu and take manually change the links as needed. Turn off the recorder and you may have what you need. It sounds like in your case a more general routine may not be needed.

jjst34 · Jan 20th 2004

ahh.. that was pretty simple.. appreciate that... sometimes we just think TOO hard!
again, thanks!

mhabib · Jan 20th 2004

Hi Balangan,

You might want to attach the toolbar to the workbook containing the macro(s). It will make your macro workbook and the associated toolbar fully portable.

1. Make sure that the workbook containing your macro(s) is open and active.

2. Click Tools > Customize

3. Select the Toolbar Tab

4. Click Attach and follow the instructions

5. Save the file

Your toolbar will now follow the file, making folder paths and file names irrelevant.

HTH

M
:spin:

Simplicity · Jan 20th 2004

Dirk, M,

Have tried the suggestions, but still no joy. Let me recap on the problem:

Workbook A contains a VB macro and a custom toolbar icon that runs the macro.
Make a copy of Workbook using 'Save As' command.
Open the new book, B, and hit the toolbar icon. The toolbar runs the macro in Workbook B.
Open Workbook A, and hit the toolbar icon - it opens book B and runs the macro from B, not A.

Now if you reassign the icon in book A to the macro in A, then book A is OK. But when you now open book B and hit the icon, it opens book A and runs the macro from A...

The custom icons on the toolbar seem to use the full path name to identify the macro, when all I want it to do is use the local macro.

Dirk, as you suggested, the possibility of building an Auto_start routine to assign the toolbar to the open workbook might work. I tried your first mini-procedure but couldn't get it to work. Any chance you could have another look at it?

Steve.

Balangan · Jan 21st 2004

Wow I finally made it work, thanks Derk
Here is what has to be done: I have 1 toolbar I want to update, its name is "Gestione". It contains both buttons (where you click and run a linked macro) and submenus, that contain more buttons each.
So this code will take care of the normal buttons:

Sub FixMenu()
Dim c As Object
For Each c In CommandBars("Gestione").Controls
c.OnAction = Mid(c.OnAction, InStr(c.OnAction, "!") + 1) 'get macro name
Next c
End Sub

Now for the submenus we have to use it some more, just filling now with the names of each submenu. My submenus is called "SCHEDA" :

Sub FixubMenu()
Dim c As Object
For Each c In CommandBars("Gestione").Controls("SCHEDA").Controls
c.OnAction = Mid(c.OnAction, InStr(c.OnAction, "!") + 1) 'get macro name
Next c
End Sub

this second macro will take care of the inner submenus buttons. Thanks a lot, Derk!

[Solved] VBA : Problem using macros when moving excel files

Participate now!

Share