代码之家 › 专栏 › 技术社区 › user2229491

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

vba named-ranges excel sql

user2229491 · 技术社区 · 11 年前

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

我希望基于任何图纸上的命名区域进行查询。有些图纸的名称没有空格,而另一些图纸的名称有空格。

对于那些没有空格的人,我可以很容易地做到这一点,但我无法理解使用空格的语法(以及一个小时的谷歌搜索)。

命名范围为“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

我已经为“带空格”工作表尝试了以下所有操作:

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

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

正如我提到的,它适用于所有名称中没有空格的图纸。

如果您能帮助我们在有空间的床单上工作,我们将不胜感激。

谢谢

根据以下评论进行更新:

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”和“WithSpaces'$Ingredient”。有了这个驱动程序,[NoSpaces$Ingredients]可以正常工作(ACE驱动程序没有)。
然而,使用schema报告的确切名称,['With Spaces'$Ingredients]不起作用。

Excel 2013版

sConnString=“提供程序=Microsoft.ACE.OLEDB.12.0;数据源=”&strFileLoc&“;扩展属性=”“Excel 12.0宏;HDR=否;IMEX=1;”“”

当运行@shahkalpesh提供的模式代码时,它将TABLE_NAME列为“NoSpaces$Ingredients”和“WithSpaces$'Ingredientes”。有了这个驱动程序,[NoSpaces$Ingredients]可以正常工作,但['With Spaces'$Ingreditions]不起作用。

最后,请参阅 http://db.tt/3lEYm2g1 例如在Excel 2007中创建的工作表,该工作表在(至少)2台不同的机器上存在此问题。

6 回复 | 直到 6 年前

user2336932 11 年前

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

SELECT * FROM [Report 1$A4:P]

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

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

Santosh mattboy 11 年前

下面的查询会起作用。只需确保命名范围成分存在于图纸中 带空格 。同时保存工作簿。

strQuery=“从[With Spaces$Ingredients]中选择*”

或者,您可以使用以下内容

strQuery=“从[With”&Chr(32)&“Spaces$Ingredients]中选择*”

shahkalpesh 11 年前

带有空格和命名范围的工作表的名称可以写为['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 在直接窗口中。以图纸名称为前缀的命名范围列表将显示在 表格名称 (您可以在上面查询)。

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

Jez Clark 10 年前

聚会又迟到了。。。

我在这里无法获得任何响应来处理整个工作表,所以我为整个工作表创建了一个命名范围(选择所有单元格并给它们一个名称-我称它们为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

显然,这不适用于每个人的情况,而且有点笨拙,但也许有人会发现它很有用。。。

H-- 9 年前

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

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

NoXSaeeD 3 年前

如果有人在查询sql中使用这个,这对我来说很有效

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

工作表名称为“name name”,只需将名称置于单引号之间