vba 在VBA中修改现有的excel连接名称
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18837971/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Modify existing excel Connection Name in VBA
提问by thomas398
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.
我需要更新 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
Any help would be great. Thanks.
任何帮助都会很棒。谢谢。
回答by Tim Williams
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
回答by thomas398
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
显然，Excel 动态排序 Connection.Item() 数组。所以我的修改是将更新的 Names 发送到数组的底部。之前：FY2013Conn1、FY2013Conn2、FY2013Conn3、FY2013Conn4 之后：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.
这很难看到，因为我正在处理 50 多个连接。我发现有效的是，不是尝试遍历整个集合，而是只修改数组中的第一项。
'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.
谢谢大家的帮助。
