无法在Excel中以名称中的空格查询工作表上的命名范围 [英] Unable to query named range on sheet with spaces in name in Excel

问题描述

我有一张包含多张工作表的工作簿，每个工作表都有相同的命名范围集（IE的范围限定为工作表，而不是工作簿）。

I希望根据任何表格上的命名范围进行查询。一些表格没有空格的名字，其他的名字有空格。

我可以轻松地为没有空格的那些，但是这样做的语法空格逃避了我（和一小时的谷歌）。

命名范围是成分，一张表命名为NoSpaces，另一个With Spaces

以下是NoSpaces表格正常工作的代码：

  sConnString =Provider = Microsoft.ACE.OLEDB.12.0; Data Source =& dictNewRecipesToCheck（arrKeys（0））& ;扩展属性=Excel 12.0; HDR =否; IMEX = 1;
 strQuery =Select * from [NoSpaces $ Ingredients]
设置objConn =新建ADODB.Connection 
 Set objRecordSet = New ADODB.Recordset 
 objConn.Open sConnString 
 objRecordSet.Open strQuery，objConn 
  

我已经尝试过以下所有的With Spaces表：

  strQuery =Select * 
 strQuery =select * from ['With Spaces'$ Ingredients]
 strQuery =从*中选择*'$'成分]
 strQuery =Select * from [With_Spaces $ Ingredients]
  

每次，我得到 Microsoft Access数据库引擎找不到对象...错误。

正如我所提到的，对于名称中没有空格的所有工作表

任何帮助，使这个工作在有空格的表格，将是非常赞赏。

谢谢！ / p>

基于以下评论的更新：

Excel 2007

  sConnString =Provider = Microsoft.ACE.OLEDB.12.0; Data Source =& strFileLoc& ;扩展属性=Excel 12.0宏; HDR =否; IMEX = 1;
  

< >当运行@shahkalpesh提供的模式代码时，它将TABLE_NAME作为两个命名范围的Ingredients（即使每个范围限定为不同的表）。

使用此驱动程序，甚至[NoSpaces $成分]不起作用。

  sConnString =Provider = Microsoft.Jet.OLEDB.4.0; Data Source =& strFileLoc& ;扩展属性=Excel 8.0; HDR =否; IMEX = 1;
  

<当运行@shahkalpesh提供的模式代码时，它将TABLE_NAME列为NoSpaces $ Ingredients和'With Spaces'$ Ingredients。使用这个驱动程序，[NoSpaces $ Ingredients]工作正常（没有使用ACE驱动程序）。

但是，使用模式报告的确切名称，['使用空格'$ Ingredients]不会工作。

Excel 2013

  sConnString =Provider = Microsoft。 ACE.OLEDB.12.0; Data Source =& strFileLoc& ;扩展属性=Excel 12.0宏; HDR =否; IMEX = 1;
  

< >当运行@shahkalpesh提供的模式代码时，它将TABLE_NAME列为NoSpaces $ Ingredients和使用空格$成分。使用这个驱动程序，[NoSpaces $ Ingredients]可以正常工作，但是['Spaces'$ Ingredients]不起作用。

最后，请参考 http://db.tt/3lEYm2g1 在Excel 2007中创建的示例表，在（至少）2个不同的机器上有此问题

解决方案

是否可以使用excel范围而不是命名范围？我有以下工作：

  SELECT * FROM [Report 1 $ A4：P] 
  

我从GetOleDbSchemaTable（）方法获取工作表名称，并删除撇号。撇号的表单不适用于我的范围。

  if（tableName.Contains（''））
 tableName = Regex.Match（tableName，@（？< ='）（。*？）（？= \ $'），RegexOptions.None）.Value +$; 
  

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).

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.

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).

The named range is "Ingredients" and one sheet is named "NoSpaces", the other "With Spaces"

Here's the code that works fine for "NoSpaces" sheet:

sConnString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & dictNewRecipesToCheck(arrKeys(0)) & ";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

I've tried all the following for the "With Spaces" sheet:

strQuery = "Select * from [With Spaces$Ingredients]"
strQuery = "Select * from ['With Spaces'$Ingredients]"
strQuery = "Select * from ['With Spaces$'Ingredients]"
strQuery = "Select * from [With_Spaces$Ingredients]"

Every time, I'm getting "The Microsoft Access database engine could not find the object ..." error.

As as I mentioned, it works fine for all sheets that don't have spaces in the name.

Any help to get this working on sheets with spaces, would be MUCH appreciated.

Thanks!

UPDATES BASED ON COMMENTS BELOW:

Excel 2007

sConnString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & strFileLoc & ";Extended Properties=""Excel 12.0 Macro;HDR=No;IMEX=1;"""

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).
With this driver, even [NoSpaces$Ingredients] doesn't work.

sConnString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strFileLoc & ";Extended Properties=""Excel 8.0;HDR=No;IMEX=1;"""

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).
However, using the exact name as reported by schema, ['With Spaces'$Ingredients] doesn't work.

Excel 2013

sConnString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & strFileLoc & ";Extended Properties=""Excel 12.0 Macro;HDR=No;IMEX=1;"""

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.

Finally, please refer to http://db.tt/3lEYm2g1 for an example sheet created in Excel 2007 that has this issue on (at least) 2 different machines.

解决方案

Would it be possible to use an excel range instead of named range? I got the following to work:

SELECT * FROM [Report 1$A4:P]

I'm getting the sheet name from the GetOleDbSchemaTable() method and removing the apostrophes. The sheetname with apostrophes does not work for me with a range.

if (tableName.Contains(' '))
            tableName = Regex.Match(tableName, @"(?<=')(.*?)(?=\$')", RegexOptions.None).Value + "$";

这篇关于无法在Excel中以名称中的空格查询工作表上的命名范围的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！