• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Changing connections sources automatically

S

saeed

New Member
Good Day Dear Experts,

I need your help with the following situation:
We generate monthly reports for various departments.
There are hundreds of interconnected Excel files in many different folders.
Each month the files are updated and renamed to reflect the month (e.g. basic_report_08.xls for August and basic_report_09.xls for September).
Now, all these connections have to be changed, manually! We open each Excel file, go to the "Connections" on the menu and click on "change source" and search for the new file. There are anywhere between 5 to 20 connections per file! How error-prone und tedious this is, is easy to imagine.

My question: is there anyway to automate this operation? Possibly a macro which would parse ALL sheets, plenty in each workbook, and replaces the 08s with 09s in the filenames ONLY. And next month 09s with 10s und so on.

If this could be done without opening each and every file, i.e. some sort of a batch operation, that would be the icing on the cake.

Your help is much appreciated.
 
This is a very basic way to do it. Can you test this for one of the linked files? If this works, then I'll create a more slicker version

Code: 
Sub UpdateWorkbookLink()

    Dim wbk As Workbook
    Const strOldLink As String = "C:\Users\Sam Mathai\Downloads\File 08.xlsx"
    Const strNewLink As String = "C:\Users\Sam Mathai\Downloads\File 09.xlsx"
    Set wbk = ThisWorkbook 'Or ActiveWorkbook depending on where you are running this code from
    wbk.ChangeLink Name:=strOldLink, NewName:=strNewLink, Type:=xlExcelLinks
    
End Sub
 
Hi Sam ,

Going by the connection that OP is mentioning , can we not use the Workbook.Connections method to return a WorkbookConnection object , and replace the "08" in the WorkbookConnection.Name property by "09" ?

Narayan
 
Much obliged Sam, it works! May I then ask for the next step(s)?

There are quite a few connections in each file, each file with plenty of sheets.
And there are few hundred files.
The optimum solution would be to determine ALL connections in each Workbook automatically, ask the user for the new name, e.g. 10 so on, and then replace the old name with the new one.

Questions:
1. How can a macro be used in ALL files without copying to each?
2. How can I get a list of all connections for each Workbook?
3. Is it possible to perform these operations without opening each and every file, i.e. some sort of batch job?



Once again, thanks a lot.
 
saeed, can you test this in one of the files first, and see if it's working to plan. If yes, then I'll create one that looks at a list of workbooks in a range of cells, and find out the linked path for each of the workbooks, and have an easy way to update them

By the way, I do not know of a way to do this without opening the workbooks (but happy to learn that if anybody out there knows how to do it)

Here's the code

Code: 
Sub UpdateLinks()

    Dim strvarLink As Variant
    Dim lngCount As Long, lngIndex As Long
    Dim strLinkSources() As String, strNewLink As String
    
    Dim wbk As Workbook
    Set wbk = ThisWorkbook 'Or ActiveWorkbook depending on where you are running this code from
    lngCount = UBound(wbk.LinkSources(xlLinkTypeExcelLinks))
    ReDim strLinkSources(1 To lngCount, 1 To 2)
    For Each strvarLink In wbk.LinkSources(xlLinkTypeExcelLinks)
        lngIndex = lngIndex + 1
        strLinkSources(lngIndex, 1) = strvarLink
ReDo:   strNewLink = InputBox("What do you want to change the following link to?" & vbLf & vbLf & strvarLink, "Update Link", strvarLink)
        If Dir(strNewLink) <> "" And strNewLink <> "" Then
            strLinkSources(lngIndex, 2) = strNewLink
            strNewLink = vbNullString
        Else
            If vbYes = MsgBox("Unable to locate this file. Please ensure this file exists and is accessible from your current environment." & vbLf & vbLf & _
                "Do you want to try again?", vbYesNo + vbQuestion, "File Not Found!") Then
                GoTo ReDo
            Else
                MsgBox "Cancelled by user!", vbOKOnly, ""
                Exit Sub
            End If
        End If
    Next strvarLink
    For lngIndex = 1 To lngCount
        wbk.ChangeLink Name:=strLinkSources(lngIndex, 1), NewName:=strLinkSources(lngIndex, 1), Type:=xlExcelLinks
    Next lngIndex
   
End Sub

Narayan, not sure if linked Excel files come as part of connections. If it does, then I guess yes we could try something on your lines also. But I am not sure if that's possible with the connections collection.
 
Hi Sam ,

My mistake ; I was misled by the use of the word connections , when probably what was meant was external links.

Narayan
 
Hi Sam,

It works, but it took me a while to figure out what the code does!

I was just wondering, is it possible to save the subroutine as VB executable file? Similar to C or Java programs?
Is there such a thing as VB executable?
I could then invoke the file and let it run.
 
saeed, not good at writing VB executable file. Sorry. Can anyone out there help saeed?

I would ask though, what's keeping you from running this from Excel?
 
Come to think of it Sam, you are right! I can save the macro to the personal macro workbook file and run it on all my files.
One last question though: for documentation purposes, is it possible to know where the links are?
I mean which sheet has them? Our files could have as many as 60 sheets, it helps keep track of them.

Once again, many thanks.
Saeed
 
You must log in or register to reply here.
Back
Top