We help IT Professionals succeed at work.

Setting an Excel worksheet's connections to another Excel worksheet using VBA.

stephenlecomptejr
stephenlecomptejr used Ask the Experts™
on
Per the following VBA code, all I'm trying to do is change the existing connection from a network file - to one that is copied locally but I'm unsure what the syntax should be to allow it to work.

I get a Run-time error '9' error:  subscript out of range.
with the yellow highlight on line:  

ActiveWorkbook.Connections.Item(sFileAbbv).OLEDBConnection.SourceDataFile = sLocalFileName

Open in new window


Some worksheets connect to the local file - others do not..
What should the correct syntax should be?  

The following works correctly:

Call LocalCopyAndConnect(""Q:\MyNetworkFolder\Claim Library BCV.xlsx", C:\XOM\SPTtemp\Claim Library BCV.xlsx")

Open in new window


The following calls did not work correctly:

Call LocalCopyAndConnect("Q:\MyNetworkFolder\PDS Content Reports by BMS-BTS-Subzone BCV.xlsx", "C:\XOM\SPTtemp\Claim Library BCV.xlsx")
Call LocalCopyAndConnect("Q:\MyNetworkFolder\PDS Content Reports by BMS-BTS-Subzone BCV.xlsx", "C:\XOM\SPTtemp\PDS Content Reports by BMS-BTS-Subzone BCV.xlsx")

Open in new window



I thought maybe it was the length of the filename with symbols but as you see a smaller version did not go through as well.
How do I investigate what's wrong with the .xlsx file to cause it to throw a run time error 9 ?  It should work cause it was previously connected to the network version.

Public Sub LocalCopyAndConnect(sNetworkFileName As String, sLocalFileName as String)

  If Dir(sNetworkFileName ) <> "" Then
    'On Error GoTo Error_Sub
    FileCopy sNetworkFileName , sLocalFileName 
    DoEvents
    ' need to ensure file is copied over with the same modified date and info
    ' because there may be a delay in file being copied over.
  End If

  If Dir(sLocalFull) <> "" Then
    ActiveWorkbook.Connections.Item(sFileAbbv).OLEDBConnection.SourceDataFile = sLocalFileName
    ActiveWorkbook.Connections(sFileAbbv).Refresh
    
  End If
  
Exit_Sub:
  Exit Sub
  

End Sub

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Qlemo"Batchelor", Developer and EE Topic Advisor
Top Expert 2015

Commented:
Your variable sFileAbbv is set somewhere else, and might be wrong.

Author

Commented:
sFileAbbv is the name of the worksheet.
Qlemo"Batchelor", Developer and EE Topic Advisor
Top Expert 2015

Commented:
No, it is a variable which should contain the name or index of the worksheet. Or did you really rename the automatically generated sheet variable in the VBA Editor?

Author

Commented:
Originally was:

Public Sub LocalCopyAndConnect(sFileAbbv As String, sNetworkFileName As String, sLocalFileName as String)

Open in new window

"Batchelor", Developer and EE Topic Advisor
Top Expert 2015
Commented:
So I'm correct, it is a variable, and might not be set.