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.
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