Asked by:
VB Script to change Linked Workbook Connection

Question
-
HI Team -
I"m trying to write a Sub to change the workbook connection.
I have this so far but it's telling me there is no links, which is incorrect. Could you suggest the best way to do this?
Thanks!
set objexcel = createobject("Excel.Application") Set objworkbook1= objExcel.Workbooks.Open("C:\TEMP\TESTSOURCE.xlsx") Call FixLinksButton_Click objworkbook1.Save objworkbook1.Close Sub FixLinksButton_Click() NewPath = "C:\TEMP\TEMP2" alink = objworkbook1.LinkSources If IsEmpty(alink) Then MsgBox "No links found" Else For Idx = 1 To UBound(alink) If alink(Idx) Like "TESTTARGET.xlsx" Then ChDir NewPath ActiveWorkbook.ChangeLink alink(1), NewPath & Mid(alink(Idx), InStrRev(alink(Idx), "\")), xlExcelLinks End If Next End If End Sub
- Moved by Bill_Stewart Friday, July 27, 2018 8:22 PM This is not VBA support forum
Thursday, May 17, 2018 12:34 PM
All replies
-
If there are no defined URLs in the Workbook then the links will be empty.
\_(ツ)_/
Thursday, May 17, 2018 1:23 PM -
So my issue is I want to change the links and resave because I'm having conflicting errors versus 2007 and 2016 when folks open and save the files as then the ISAM error appears.
Or is there another method to solve this?
Thanks!
Thursday, May 17, 2018 1:27 PM -
If the workbooks are damaged you will have to fix them first.
Why do you need to change the urls? Have the web sites been changed?
\_(ツ)_/
Thursday, May 17, 2018 1:30 PM -
If this is VBA then you need to post in a VBA forum. If it is VBScript then the coding is all wrong. Did you copy VBA code to make this? VBA code will not work in VBScript.
\_(ツ)_/
Thursday, May 17, 2018 1:31 PM -
No, I want this to be done in VB Script.
I have tried this to strip off the 42 chracters that 2010 & 2016 save the connection as:
Set objExcel = CreateObject("Excel.Application") Set objworkbook1= objExcel.Workbooks.Open("C:\TEMP\TESTTARGET.xlsx") Call RepairConnections Sub RepairConnections() 'if the data was refreshed in excel 2010 and then refreshed in excel 2007, the connection strings would 'be changed and the mode would switch from read-only to share-write-deny which would lock things up 'most everyone is on 2010 now Dim cn As WorkbookConnection Dim oledbCn As OLEDBConnection Dim i As Long i = 0 For Each cn In objworkbook1.Connections Set oledbCn = cn.OLEDBConnection If Right(oledbCn.Connection, 42) = "Jet OLEDB:Bypass UserInfo Validation=False" Then i = i + 1 oledbCn.Connection = Left(oledbCn.Connection, Len(oledbCn.Connection) - 43) 'take that last part off End If Next If i = 0 Then MsgBox ("Done. No connections needed repair.") Else MsgBox ("Done. " & i & " connection(s) needed repair.") End If End Sub
But having trouble.
Thursday, May 17, 2018 1:33 PM -
This is the correct command for VBScript:
links = objWorkbook.LinkSources(1)
objWorkbook.ChangeLink "c:\excel\book1.xls", "c:\excel\book2.xls", 1
\_(ツ)_/
Thursday, May 17, 2018 1:38 PM -
I recommend that you do this in PowerShell. Since you do not know VBScript/VBA then don't waste time learning it. Learn PowerShell and you will see that you can do this interactively at a prompt and test and inspect each step until you get what you want.
Note that we do not fix code you have found. You need to be a scripter and know what your code is doing.
\_(ツ)_/
Thursday, May 17, 2018 1:41 PM -
Hi -
I've been able to solve for this using VBSCRIPT:
Sub RepairConnections() Dim Find, Rpl Find = ";Jet OLEDB:Bypass UserInfo Validation=False;Jet OLEDB:Limited DB Caching=False;Jet OLEDB:Bypass ChoiceField Validation=False" Rpl = "" i = 0 For Each cn In objworkbook1.Connections Set oledbCn = cn.OLEDBConnection If InStr(oledbCn.Connection, Find) Then i = i + 1 oledbCn.Connection = Replace(oledbCn.Connection,Find,Rpl) objworkbook1.Save End If Next If i = 0 Then WScript.Echo "The current external workbook connection(s) does not need to be repaired." Else WScript.Echo i & " external workbook connection(s) needed to be repaired." End If End Sub
Thanks!Thursday, May 17, 2018 5:38 PM -
That is not the question you asked. Connections are to a DB and not a link to an external web site or file.
In PowerShell this looks like this:
$xl.Workbooks[1].Connections
\_(ツ)_/
Thursday, May 17, 2018 5:44 PM