VBA to change Workbook data connections

[thommo41]

Hi,
This is really bugging me now. I found some snippits of code on the web and this forum, which helped me get to what I have. But I can't get it working!
My issue is we have a remote office, using an access DB which links to an Excel file. We sometimes send the files to other people to make changes, but the link is then broken (it was a network location).
You can just change it manually, or accept the many prompts that come up, but it needs doing at each end, each time.
I thought I'd do a routine to help them change the connection paths, this is what I have...

Sub alan()
Dim i As Long
Dim cnt As Long
Dim modtext As String, modfile As String
Dim modrange As String, OldPath As String, NewPath As String

OldPath = "\\192.168.1.100\Multimedia\Commissioning Folder\"
NewPath = "C:\matts\"

cnt = ActiveWorkbook.Connections.Count

For i = 1 To cnt
    modfile = ActiveWorkbook.Connections.Item(i).OLEDBConnection.SourceDataFile
    'MsgBox (Left(modfile, 7))
    If Left(modfile, 5) = Left(NewPath, 5) Then
    'Do nothing
    Else
        MsgBox (modfile)
        modfile = VBA.Replace(modfile, OldPath, NewPath)
        MsgBox (modfile)
        ActiveWorkbook.Connections.Item(i).OLEDBConnection.SourceDataFile = modfile
    End If
        modtext = ActiveWorkbook.Connections.Item(i).OLEDBConnection.Connection
        modtext = VBA.Replace(modtext, OldPath, NewPath)
        ActiveWorkbook.Connections.Item(i).OLEDBConnection.Connection = modtext
 Next i

End Sub

I added the sourcedatafile part to the routine afterwards, because the connection string part worked fine. But I now get Run-time error '1004' Application-defined or object-defined error...
Can anyone help me out? The IF part was for troubleshooting the problem, it's not essential I don't think?
Thanks, Alan

 

[jkpieterse]

I doubt if you need the SourceDatafile property, the connection property should do. Does it not?
SOmetimes the path is in both the Connection and in the CommandText.

 

[thommo41]

Morning. The Connection property alone seems ok, until you "refresh all" to fetch the data. Then you get an excel error, saying <oldpath> is not a valid path...

 

[thommo41]

Any ideas anyone?

 

[Abvlecxe]

Hi, sorry to open an old thread but I was wondering if you ever did find a solution to this problem as I am facing the same source file issue, thanks

Hi,
This is really bugging me now. I found some snippits of code on the web and this forum, which helped me get to what I have. But I can't get it working!
My issue is we have a remote office, using an access DB which links to an Excel file. We sometimes send the files to other people to make changes, but the link is then broken (it was a network location).
You can just change it manually, or accept the many prompts that come up, but it needs doing at each end, each time.
I thought I'd do a routine to help them change the connection paths, this is what I have...

Sub alan()
Dim i As Long
Dim cnt As Long
Dim modtext As String, modfile As String
Dim modrange As String, OldPath As String, NewPath As String

OldPath = "\\192.168.1.100\Multimedia\Commissioning Folder\"
NewPath = "C:\matts\"

cnt = ActiveWorkbook.Connections.Count

For i = 1 To cnt
    modfile = ActiveWorkbook.Connections.Item(i).OLEDBConnection.SourceDataFile
    'MsgBox (Left(modfile, 7))
    If Left(modfile, 5) = Left(NewPath, 5) Then
    'Do nothing
    Else
        MsgBox (modfile)
        modfile = VBA.Replace(modfile, OldPath, NewPath)
        MsgBox (modfile)
        ActiveWorkbook.Connections.Item(i).OLEDBConnection.SourceDataFile = modfile
    End If
        modtext = ActiveWorkbook.Connections.Item(i).OLEDBConnection.Connection
        modtext = VBA.Replace(modtext, OldPath, NewPath)
        ActiveWorkbook.Connections.Item(i).OLEDBConnection.Connection = modtext
 Next i

End Sub

I added the sourcedatafile part to the routine afterwards, because the connection string part worked fine. But I now get Run-time error '1004' Application-defined or object-defined error...
Can anyone help me out? The IF part was for troubleshooting the problem, it's not essential I don't think?
Thanks, Alan

 

[jkpieterse]

As I already said: the path is sometimes in both the connection string and the CommandText string. So you need to check both strings.