无法在Excel中查询名称中有空格的工作表上的命名区域

Question

我有一个有多个工作表的工作簿，每个工作表都有相同的命名范围集(即它们的范围是工作表，而不是工作簿)。

我想基于任何工作表上的命名范围进行查询。有些工作表的名称没有空格，而有些工作表的名称有空格。

我可以很容易地为那些没有空格的做这个，但是用空格做这个的语法让我迷惑(和一个小时的谷歌)。

命名范围为"Ingredients"，其中一张命名为"NoSpaces"，另一张命名为"With Spaces"

下面是"NoSpaces"工作表的代码:

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

我已经尝试了以下所有的"With空格"工作表:

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

每次，我得到"The Microsoft Access database engine could not find The object…"的错误。

正如我所提到的，它适用于所有没有空格的工作表。

如果能在有空格的表格上得到帮助，我将不胜感激。

谢谢!

根据下面的注释更新:

Excel 2007

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

当运行@shahkalpesh提供的模式代码时，它将TABLE_NAME列为两个命名范围的"成分"(即使每个范围都适用于不同的表)。
使用这个驱动程序，即使[NoSpaces$Ingredients]也不能工作。

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

当运行@shahkalpesh提供的模式代码时，它将TABLE_NAME列为"NoSpaces$Ingredients"one_answers"'With Spaces'$Ingredients"。使用这个驱动程序，[NoSpaces$Ingredients]可以正常工作(在ACE驱动程序中不能)。
但是，使用模式报告的确切名称，['With Spaces'$Ingredients]不起作用。

Excel 2013

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

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

最后，请参考http://db.tt/3lEYm2g1在Excel 2007中创建的示例工作表，该示例工作表在(至少)两台不同的机器上有此问题。

Answer 1

是否可以使用excel范围而不是命名范围?我得到了以下工作:

SELECT * FROM [Report 1$A4:P]

我从GetOleDbSchemaTable()方法中获取表名并删除撇号。带撇号的工作表名不适用于我的范围。

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

Answer 2

下面的查询将工作。只需确保命名范围Ingredients存在于sheet With Space中。同时保存工作簿。

strQuery = "Select * from [With空格$Ingredients]"

也可以使用

strQuery = "Select * from [With] &杆(32),"空间成分]美元"

Answer 3

工作表的名称，空格后跟命名范围，可以写成['My sheet $'MyData]

下面是如何列出工作簿

中包含的表

1)获取工作簿中表列表的代码

dim i as Integer
Set objRecordSet = objConn.OpenSchema(adSchemaTables)
Do While Not objRecordSet.EOF
    i = 1
    For i = 0 To objRecordSet.Fields.Count - 1
        Debug.Print objRecordSet.Fields(i).Name, objRecordSet.Fields(i).Value
    Next
    objRecordSet.MoveNext
Loop

编辑:对于您的场景，它将是

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

编辑2:对不起，我第一次粘贴错了代码。请使用清单1中的上述代码，并在即时窗口中查找TABLE_NAME。以工作表名称为前缀的命名范围列表将针对TABLE_NAME显示(您可以在其上查询)。

另外，确保命名范围的作用域为工作表。确保工作表名称和范围名称的大小写与query匹配。

Answer 4

又一次迟到了…

我不能得到任何响应在这里工作的整个工作表，所以我做了一个命名范围为整个工作表(选择所有单元格，并给他们一个名字-我叫他们POList)，并引用它:

UPDATE [POList] SET..... etc

所以没有单引号，没有反引号，没有$符号，甚至没有工作表名称。

话虽如此，所讨论的工作簿只有一个工作表(其名称中确实有空格)。

使用Excel 2002(!)和下面的连接代码

Dim cn As ADODB.Connection
Set cn = New ADODB.Connection
With cn
    .Provider = "Microsoft.Jet.OLEDB.4.0"
    .ConnectionString = "Data Source=C:Purchase Req No. List.xls; Extended Properties=Excel 8.0;"
    .Open
End With

显然这并不适用于每个人的情况，是一个笨拙的解决方案，但也许有人会发现它很有用…

Answer 5

我遇到了同样的问题，并且能够在没有命名范围的情况下解决。此外，作为我的问题的双重部分，确保工作表名称中没有尾随空格。试一试……

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

Answer 6

如果有人使用这个在查询sql这个工作为我

select * 
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 
'Excel 12.0 Xml;Database=YourPathYourFileName.xlsx;',['name name$']);

表名是"name "把name放在单引号