Return to 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 replace 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.

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.

I have a excel document that has a macro which when run will modify a CommandText of that connection to pass in params from the excel spreadsheet. like so:

code:

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

We would like the refresh to not only modify the connection command but also modify the connection as we would like to run for a different database also:

screenshot:

Just like the macro replace the command params with values from the spreadsheet we 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 getting 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.

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

I have a excel document that has a macro which when run will modify a CommandText of that connection to pass in params from the excel spreadsheet. like so:

code:

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

We would like the refresh to not only modify the connection command but also modify the connection as we would like to run for a different database also:

screenshot:

Just like the macro replace the command params with values from the spreadsheet we 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 getting it working from there.

I tried to do something like this:

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

but that does not work. Thanks.

I have a excel document that has a macro which when run will modify a CommandText of that connection to pass in params from the excel spreadsheet. like so:

code:

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

We would like the refresh to not only modify the connection command but also modify the connection as we would like to run for a different database also:

screenshot:

Just like the macro replace the command params with values from the spreadsheet we 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 getting 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.