VBA To Change Workbook Data Connections

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 change the connection paths, this is what I have...

Code:
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.100MultimediaCommissioning Folder"
NewPath = "C:matts"

[Code] ...

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...