Changing an existing connection name in VBA

Question

I need to update the connection name for excel SQL connection. This is my attempt. I was able to change the connection string and command text by doing a standard replacement.

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.

Answer 1

Try the following:

 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 

Answer 2

Apparently, Excel dynamically sorts the Connection.Item () array. Therefore, my modifications sent updated names at the bottom of the array. Before: FY2013Conn1, FY2013Conn2, FY2013Conn3, FY2013Conn4 After: FY2014Conn2, FY2014Conn3, FY2014Conn4, FY2014Conn1

It was hard to understand because I was dealing with 50+ connections. What I found effective, instead of trying to iterate over the entire set, only change the first element 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 the help.