Sub RunQuery() Syntax Error using VBA

[kkoruni]

Good morning all,
I am running into a syntax error ( [datasource.error] microsoft sql: incorrect syntax near '>'. ) I tried multiple ways to fix this error before posting but I am at dead end.


Sub RunQuery()
Dim QueryText As String
QueryText = " "
QueryText = QueryText + "USE DataBaseName;"
QueryText = QueryText + "GO"
QueryText = QueryText + " "
QueryText = QueryText + "EXEC dbo.StoredProcedure"
QueryText = QueryText + "@BeginDate >= '" & ActiveSheet.Range("B3") & "' "
QueryText = QueryText + "@EndDate <= '" & ActiveSheet.Range("B4") & "' "
QueryText = QueryText + "GO"
QueryText = QueryText + " "
QueryText = QueryText + "select * from ##TempTableFromStoredProcedure"
ActiveWorkbook.Queries("Query1").Formula = _
"let" & Chr(13) & "" & Chr(10) & " Source = Sql.Database(""ServerName"", "" DataBaseName "", [Query=""" & QueryText & """, CreateNavigationProperties=false])" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & " Source"

ActiveWorkbook.RefreshAll

End Sub

 

[RoryA]

It looks like you are missing some spaces in there. Try outputting the QUeryText variable and you should see what I mean.

 

[kkoruni]

It looks like you are missing some spaces in there. Try outputting the QUeryText variable and you should see what I mean.

Hello,
I am so sorry but the way i understand querytext variable is by using brackets {}. But, knowing where to use them I tried google and i read in microsoft's query-variable some way to use them but still failed.

QueryText = QueryText + "{@BeginDate >=} '" & ActiveSheet.Range("B3") & "' "
QueryText = QueryText + "{@EndDate <=} '" & ActiveSheet.Range("B4") & "' "

Any help RoryA is greatly appreciated.

Thank you.

 

[RoryA]

With these lines:

QueryText = QueryText + "EXEC dbo.StoredProcedure"
QueryText = QueryText + "@BeginDate >= '" & ActiveSheet.Range("B3") & "' "

there is no space between the end of the word StoredProcedure and the start of the parameter name. Also note that it is safer to use & for concatenation rather than +

 

[kkoruni]

With these lines:

QueryText = QueryText + "EXEC dbo.StoredProcedure"
QueryText = QueryText + "@BeginDate >= '" & ActiveSheet.Range("B3") & "' "

there is no space between the end of the word StoredProcedure and the start of the parameter name. Also note that it is safer to use & for concatenation rather than +

Hi RoryA,
I am not seeing where I am wrong. I am very sorry I am new to vba and I am failing miserly. I am completely lost.
I still get error even if I remove the GO. Can you please help me fix this syntax issue?

Sub RunQuery()
Dim QueryText As String
QueryText = " "
QueryText = QueryText & "USE DataBaseName;" & " "
QueryText = QueryText & " "
QueryText = QueryText & "EXEC dbo.StoredProcedure"
QueryText = QueryText & " "
QueryText = QueryText & " @BeginDate >= '" & ActiveSheet.Range("B3") & "' "
QueryText = QueryText & " @EndDate <= '" & ActiveSheet.Range("B4") & "' "
QueryText = QueryText & " "
QueryText = QueryText 7 "select * from ##TempTableFromStoredProcedure"

 

[RoryA]

If you hardcode the parameters in your query, does it actually run in power query?