Quick way to fix multiple links in Excel

I have moved an excel file to another location and need to sort out all connections to the access file that populates the spreadsheet. Is there a way to do this without one by one through the connection manager.

Thank you for any assistance.
GrahamSAAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

vguzmanIT ManagerCommented:
This is a VBA macro I use to delete or fix the links on any worksheet, you will need to fix it to meet your needs, depending you new location (path) etc. Ask if you need help.

http://www.exceltip.com/st/List,_change_or_delete_external_formula_references_%28links%29_using_VBA_in_Microsoft_Excel/487.html
0
GrahamSAAuthor Commented:
Thank you for this
I am going to try figure this all out but will no doubt need a little help...
0
GrahamSAAuthor Commented:
OK
There is some action here.
I am getting the formula lists but need to edit the file location. I need to change  C:\Users\Gordon\Desktop to C:\Users\Graham\Desktop and also in the connection string.

Thanks again
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

gowflowCommented:
GrahamSA
Pls allow me this comment and do not take it as an offence,
BUT I'v never ever seen a question with 20 points to award !!!

gowflow
0
GrahamSAAuthor Commented:
Missed a 0 sorry friend
0
gowflowCommented:
no worries
I need to change  C:\Users\Gordon\Desktop to C:\Users\Graham\Desktop and also in the connection string.
>>> Will search replace help ?
or else do u need a macro if yes pls specify what you need to change
gowflow
0
GrahamSAAuthor Commented:
A search and replace will do perfectly, where do I find the list of all the connections and stuff so I don't have to do this one at a time? I am very new to all this and kind of thrown in the deep end. Trying to learn as much as possible while at the same time getting the job done.


0
gowflowCommented:
can you post the workbook I could then do it for you ? or it wold be easier to draft a macro is it is necessary.
gowflow
0
GrahamSAAuthor Commented:
Lesedi-Snapshot.xlsm

Here you go
There is still a lot to do on this file but when I am finished I will need to put it on a few machines and will need to go though all the connections and reroute to make the refresh work.

If only i could find the page of text which has all the connections listed I would be able to do the search replace, would this not be the easiest way to do this?

Thanks gowflow
0
gowflowCommented:
I have build a routine and testing it but sometimes I am getting an error sometimes not can you post one of the link files
C:\Users\Graham\Desktop\Lesedi_Reports\Access_Connect\Contract_Summary.accdb
or an other one I guess there are 3 in this workbook
gowflow
0
GrahamSAAuthor Commented:
Wow Thanks for this
Here you go!
 Contract-Summary.accdb
0
gowflowCommented:
Here is it GrahamSA,

Pls do the following:
1) Make a new copy of your latest production workbook and save it to a new name (so you keep the old one in case something goes wrong as a backup)
2) Activate the macros once you run this new created file.
3) Press on Developper menu and choose Visual Basic
4) Right click on the name of your file in the left pane and choose Insert/Module
5) If it is the same fiel you send me then it will create Module6 for you if not check out what module it created and doubleclik on it so it displays in the right pane a blank page with Declaration on top
6) SELECT ALL from the below code right click in the code and select COPY
7) Paste the code in this new module.
8) Save the workbook.
9) Display your first sheet on the workbook
10) Select from the Developper Menu Macro and from the list choose RenameDataTable and press Run

You will be prompt for each connection a file to replace. By default it will open in the same location of the existing workbook, look well in the top screen for the old datasource name and choose the correct new datasource and press open you will receive a msgbox wit the result untill all connections are done. The fille will autosave

Pls try it and give me your feedback.
gowflow
Sub RenameDataTable()
Dim WB As Workbook
Dim WS As Worksheet
Dim Conection As Connections
Dim NewSourceDataFile As String, SourceDataFile As String


Set WB = ActiveWorkbook
Set Conection = WB.Connections


For I = 1 To Conection.Count
    If Conection.Item(I).OLEDBConnection.SourceDataFile <> SourceDataFile Then
        SourceDataFile = Conection.Item(I).OLEDBConnection.SourceDataFile
        With Application.FileDialog(msoFileDialogFilePicker)
            .InitialFileName = Application.ActiveWorkbook.Path
            .Filters.Add "Database File", "*.accdb", 1
            .Title = "Please Select New DataSource file to Replace " & Conection.Item(I).OLEDBConnection.SourceDataFile
            .Show
            NewSourceDataFile = ""
            For Each vrtSelectedItem In .SelectedItems
                If vrtSelectedItem = "" Then Exit Sub
                NewSourceDataFile = vrtSelectedItem
            Next vrtSelectedItem
        End With
    End If
    If NewSourceDataFile <> "" Then

    With Conection.Item(I).OLEDBConnection
        .Connection = Application.WorksheetFunction.Substitute(.Connection, SourceDataFile, NewSourceDataFile)
        MsgBox ("Data Source:" & Chr(9) & "[" & .CommandText & "]" & Chr(10) _
            & "Old Location:" & Chr(9) & "[" & SourceDataFile & "]" & Chr(10) _
            & ">>> Renamed to" & Chr(10) _
            & "New Location:" & Chr(9) & "[" & NewSourceDataFile & "]")
        '.CommandText = StringToArray(Application.WorksheetFunction.Substitute(.CommandText, SourceDataFile, NewSourceDataFile))
        '.Refresh
    End With
    End If
Next I
WB.Save
MsgBox ("Total of " & I & "Connection Data Source have been updated successfully")

End Sub

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
GrahamSAAuthor Commented:
Thank you gowflow!!!

I copied pasted the folder containing all the files in question and ran the macro as you instructed and it worked perfectly.

This file when it is finished will have a huge amount of connections so ideally you have save me hours of time.

NICE WORK.!
0
gowflowCommented:
I am glad I could help you. Tks for the grade and pls do not spare me if you have any other issue, if it is an imminent one post the link in here and I will assist you.
rgds/gowflow
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.