在VBA中使用ADODB查询带有空格的工作表名称 [英] Query sheet names with spaces using ADODB in VBA

问题描述

我正在尝试使用adodb查询Excel文件。但是某些文件具有非标准的工作表/列名称。

I'm trying to use adodb to query excel files. But some files have nonstandard sheet/column name.

strQry = "select * from [ sheet1$A1:A50]"

我收到一条错误消息，说姓名的无效括号。如何在名称中有多余空格的工作表上运行查询？这些是来自客户端的原始文件，因此我不想更改名称或其他任何内容。

I got an error saying "Invalid bracketing of name". How do I run query on sheet that has an extra space in its name? These are raw files from clients so I don't want to change the name or anything.

此外，我对该列还有另一个问题。

Also, I have another problem regarding the column.

strQry2 = "select [column\rA] from [sheet1$E1:E122]"

\r是换行符。我收到一个错误没有为一个或多个必需参数提供值。

"\r" is a line break. I got an error "No value given for one or more required parameters."

关于如何处理这些问题的任何建议？

Any advice on how to deal with these issues?

更新：

Sheet 1 工作正常，但我的工作表命名为 Sheet1 （ sheet1之前的多余空格。

Sheet 1 works fine but the sheet I have is named something like Sheet1 (extra space before sheet1.

某些列标题有换行符列名中的/ carriage return，例如 column& vbcrlf& name。是否可以查询这些？

Some of the column headers have a line break/carriage return within column name, like "column" & vbcrlf & "name". Is there a way to query these?

推荐答案

如果 Sheet 1 在Sheet和1 use之间有一个空格，则空间会一直

As far as the space goes if Sheet 1 has a space between Sheet and 1 use

从[Sheet 1 $ A1：A50]中选择*

如果<$前面有空格c $ c> sheet 1 又名 Chr（32）& Sheet 1 ，那么就不可能使用<$ c从该表中选择任何内容$ c> []语法

If there is a space in front of the sheet 1 aka. Chr(32) & Sheet 1 then it's impossible to select anything from that sheet using the [] syntax

但是，如果您不想更改原始名称您可以创建的电子表格为要从中提取数据的范围指定一个临时 命名范围。

however, if you don't want to change the original name of the spreadsheet you can create a temporary named range for the Range you want to pull the data from.

例如：

' add a temporary name
ThisWorkbook.Names.Add name:="tmp", RefersTo:=Sheets(" Sheet 1").Range("A1:C4")

' create your sql including the named range
sql = "SELECT * FROM tmp"

' open recordset
rs.Open sql, cn, adOpenUnspecified, adLockUnspecified

' remove the temporary name
ThisWorkbook.Names.Item("tmp").Delete

' copy rs to spreadsheet
ActiveSheet.Range("F2").CopyFromRecordset rs

我不理解的第二个问题，您可以详细说明，我将更新答案吗？

The second question I don't understand so can you elaborate and I will update the answer?

这篇关于在VBA中使用ADODB查询带有空格的工作表名称的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！