title: SQL Named Variables in VBA
body: <p>I have a query that's run in Excel, with a variable parameter that can be changed in the workbook.</p>
  
  <p>This is referenced several times throughout the SQL statement, and so I have declared a variable (@Days) before the Select, and then referenced this throughout the query.</p>
  
  <p>This works fine in management studio.</p>
  
  <p>If I try to execute this in VBA, I get Application-defined or object defined error.</p>
  
  <p>If I uncomment my clipboard code, and paste the result into management studio, it executes no problem.
  If I stop the VBA before .Refresh, and manually refresh the query it refreshes fine.</p>
  
  <p>VBA is below:</p>
  
  <pre><code>Sub Refresh()
  
  
  Dim Days As String
  Dim SQLStat As String
  Dim RChar As Integer
  
  Days = Sheets("Data").Range("A1").Value
  
  ' Gets current Query, and strips off declaration
  SQLStat = ActiveWorkbook.Connections("Query").ODBCConnection.CommandText
  SQLStat = Right(SQLStat, (Len(SQLStat) - (InStr(1, SQLStat, "Select") - 1)))
  
  ' Adds Declaration with new days value.
  SQLStat = "Declare @Days integer set @Days = " &amp; Days &amp; " " &amp; Chr(13) &amp; SQLStat
  
  
  ' Puts SQL in clipboard for test purposes
  'Dim clipboard As MSForms.DataObject
  'Set clipboard = New MSForms.DataObject
  'clipboard.SetText SQLStat
  'clipboard.PutInClipboard
  
  
  With ActiveWorkbook.Connections("Query").ODBCConnection
      .BackgroundQuery = False
      .CommandText = SQLStat
      .Refresh
  End With
  
  MsgBox "Refreshed"
  
  End Sub
  </code></pre>
  
  <p>I have split this out into a test scenario. Test1 Fails. The query doesn't update. Test2 Refreshes as you'd expect.</p>
  
  <pre><code>Sub Test1()
  Dim SqlStr As String
  Dim Days As String
  
  Days = Sheets("test").Range("A1").Value
  SqlStr = "declare @Days int set @Days = " &amp; Days &amp; " select dateadd(day,@days,getdate())"
  
  With ActiveWorkbook.Connections("Query").ODBCConnection
      .CommandText = SqlStr
      .Refresh
  End With
  
  End Sub
  
  
  Sub Test2()
  Dim SqlStr As String
  Dim Days As String
  
  Days = Sheets("test").Range("A1").Value
  SqlStr = "select dateadd(day," &amp; Days &amp; ",getdate())"
  
  With ActiveWorkbook.Connections("Query").ODBCConnection
      .CommandText = SqlStr
      .Refresh
  End With
  
  End Sub
  </code></pre>