替换Excel连接字符串中的值

Question

谁能建议一个VBA过程，将取代一个Excel SQl Server OLEDB连接字符串的元素?

我已经尝试了下面的代码，但我认为这与dbconnection变量的数据类型有关。

Sub ModifyConnection()
    Dim ComHostname As String
    ComHostname = Environ$("computername")
    Call MsgBox(ComHostname)
    Dim dbconnection As String
    dbconnection = ActiveWorkbook.Connections("ConnectionName").OLEDBConnection.connection
    dbconnection = Replace(dbconnection, "___BTN_____", ComHostname & "")
    Set ActiveWorkbook.Connections("ConnectionName").OLEDBConnection.connection = dbconnection
 End Sub

代码试图用当前计算机的主机名替换连接字符串中的计算机主机名实例。

抛出错误424 - object required

Answer 1

原来这是一个简单的连接名称错误的情况。操作代码如下:

Sub ModifyConnection()
    Dim ComHostname As String
    ComHostname = Environ$("computername")
    Call MsgBox(ComHostname)
    Dim dbconnection As String
    dbconnection =     ActiveWorkbook.Connections("ConnectionName").OLEDBConnection.connection
    dbconnection = Replace(dbconnection, "???BTN?????", ComHostname & "")
    ActiveWorkbook.Connections("ConnectionName").OLEDBConnection.connection = dbconnection
End Sub