我需要更新 Excel 工作簿的 SQL 连接的连接名称。这是我的尝试。我已经能够通过执行标准替换来修改连接字符串和命令文本。
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
任何帮助都会很棒。谢谢。
尝试这个:
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
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)