Modify an embedded Connection String in microsoft excel macro

Question

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

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.

Answer 1

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:

• 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

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

Answer 2

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"

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.

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

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!

Answer 3

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.

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

Answer 4

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

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

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

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

Answer 5

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).

So to modify your example:

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

Answer 6

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, Delete this connection and reinstate the old connection/name. (I am unable to test this myself currently, so tread carefully.)

Alternatively, you might change the current connections SourceConnectionFile:

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

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

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

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.