title: VBA - SQL Query DB Table to Excel With Special Characters in Table Name
body: <p>I posted a previous question regarding SQL Query to Excel here:</p>
  
  <p><a href="https://stackoverflow.com/questions/55242472/vba-import-all-rows-from-table-in-sql-to-excel">VBA - Import All Rows from Table in SQL to Excel</a></p>
  
  <p>Which, turned out to be an oversight issue which I have since resolved. Now, however, I face a new issue when trying to import a different table. </p>
  
  <p>When I first got the macro put together, it was for a table named with underscores such as: <strong>my_table_1_query</strong></p>
  
  <p>Now, I attempted to use the exact same code with the table name such as: <strong>my-table_query_1</strong> and I get an error on this line: </p>
  
  <pre><code>.Refresh BackgroundQuery:=False
  </code></pre>
  
  <p>Error Message:</p>
  
  <blockquote>
    <p>Incorrect syntax near '-'</p>
  </blockquote>
  
  <p>This is in the function <strong><em>ImportSQLtoQueryTable</em></strong></p>
  
  <p>Here is the code:</p>
  
  <p><em>Functions</em></p>
  
  <p><strong>ImportSQLtoQueryTable</strong></p>
  
  
  
  <pre class="lang-vb prettyprint-override"><code>Function ImportSQLtoQueryTable(ByVal conString As String, ByVal query As String, ByVal target As Range) As Integer
  
      Dim ws As Worksheet
      Set ws = target.Worksheet
  
      Dim address As String
      address = target.Cells(1, 1).address
  
      'Procedure recreates ListObject or QueryTable
      'For Excel 2007 or higher
      If Not target.ListObject Is Nothing Then
  
          target.ListObject.Delete
  
      End If
  
      'For 2007 or higher
      If Application.Version &gt;= "12.0" Then
  
          With ws.ListObjects.Add(SourceType:=0, Source:=Array("OLEDB;" &amp; conString), Destination:=Range(address))
  
              With .QueryTable
  
                  .CommandType = xlCmdSql
                  .CommandText = StringToArray(query)
                  .BackgroundQuery = True
                  .SavePassword = True
                  .Refresh BackgroundQuery:=False
                  .ListObject.Name = "DB KW Component Table"
  
              End With
  
          End With
  
      End If
  
      ImportSQLtoQueryTable = 0
  
  End Function
  </code></pre>
  
  <p><strong>StringToArray</strong></p>
  
  <pre class="lang-vb prettyprint-override"><code>Function StringToArray(Str As String) As Variant
  
      Const StrLen = 127
      Dim NumElems As Integer
      Dim Temp() As String
      Dim I As Integer
  
      NumElems = (Len(Str) / StrLen) + 1
      ReDim Temp(1 To NumElems) As String
  
      For I = 1 To NumElems
  
         Temp(I) = Mid(Str, ((I - 1) * StrLen) + 1, StrLen)
  
      Next I
  
      StringToArray = Temp
  
  End Function
  </code></pre>
  
  <p><strong>GetTestConnectionString</strong></p>
  
  <pre class="lang-vb prettyprint-override"><code>Function GetTestConnectionString() As String
  
      GetTestConnectionString = OleDbConnectionString( _
          "Server Location", _
          "Connection DB", _
          "Username", _
          "Password")
  
  End Function
  </code></pre>
  
  <p><strong>OleDbConnectionString</strong></p>
  
  <pre class="lang-vb prettyprint-override"><code>Function OleDbConnectionString(ByVal Server As String, ByVal Database As String, ByVal Username As String, ByVal Password As String) As String
  
      If Username = "" Then
  
          MsgBox "User name for DB login is blank. Unable to Proceed"
  
      Else
          OleDbConnectionString = _
          "Provider=SQLOLEDB.1;" &amp; _
          "Data Source=" &amp; Server &amp; "; " &amp; _
          "Initial Catalog=" &amp; Database &amp; "; " &amp; _
          "User ID=" &amp; Username &amp; "; " &amp; _
          "Password=" &amp; Password &amp; ";"
      End If
  
  End Function
  </code></pre>
  
  <p><em>Sub:</em></p>
  
  <p><strong>TestImportUsingQueryTable</strong></p>
  
  <pre class="lang-vb prettyprint-override"><code>Sub TestImportUsingQueryTable()
  
      Dim conString As String, query As String
      Dim DestSh As Worksheet
      Dim tmpltWkbk As Workbook
      Dim target As Range
  
      'Set workbook to be used
      Set tmpltWkbk = Workbooks("New DB.xlsm")
  
      'Need to add check if sheet already exists
      'If sheet already exists then just refresh table
  
      'Add a new sheet called "DB Table"
      Set DestSh = tmpltWkbk.Worksheets.Add
      DestSh.Name = "DB Table"
  
      With DestSh
  
          .UsedRange.Clear
          Set target = .Cells(2, 2)
  
      End With
  
      'Get connection string
      conString = GetTestConnectionString()
  
      'Set Query to table
      query = "SELECT * FROM SAT_Keyword_DB_X7.dbo.kw_link-tbl_keyword_components"
  
      Select Case ImportSQLtoQueryTable(conString, query, target)
  
          Case Else
  
      End Select
  
  End Sub
  </code></pre>