none
VB Script to change Linked Workbook Connection RRS feed

  • 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