vba 在 microsoft excel 宏中修改嵌入的连接字符串

Modify an embedded Connection String in microsoft excel macro

提问by lukemh

I have an Excel document that has a macro which when run will modify a CommandTextof that connection to pass in parameters from the Excel spreadsheet, like so:

我有一个 Excel 文档，它有一个宏，运行时将修改CommandText该连接的一个以从 Excel 电子表格中传递参数，如下所示：

Sub RefreshData()
ActiveWorkbook.Connections("Job_Cost_Code_Transaction_Summary")
  .OLEDBConnection.CommandText = "Job_Cost_Code_Transaction_Summary_Percentage_Pending @monthEndDate='" & Worksheets("Cost to Complete").Range("MonthEndDate").Value & "', @job ='" & Worksheets("Cost to Complete").Range("Job").Value & "'"
ActiveWorkbook.Connections("Job_Cost_Code_Transaction_Summary").Refresh
End Sub

I would like the refresh to not only modify the connection command but also modify the connection as I would like to use it with a different database also:

我希望刷新不仅可以修改连接命令，还可以修改连接，因为我还想将它与不同的数据库一起使用：

Just like the macro replaces the command parameters with values from the spreadsheet I would like it to also replace the database server name and database name from values from the spreadsheet.

就像宏用电子表格中的值替换命令参数一样，我希望它还可以从电子表格的值中替换数据库服务器名称和数据库名称。

A complete implementation is not required, just the code to modify the connection with values from the sheet will be sufficient, I should be able to get it working from there.

不需要完整的实现，只需使用工作表中的值修改连接的代码就足够了，我应该能够从那里开始工作。

I tried to do something like this:

我试图做这样的事情：

 ActiveWorkbook
 .Connections("Job_Cost_Code_Transaction_Summary")
 .OLEDBConnection.Connection = "new connection string"

but that does not work. Thanks.

但这不起作用。谢谢。

采纳答案by lukemh

The answer to my question is below.

我的问题的答案如下。

All of the other answers are mostly correct and focus on modifying the current connection, but I want just wanting to know how to set the connection string on the connection.

所有其他答案大部分都是正确的，并且专注于修改当前连接，但我只想知道如何在连接上设置连接字符串。

The bug came down to this. If you look at my screenshot you will see that the connection string was:

错误归结为这一点。如果您查看我的屏幕截图，您会看到连接字符串是：

Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;Initial Catalog=ADCData_Doric;Data Source=doric-server5;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=LHOLDER-VM;Use Encryption for Data=False;Tag with column collation when possible=False

I was trying to set that string with ActiveWorkbook.Connections("Job_Cost_Code_Transaction_Summary").OLEDBConnection.Connection = "connection string"

我试图用 ActiveWorkbook.Connections("Job_Cost_Code_Transaction_Summary").OLEDBConnection.Connection = "connection string"

I was getting an error when i was simply trying to assign the full string to the Connection. I was able to MsgBox the current connection string with that property but not set the connection string back without getting the error.

当我只是尝试将完整字符串分配给 Connection 时出现错误。我能够使用该属性将当前连接字符串发送到 MsgBox，但无法在没有收到错误的情况下将连接字符串设置回去。

I have since found that the connection string needs to have OLEDB;prepended to the string.

从那以后，我发现连接字符串需要OLEDB;预先添加到字符串中。

so this now works!!!

所以这现在有效！！！

ActiveWorkbook.Connections("Job_Cost_Code_Transaction_Summary").OLEDBConnection.Connection = "OLEDB;Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;Initial Catalog=ADCData_Doric;Data Source=doric-server5;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=LHOLDER-VM;Use Encryption for Data=False;Tag with column collation when possible=False"

very subtle but that was the bug!

非常微妙，但这就是错误！

回答by reverpie

I would like to give my small contribute here to this old topic. If you have many connections in your Excel file, and you want to change the DB name and DB server for all of them, you can use the following code as well:

我想在这里为这个古老的话题做一点小小的贡献。如果您的 Excel 文件中有多个连接，并且您想更改所有连接的数据库名称和数据库服务器，您也可以使用以下代码：

• It iterates through all connections and extracts the connection string
• Each connection string is split into an array of strings
• It iterates through the array searching for the right connection values to modify, the others are not touched
• The it recompose the array into the string and commit the change

• 它遍历所有连接并提取连接字符串
• 每个连接字符串被拆分成一个字符串数组
• 它遍历数组，搜索要修改的正确连接值，其他不受影响
• 它将数组重组为字符串并提交更改

This way you don't need to use replace and to know the previous value, and the rest of the string will remain intact. Also, we can refer to a cell name, so you can have names in your Excel file

这样您就不需要使用替换并知道以前的值，并且字符串的其余部分将保持不变。此外，我们可以引用单元格名称，因此您可以在 Excel 文件中使用名称

I hope it can help

我希望它可以帮助

Sub RelinkConnections()

Dim currConnValues() As String

For Each currConnection In ThisWorkbook.Connections
    currConnValues = Split(currConnection.OLEDBConnection.Connection, ";")
    For i = 0 To UBound(currConnValues)
        If (InStr(currConnValues(i), "Initial Catalog") <> 0) Then
            currConnValues(i) = "Initial Catalog=" + Range("DBName").value
        ElseIf (InStr(currConnValues(i), "Data Source") <> 0) Then
            currConnValues(i) = "Data Source=" + Range("DBServer").value
        End If
    Next
    currConnection.OLEDBConnection.Connection = Join(currConnValues, ";")
    currConnection.Refresh
Next

End Sub

回答by citizenkong

You could use a function that takes the OLEDBConnection and the parameters to be updated as inputs, and returns the new connection string. It's similar to Jzz's answer but allows some flexibility without having to edit the connection string within the VBA code each time you want to change it - at worst you'd have to add new parameters to the functions.

您可以使用将 OLEDBConnection 和要更新的参数作为输入并返回新连接字符串的函数。它类似于 Jzz 的答案，但具有一定的灵活性，而无需在每次要更改 VBA 代码时编辑连接字符串 - 最坏的情况是您必须向函数添加新参数。

Function NewConnectionString(conTarget As OLEDBConnection, strCatalog As String, strDataSource As String) As String

    NewConnectionString = conTarget.Connection
    NewConnectionString = ReplaceParameter("Initial Catalog", strCatalog)
    NewConnectionString = ReplaceParameter("Data Source", strDataSource)

End Function

Function ReplaceParameter(strConnection As String, strParamName As String, strParamValue As String) As String

    'Find the start and end points of the parameter
    Dim intParamStart As Integer
    Dim intParamEnd As Integer
    intParamStart = InStr(1, strConnection, strParamName & "=")
    intParamEnd = InStr(intParamStart + 1, strConnection, ";")


    'Replace the parameter value
    Dim strConStart As String
    Dim strConEnd As String
    strConStart = Left(strConnection, intParamStart + Len(strParamName & "=") - 1)
    strConEnd = Right(strConnection, Len(strConnection) - intParamEnd + 1)

    ReplaceParameter = strConStart & strParamValue & strConEnd

End Function

Note that I have modified this from existing code that I have used for a particular application, so it's partly tested and might need some tweaking before it totally meets your needs.

请注意，我已根据用于特定应用程序的现有代码对其进行了修改，因此它经过了部分测试，可能需要进行一些调整才能完全满足您的需求。

Note as well that it'll need some kind of calling code as well, which would be (assuming that the new catalog and data source are stored in worksheet cells):

还要注意，它还需要某种调用代码，即（假设新目录和数据源存储在工作表单元格中）：

Sub UpdateConnection(strConnection As String, rngNewCatalog As Range, rngNewSource As Range)

    Dim conTarget As OLEDBConnection
    Set conTarget = ThisWorkbook.Connections.OLEDBConnection(strConnection)

    conTarget.Connection = NewConnectionString(conTarget, rngNewCatalog.Value, rngNewSource.Value)
    conTarget.Refresh

End Sub

回答by PatricK

I think you are so close to achieve what you want.

我认为你离实现你想要的已经很近了。

I was able to change for ODBCConnection. Sorry that I couldn't setup OLEDBConnectionto test, you can change occurrences of ODBCConnectionto OLEDBConnectionin your case.

我能够更改ODBCConnection。抱歉，我无法设置OLEDBConnection进行测试，您可以在您的情况下将ODBCConnection 的出现次数更改为OLEDBConnection。

Try add this 2 subs with modification, and throw in what you need to replace in the CommandTextand Connection String. Note I put .Refreshto update the connection, you may not need until actual data refresh is needed.

尝试添加这 2 个 subs 并进行修改，并在CommandText和Connection String 中添加您需要替换的内容。注意我是.Refresh为了更新连接，在需要实际数据刷新之前您可能不需要。

You can change other fields using the same idea of breaking things up then Join it later:

您可以使用相同的拆分想法更改其他字段，然后稍后加入：

Private Sub ChangeConnectionString(sInitialCatalog As String, sDataSource As String)
    Dim sCon As String, oTmp As Variant, i As Long
    With ThisWorkbook.Connections("Job_Cost_Code_Transaction_Summary").ODBCConnection
        sCon = .Connection
        oTmp = Split(sCon, ";")
        For i = 0 To UBound(oTmp) - 1
            ' Look for Initial Catalog
            If InStr(1, oTmp(i), "Initial Catalog", vbTextCompare) = 1 Then
                oTmp(i) = "Initial Catalog=" & sInitialCatalog
            ' Look for Data Source
            ElseIf InStr(1, oTmp(i), "Data Source", vbTextCompare) = 1 Then
                oTmp(i) = "Data Source=" & sDataSource
            End If
        Next
        sCon = Join(oTmp, ";")
        .Connection = sCon
        .Refresh
    End With
End Sub

Private Sub ChangeCommanText(sCMD As String)
    With ThisWorkbook.Connections("Job_Cost_Code_Transaction_Summary").ODBCConnection
        .CommandText = sCMD
        .Refresh
    End With
End Sub

回答by Jzz

This should do the trick:

这应该可以解决问题：

Sub jzz()

Dim conn As Variant
Dim connectString As String

For Each conn In ActiveWorkbook.Connections
    connectString = conn.ODBCConnection.Connection
    connectString = Replace(connectString, "Catalog=ADCData_Doric", "Catalog=Whatever")
    connectString = Replace(connectString, "Data Source=doric-server5", "Data Source=Whatever")

    conn.ODBCConnection.Connection = connectString
Next conn


End Sub

It loops every connection in your workbook and change the Connection String (in the 2 replace statements).

它循环工作簿中的每个连接并更改连接字符串（在 2 个替换语句中）。

So to modify your example:

所以要修改你的例子：

ActiveWorkbook.Connections("Job_Cost_Code_Transaction_Summary").ODBCConnection.Connection = "new connection string"

回答by Andy G

I assume it is necessary for your to keep the same connection-name? Otherwise, it would be simplest to ignore it and create a new Connection.

我认为您有必要保持相同的连接名称吗？否则，最简单的方法是忽略它并创建一个新连接。

You might rename the connection, and create a new one using the name:

您可以重命名连接，并使用名称创建一个新连接：

ActiveWorkbook.Connections("Job_Cost_Code_Transaction_Summary").Name = "temp"
'or, more drastic:
'ActiveWorkbook.Connections("Job_Cost_Code_Transaction_Summary").Delete

ActiveWorkbook.Connections.Add "Job_Cost_Code_Transaction_Summary", _
    "a description", "new connection string", "command text" '+ ,command type

Afterwards, Deletethis connection and reinstate the old connection/name. (I am unable to test this myself currently, so tread carefully.)

之后，Delete此连接并恢复旧的连接/名称。（我目前无法自己测试，所以请谨慎行事。）

Alternatively, you might change the current connections SourceConnectionFile:

或者，您可以更改当前连接SourceConnectionFile：

ActiveWorkbook.Connections("Job_Cost_Code_Transaction_Summary").OLEDBConnection.SourceConnectionFile = "..file location.."

This typically references an .odcfile (Office Data Connection) saved on your system that contains the connection details. You can create this file from the Window's Control Panel.

这通常引用保存在系统上的.odc文件（Office 数据连接），其中包含连接详细信息。您可以从窗口的控制面板创建此文件。

You haven't specified, but an .odc file may be what your current connection is using.

您尚未指定，但 .odc 文件可能是您当前连接使用的文件。

Again, I am unable to test these suggestions, so you should investigate further and take some precautions - so that you won't risk losing the current connection details.

同样，我无法测试这些建议，因此您应该进一步调查并采取一些预防措施 - 这样您就不会冒丢失当前连接详细信息的风险。