vba 如果工作表的第一行没有列名，如何使用 ADODB 连接从 Excel 中检索数据？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12979605/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to retrieve data from Excel with ADODB connection if the first line of the worksheet does not have the column name?
提问by kb_sou
I use the following type of code to retrieve data from some Excel Workbooks (path is a Parameter)
我使用以下类型的代码从一些 Excel 工作簿中检索数据（路径是一个参数）
Dim strSQL As String, conStr as String
Dim cnn As New ADODB.Connection
Dim rs As New ADODB.Recordset
conStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" & path & "';" & _
"Extended Properties=""Excel 12.0;HDR=YES;IMEX=1;"";"
strSQL = "SELECT [Field1], [Field2] FROM [Worksheet$] WHERE [Thing1] > 1"
cnn.open conStr
rs.Open query, cnn, adOpenStatic, adLockOptimistic, adCmdText
That code works fine if the names of the fields are on the first row of the worksheet. The problem is that I need to retrieve data from a worksheet that the data table begins on another row (Row 10).
如果字段名称位于工作表的第一行，则该代码可以正常工作。问题是我需要从数据表在另一行（第 10 行）开始的工作表中检索数据。
Is there a way to specify the first row of my data table?
有没有办法指定我的数据表的第一行？
采纳答案by Doug Glancy
See this Microsoft page. You can use something like:
请参阅此Microsoft 页面。您可以使用以下内容：
strSQL = "SELECT [Field1], [Field2] FROM [Worksheet$$A10:B43] WHERE [Thing1] > 1"
回答by Anonimista
Use a named or unnamed range in your query:
在查询中使用命名或未命名范围：
strQuery = "SELECT * FROM MyRange"
strQuery = "SELECT * FROM [Sheet1$A1:B10]"
See these Microsoft support articles for more information:
有关详细信息，请参阅这些 Microsoft 支持文章：
How To Use ADO with Excel Data from Visual Basic or VBA
如何使用 ADO 处理来自 Visual Basic 或 VBA 的 Excel 数据
ExcelADO demonstrates how to use ADO to read and write data in Excel workbooks
回答by scott
You can query a range of cells starting from row 10:
您可以从第 10 行开始查询一系列单元格：
"SELECT * FROM [Worksheet$A10:S100] WHERE [Thing1] > 1"
What can be tough is finding what the end of the range should be. You could put in a ridiculously large number, but then you'd have to add special handling for the rows of NULL at the end.
很难找到范围的末端应该是什么。您可以输入一个大得离谱的数字，但是您必须在最后为 NULL 行添加特殊处理。
