Edit Data Connection Source File

[Abvlecxe]

New to the site, hello to everyone.

I am looking for help with my code, basically I have two workbooks, wb1 connects to wb2 via a file data connection to retrieve table information. When the files are located in the same folders all is good and everything refreshes as required. However when I send the files to another office on a different server etc. the links obviously fall over.

To make it easy on the end user to update the source connection file I thought about a macro button "update connection" when pressed it would ask the user to select a new source file, so I hunted the net and came across the code below from 2016 which works in terms of asking the user to select a new file but once selected the data 5 query connections don't then update to the new source file, any help would be much appreciated, thanks in advance


Sub xlTest()

Dim i As Long
Dim cnt As Long
Dim cn

cnt = ActiveWorkbook.Connections.Count

'Choose a File
strPath = Application.GetOpenFilename(Title:="Choose a file", _
FileFilter:="Excel Files *.xls* (*.xls*),")

If strPath = False Then
MsgBox "No file selected. Exiting now.", vbExclamation
Exit Sub
End If
'Change Connection source filepath for each connection
For i = cnt To 1 Step -1
Set cn = ActiveWorkbook.Connections.Item(i)
cn.OLEDBConnection.SourceDataFile = strPath
Next i

End Sub

[jmcilhinney]

Welcome. For future reference, please use formatting tags when posting code for readability. The last two buttons on the editor toolbar provide two different options.

Sub xlTest()
 
Dim i As Long
Dim cnt As Long
Dim cn
 
cnt = ActiveWorkbook.Connections.Count
 
'Choose a File
strPath = Application.GetOpenFilename(Title:="Choose a file", _
FileFilter:="Excel Files *.xls* (*.xls*),")
 
If strPath = False Then
    MsgBox "No file selected. Exiting now.", vbExclamation
    Exit Sub
End If
'Change Connection source filepath for each connection
For i = cnt To 1 Step -1
    Set cn = ActiveWorkbook.Connections.Item(i)
    cn.OLEDBConnection.SourceDataFile = strPath
Next i
 
End Sub

[jmcilhinney]

Also, is that actually VB.NET code? Looks like it's either VBA or VB6, so this is the wrong forum. Decide which it is and use the Report Post icon under your post to ask the mods to move this thread to the correct forum.

[Abvlecxe]

Also, is that actually VB.NET code? Looks like it's either VBA or VB6, so this is the wrong forum. Decide which it is and use the Report Post icon under your post to ask the mods to move this thread to the correct forum.

Hi jmcilhinney,

Appreciate the info etc, was my first post but i'll know going forward

[si_the_geek]

Welcome to VBForums

Thread moved from the 'VB.Net' forum to the 'Office Development/VBA' forum.