[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Quick way to fix multiple links in Excel

Posted on 2011-10-08
14
Medium Priority
?
347 Views
Last Modified: 2012-06-27
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.
0
Comment
Question by:GrahamSA
  • 7
  • 6
14 Comments
 
LVL 5

Expert Comment

by:vguzman
ID: 36936742
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
 

Author Comment

by:GrahamSA
ID: 36936813
Thank you for this
I am going to try figure this all out but will no doubt need a little help...
0
 

Author Comment

by:GrahamSA
ID: 36937017
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 31

Expert Comment

by:gowflow
ID: 36937082
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
 

Author Comment

by:GrahamSA
ID: 36937108
Missed a 0 sorry friend
0
 
LVL 31

Expert Comment

by:gowflow
ID: 36937910
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
 

Author Comment

by:GrahamSA
ID: 36939017
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
 
LVL 31

Expert Comment

by:gowflow
ID: 36939088
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
 

Author Comment

by:GrahamSA
ID: 36941340
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
 
LVL 31

Expert Comment

by:gowflow
ID: 36943834
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
 

Author Comment

by:GrahamSA
ID: 36943979
Wow Thanks for this
Here you go!
 Contract-Summary.accdb
0
 
LVL 31

Accepted Solution

by:
gowflow earned 2000 total points
ID: 36945679
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
 

Author Closing Comment

by:GrahamSA
ID: 36947049
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
 
LVL 31

Expert Comment

by:gowflow
ID: 36948543
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

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

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

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

834 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