通过 VBA 更新 ODBC excel 链接上的路径
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15560525/
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
Updating paths on ODBC excel links through VBA
提问by Sebastien
I have a current ODBC link created a while back that is querying an Excel file. What I would like to do now is to through each ODBC Connection in the workbook and update the Connection String so it uses a different path where another .xls file of the same name is located.
我有一个当前创建的 ODBC 链接，它正在查询 Excel 文件。我现在想做的是通过工作簿中的每个 ODBC 连接并更新连接字符串，以便它使用另一个同名 .xls 文件所在的不同路径。
In other words, the current connection string as I see it in Excel (Data>Connections>Connections>1stConn Properties>Definition tab>Connection String) is the following:
换句话说，我在 Excel 中看到的当前连接字符串（数据>连接>连接>1stConn 属性>定义选项卡>连接字符串）如下：
DSN=Excel Files;DBQ=C:\TEST\CurrentQuarter.xls;DefaultDir=C:\TEST;DriverId=1046;MaxBufferSize=2048;PageTimeout=5;
and I wish to change it to:
我希望将其更改为：
DSN=Excel Files;DBQ=C:\OTHERTEST\CurrentQuarter.xls;DefaultDir=C:\OTHERTEST;DriverId=1046;MaxBufferSize=2048;PageTimeout=5;
I have tried this code:
我试过这个代码：
Sub SwitchODBCSource()
Dim conn As WorkbookConnection
For Each conn In ActiveWorkbook.Connections
With conn
'I have tried without succes the following 2 properties, without any luck:
.CommandText = "DSN=Excel Files;DBQ=C:\OTHERTEST\CurrentQuarter.xls;DefaultDir=C:\OTHERTEST;DriverId=1046;MaxBufferSize=2048;PageTimeout=5;"
.Connection = "DSN=Excel Files;DBQ=C:\OTHERTEST\CurrentQuarter.xls;DefaultDir=C:\OTHERTEST;DriverId=1046;MaxBufferSize=2048;PageTimeout=5;"
End With
Next conn
Set conn = Nothing
End Sub
Am I using an appropriate method on Connection ( .CommandText or .Connection) ? I have the feeling that I am not since VBA throws me an error "Object doesn't support this property or method"
我是否在 Connection（.CommandText 或 .Connection）上使用了适当的方法？我感觉我不是，因为 VBA 向我抛出一个错误“对象不支持此属性或方法”
In that case, the Object used is a QueryTable. Should I change Object and use that one ? I was under the impression that that user didn't want to connect to an .xls file..
在这种情况下，使用的对象是一个 QueryTable。我应该更改 Object 并使用那个吗？我的印象是该用户不想连接到 .xls 文件。
Any Help would be greatly appreciated !
任何帮助将不胜感激 ！
采纳答案by NickSlash
try conn.ODBCConnectionor conn.OLEDBConnectionas they do have the .commandtextand .connectionproperties.
尝试conn.ODBCConnection或conn.OLEDBConnection因为他们确实有.commandtext和.connection属性。
I've got no idea if this will allow you to change them though. I would have thought that you need to remove and then re-create the connections using the new connections string.
我不知道这是否会让你改变它们。我原以为您需要删除然后使用新的连接字符串重新创建连接。
回答by Sebastien
you are correct: conn.ODBCConnection.Connection is the way to go. And it is letting me change it with VBA! Awesome.
你是对的： conn.ODBCConnection.Connection 是要走的路。它让我用 VBA 改变它！惊人的。
Here is the Code that a generous MrExcel fellow suggested which is working fine (thanks Jerry):
这是一位慷慨的 MrExcel 研究员建议的代码，它运行良好（感谢 Jerry）：
Sub SwitchODBCSource()
Dim conn As WorkbookConnection
Dim sOldConnection As String, sNewConnection As String
Const sOldPath As String = "C:\TEST" '--omit trailing backslashes to change DefaultDir
Const sNewPath As String = "C:\OTHERTEST"
For Each conn In ActiveWorkbook.Connections
With conn
If .Type = xlConnectionTypeODBC Then
sOldConnection = .ODBCConnection.Connection
If InStr(1, sOldConnection, sOldPath) > 0 Then
sNewConnection = Replace(sOldConnection, _
sOldPath, sNewPath, Compare:=vbTextCompare)
.ODBCConnection.Connection = sNewConnection
.Refresh '--optional to refresh now
End If
End If
End With
Next conn
Set conn = Nothing
End Sub
Thanks Nick
谢谢尼克
Sebastien
塞巴斯蒂安
