Remove From My Forums

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

0

Sign in to vote

If there are no defined URLs in the Workbook then the links will be empty.

\_(ツ)_/

Thursday, May 17, 2018 1:23 PM
0

Sign in to vote

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
0

Sign in to vote

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
0

Sign in to vote

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

0

Sign in to vote

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
0

Sign in to vote

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

0

Sign in to vote

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

Asked by:

VB Script to change Linked Workbook Connection

Question

All replies