1

I have been struggling with a need I had for several month and today I found the solution, or workaround if you'd like, to it.

The solution was inspired on a post I found here:

how to pass parameters to query in SQL (Excel)

And, even though I wanted to thank @mono código for the idea, I couldn't comment on the post anything on the original thread due to my lack of reputation. So I thought of posting this to thank and also to help others with the struggle.

My first approach for this was using Power Query, but doing modifications to the script afterwards is very complicated. This approach is much more simpler IMO.

It is basically getting the command text of your connection and modifying it on the fly:

With ActiveWorkbook.Connections("MyConnection").OLEDBConnection
    queryOriginalText = .CommandText
    queryPreText = .CommandText

    queryPostText = Replace(queryPreText, "SET @From=@From", "SET @From='" & Range("StartDate") & "'")
    queryPreText = queryPostText
    queryPostText = Replace(queryPreText, "SET @To=@To", "SET @To='" & Range("EndDate") & "'")
    queryPreText = queryPostText
    queryPostText = Replace(queryPreText, "SET @OrderNo=@OrderNo", "SET @OrderNo='" & Range("OrderNo") & "'")

    .CommandText = queryPostText
    ActiveWorkbook.Connections("MyConnection").Refresh
    .CommandText = queryOriginalText
End With

My script has 3 variables that I use as conditions to filter my data: @From, @To and @OrderNo. And, when I set those on my script I do it like this:

SET @From=@From
SET @To=@To
SET @OrderNo=@OrderNo

In my VBA I look for those specific strings and replace them one by one with values that the user input on the Sheet, on specific cell with Range names.

At the end I put back the original text so the strings to replace are always there when the user hit the button that runs the macro. This only works if you unchecked the option

Connection Properties

otherwise you will get a runtime error.

I hope this helps

mejiaks
  • 15
  • 5
  • 5
    If your goal is to provide both a problem and a solution, then post only the problem in the question part, then answer your own question below in teh answer area. – Greg Viers Feb 02 '18 at 20:23
  • Why do you have to do this, exactly?! – ASH Feb 03 '18 at 01:16
  • @ryguy72 When retrieving data from MSSQL and want to specify a data range for example. in order to do this , the user has to edit the connection and the script itself. For some users this is "advanced" stuff, so it is better if they just specify the dates on two cells of the spreadsheet and then I to the above to change dinamically on the fly. – mejiaks Feb 05 '18 at 14:45

1 Answers1

0

Are you saying you need to pass variable to the ConnectionString? That doesn't seem right at all. Pass dates to the Query that is passed to the ConnectionSting. Follow this example.

Private Sub CommandButton1_Click()
Dim FromDate As Date
Dim ToDate As Date

FromDate = Format(Sheets("Sheet1").Range("B1").Value, "yyyy-mm-dd") 'Pass value from cell B1 to SellStartDate variable
ToDate = Format(Sheets("Sheet1").Range("B2").Value, "yyyy-mm-dd") 'Pass value from cell B2 to SellEndDate variable

MsgBox FromDate
MsgBox ToDate

'Pass the Parameters values to the stored procedure used in the data connection
With ActiveWorkbook.Connections("TestConnection").OLEDBConnection
.CommandText = "EXEC dbo.spr_TestProcedure '" & FromDate & "','" & ToDate & "'"
ActiveWorkbook.Connections("TestConnection").Refresh

End With
End Sub

Also, it may be helpful to follow the example from the link below.

https://www.mssqltips.com/sqlservertip/3436/passing-dynamic-query-values-from-excel-to-sql-server/

Post back if you are still having an issue with this.

ASH
  • 20,759
  • 19
  • 87
  • 200
  • That works perfectly when you have a stored procedure at the back end, but in my case I am not able to create a stored procedure so it is a native sql script you run against the DB engine. That is why I do what i describe on the post – mejiaks Feb 06 '18 at 18:07
  • If you don't have sprocs on that machine I'm not sure how to help you. Maybe someone else will jump in here with a viable solution. – ASH Feb 06 '18 at 19:27
  • actually, the post is a problem with the solution – mejiaks Feb 07 '18 at 20:19
  • Yeah sorry but this solution is faulty in many regards: [1] you have to have to stored procedure and [2] the link mssqltips uses an example that is open to SQL Injection. I'll post a better solution using pure VBA – Fandango68 Jun 21 '18 at 06:48