vba 无法在 Excel 中使用名称中的空格查询工作表上的命名范围

Unable to query named range on sheet with spaces in name in Excel

提问by user2229491

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"

命名范围为“成分”，一张纸名为“NoSpaces”，另一张名为“With Spaces”

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

这是适用于“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

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.

每次，我都会收到“Microsoft Access 数据库引擎找不到对象...”错误。

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

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.

当通过@shahkalpesh 提供的模式代码运行时，它会将 TABLE_NAME 列为两个命名范围的“成分”（即使每个范围都限定在不同的工作表中）。
使用此驱动程序，即使 [NoSpaces$Ingredients] 也不起作用。

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.

当通过@shahkalpesh 提供的模式代码运行时，它将 TABLE_NAME 列为“NoSpaces$Ingredients”和“'With Spaces'$Ingredients”。使用此驱动程序， [NoSpaces$Ingredients] 工作正常（使用 ACE 驱动程序时不行）。
但是，使用架构报告的确切名称，['With Spaces'$Ingredients] 不起作用。

Excel 2013

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.

当通过@shahkalpesh 提供的模式代码运行时，它会将 TABLE_NAME 列为“NoSpaces$Ingredients”和“'With Spaces$'Ingredients”。使用此驱动程序， [NoSpaces$Ingredients] 工作正常，但 ['With Spaces'$Ingredients] 不起作用。

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

最后，请参阅http://db.tt/3lEYm2g1以获取在 Excel 2007 中创建的示例工作表，该工作表在（至少）2 台不同的机器上存在此问题。

回答by user2336932

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

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

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.

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

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

回答by shahkalpesh

The name of the sheet with spaces followed by named range can be written as ['My Sheet$'MyData]

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

Here is how you get to list the tables contained in the workbook

以下是列出工作簿中包含的表格的方法

1) Code to get the list of tables in the workbook

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

EDIT: For your scenario, it will be

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

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

EDIT2: I am sorry, I pasted the wrong code the first time. Please use the above code in the listing 1 and look for TABLE_NAMEin the immediate window. The listing of named ranges prefixed with sheet name will be shown against TABLE_NAME(on which you can query).

EDIT2：对不起，我第一次粘贴了错误的代码。请使用清单 1 中的上述代码并TABLE_NAME在立即窗口中查找。将显示以工作表名称为前缀的命名范围列表TABLE_NAME（您可以在其上查询）。

Also, make sure that the named range is scoped to the worksheet. Make sure that the casing of the sheet name and range name matches with query.

此外，请确保命名范围的范围是工作表。确保工作表名称和范围名称的大小写与查询匹配。

回答by Santosh

Below query would work. Just make sure the named range Ingredientsexist in sheet With Space. Also save the workbook.

下面的查询会起作用。只需确保命名的范围成分存在于工作表With Space 中。同时保存工作簿。

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

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

Alternatively you can use below

或者，您可以在下面使用

strQuery = "Select * from [With" & Chr(32) & "Spaces$Ingredients]"

strQuery = "Select * from [With" & Chr(32) & "Spaces$Ingredients]"

回答by Jez Clark

Another late entrance to the party...

聚会又迟到了……

I couldn't get any of the responses here to work for the entire sheet, so I made a named range for the whole sheet (select all cells and give them a name - I called them POList) and referred to that thus:

我无法在此处获得适用于整个工作表的任何响应，因此我为整个工作表创建了一个命名范围（选择所有单元格并为其命名 - 我将它们称为 POList）并因此引用：

UPDATE [POList] SET..... etc

So no single quotes, no backticks, no $ sign, not even the sheet name.

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

Having said that, the workbook in question only has the one sheet (which DOES have spaces in the name).

话虽如此，有问题的工作簿只有一张纸（名称中确实有空格）。

This works using Excel 2002 (!) and the following connection code

这适用于 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

Obviously this won't work for everyone's situation and is a bit of a kludgey workaround, but maybe someone will find it useful...

显然这不适用于每个人的情况，并且有点笨拙的解决方法，但也许有人会发现它很有用......

回答by H--

I was having this same issue and was able to solve without a named range. In addition, as a two-fold part of what my issue was, make sure there are no trailing spaces in the worksheet name. Try...

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

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