Excel VBA;更新连接字符串 [英] Excel VBA; Updating a connection string

问题描述

我只是想让VBA更新一个OLEDB连接字符串。当我浏览代码时，我没有收到任何错误，但连接刷新失败，当我检查UI中的连接字符串时，显然我的代码根本没有改变（因此刷新失败）。我错过了什么？

I'm just trying to get VBA to update an OLEDB connection string. When I step through the code, I don't get any errors but the connection refresh fails and when I examine the connection string in the UI, it's obvious that my code has not changed it at all (hence the refresh failure). What have I missed?

这是代码：

Sub UpdateQueryConnectionString(ConnectionString As String)

  With ActiveWorkbook.Connections("Connection Name"). _
      OLEDBConnection
      .Connection = StringToArray(ConnectionString)
  End With
  ActiveWorkbook.Connections("Connection Name").Refresh
End Sub

提供的ConnectionString是：

The ConnectionString being fed in is:

ConnectionString = = "Provider=SLXOLEDB.1;Data Source=SERVER;Initial Catalog=DATABASE" _
& ";User ID=" & Username & ";Password=" & Password & _
";Persist Security Info=True;Extended Properties=" _
& Chr(34) & "PORT=1706;LOG=ON;CASEINSENSITIVEFIND=ON;INCLUDECALCFIELDS=ON;" & Chr(34)

函数StringToArray直接从示例4复制到 http://support.microsoft.com/kb/105416

The function StringToArray is copied straight out of Example 4 on http://support.microsoft.com/kb/105416

推荐答案

知道了以下代码已经起作用。

Got it. The following code has worked.

Sub UpdateQueryConnectionString(ConnectionString As String)

  Dim cn As WorkbookConnection
  Dim oledbCn As OLEDBConnection
  Set cn = ThisWorkbook.Connections("Connection Name")
  Set oledbCn = cn.OLEDBConnection
  oledbCn.Connection = ConnectionString

End Sub

只需在我的初始问题中说明的情况下，将ConnectionString输入为一个字符串。

Just feed ConnectionString in as a string like I illustrated in my initial question.

这篇关于Excel VBA;更新连接字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！