title: Unable to query named range on sheet with spaces in name in Excel
body: <p>I have a workbook with multiple sheets, and each sheet has the same set of named ranges (IE they are scoped to the sheet, not workbook).</p>
  
  <p>I want to query based on a named range on any of the sheets.  Some sheets have names with no spaces, and others do have names with spaces.</p>
  
  <p>I can easily do this for the ones with no space, but the syntax for doing this with spaces escapes me (and an hour of google-ing).</p>
  
  <p>The named range is "Ingredients" and one sheet is named "NoSpaces", the other "With Spaces"</p>
  
  <p>Here's the code that works fine for "NoSpaces" sheet:</p>
  
  <pre><code>sConnString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" &amp; dictNewRecipesToCheck(arrKeys(0)) &amp; ";Extended Properties=""Excel 12.0;HDR=No;IMEX=1;"""
  strQuery = "Select * from [NoSpaces$Ingredients]"
  Set objConn = New ADODB.Connection
  Set objRecordSet = New ADODB.Recordset
  objConn.Open sConnString
  objRecordSet.Open strQuery, objConn
  </code></pre>
  
  <p>I've tried all the following for the "With Spaces" sheet:</p>
  
  <pre><code>strQuery = "Select * from [With Spaces$Ingredients]"
  strQuery = "Select * from ['With Spaces'$Ingredients]"
  strQuery = "Select * from ['With Spaces$'Ingredients]"
  strQuery = "Select * from [With_Spaces$Ingredients]"
  </code></pre>
  
  <p>Every time, I'm getting "The Microsoft Access database engine could not find the object ..." error.</p>
  
  <p>As as I mentioned, it works fine for all sheets that don't have spaces in the name.</p>
  
  <p>Any help to get this working on sheets with spaces, would be MUCH appreciated.</p>
  
  <p>Thanks!</p>
  
  <p>UPDATES BASED ON COMMENTS BELOW:</p>
  
  <p>Excel 2007  </p>
  
  <pre><code>sConnString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" &amp; strFileLoc &amp; ";Extended Properties=""Excel 12.0 Macro;HDR=No;IMEX=1;"""
  </code></pre>
  
  <p>When run through the schema code provided by @shahkalpesh it lists TABLE_NAME as just "Ingredients" for both named ranges (even though each are scoped to a different sheet).<br>
  With this driver, even [NoSpaces$Ingredients] doesn't work.</p>
  
  <pre><code>sConnString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &amp; strFileLoc &amp; ";Extended Properties=""Excel 8.0;HDR=No;IMEX=1;"""
  </code></pre>
  
  <p>When run through the schema code provided by @shahkalpesh it lists TABLE_NAME as "NoSpaces$Ingredients" and "'With Spaces'$Ingredients".  With this driver, [NoSpaces$Ingredients] works fine (it didn't with ACE driver).<br>
  However, using the exact name as reported by schema, ['With Spaces'$Ingredients] doesn't work.</p>
  
  <p>Excel 2013</p>
  
  <pre><code>sConnString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" &amp; strFileLoc &amp; ";Extended Properties=""Excel 12.0 Macro;HDR=No;IMEX=1;"""
  </code></pre>
  
  <p>When run through the schema code provided by @shahkalpesh it lists TABLE_NAME as "NoSpaces$Ingredients" and "'With Spaces$'Ingredients".  With this driver, [NoSpaces$Ingredients] works fine, but ['With Spaces'$Ingredients] doesn't work.</p>
  
  <p>Finally, please refer to <a href="http://db.tt/3lEYm2g1" rel="nofollow">http://db.tt/3lEYm2g1</a> for an example sheet created in Excel 2007 that has this issue on (at least) 2 different machines.</p>