Modify existing excel Connection Name in VBA

问题:

I need to update the Connection Name of an excel workbook's sql connection. This is my attempt. I have been able to modify the Connection String and Command Text by doing a standard replace.

Sub ConnectionString_modify()

Dim i As Long
Dim cnt As Long
Dim modtext As String
Dim modrange As String

'Grab nummber of workbook connections
cnt = ActiveWorkbook.Connections.Count

For i = 1 To cnt

    'Changes to Connection string --This works
    modtext = ActiveWorkbook.Connections.Item(i).OLEDBConnection.Connection
    modtext = VBA.Replace(modtext, "_FY2013", "_FY2014")
    ActiveWorkbook.Connections.Item(i).OLEDBConnection.Connection = modtext

    'Changes Connection Name
    modname = ActiveWorkbook.Connections.Item(i).Name
    modname = VBA.Replace(modname, "_FY2013", "_FY2014")
    ActiveWorkbook.Connections.Item(i).Name = modname 
 Next i

End sub

Any help would be great. Thanks.

回答1:

Try this:

Sub ConnectionString_modify()

Dim i As Long
Dim cnt As Long
Dim modtext As String
Dim modrange As String
Dim conn

'Grab nummber of workbook connections
cnt = ActiveWorkbook.Connections.Count

For i = cnt To 1 Step -1

    Set conn = ActiveWorkbook.Connections.Item(i)

    modtext = conn.OLEDBConnection.Connection
    modtext = VBA.Replace(modtext, "_FY2013", "_FY2014")
    conn.OLEDBConnection.Connection = modtext

    conn.Name = VBA.Replace(conn.Name, "_FY2013", "_FY2014")

Next i

End sub

回答2:

Apparently, Excel dynamicly sorts the Connection.Item() array. So my modifications were sending the updated Names to the bottom of the array. Before: FY2013Conn1, FY2013Conn2, FY2013Conn3, FY2013Conn4 After: FY2014Conn2, FY2014Conn3, FY2014Conn4, FY2014Conn1

This was hard to see because I was dealing with 50+ connections. What I found to be effective was instead of trying to iterate through the entire set, only modify the first item in the array.

'Connection Count
cnt = ActiveWorkbook.Connections.Count 

While cnt > 0
   'Always modify the first Item 
   Set conn = ActiveWorkbook.Connections.Item(1)

   'Mod code makes changes to Command Text
    modtext = conn.OLEDBConnection.Connection
    modtext = VBA.Replace(modtext, "_FY2013", "_FY2014")
    conn.OLEDBConnection.Connection = modtext

    'Changes Connection Name
    conn.Name = VBA.Replace(conn.Name, "_FY2013", "_FY2014")

    'Iterate through the cnt
     cnt = cnt - 1
  Wend

Thanks for everyone's help.