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

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 2

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