使用ADO查询Excel时“定义了太多字段”

I'm querying an Excel 2010 workbook using the following ADO:

我正在使用以下ADO查询Excel 2010工作簿：

Function WorksheetRecordsetSQL(workbookPath As String, sheetName As String, selectSQL As String) As ADODB.Recordset

Dim objconnection As New ADODB.Connection
Dim objrecordset As New ADODB.Recordset

Const adOpenStatic = 3
Const adLockOptimistic = 3
Const adCmdText = &H1

objconnection.CommandTimeout = 99999999

objconnection.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
    "Data Source=" & workbookPath & ";" & _
        "Extended Properties=""Excel 12.0 Xml;HDR=YES;IMEX=1"";"

objrecordset.Open selectSQL, _
    objconnection, adOpenStatic, adLockOptimistic, adCmdText

Set WorksheetRecordsetSQL = objrecordset

End Function

When I call it with this SQL:

当我用这个SQL调用它时：

Select * FROM [someWorksheet$]

the function executes successfully. But if I call it with:

该函数执行成功。但如果我称之为：

Select *,cdate(someField) FROM [someWorksheet$]

then I get this error:

然后我收到这个错误：

Too many fields defined.

I found that the select * query was producing a recordset with 255 fields (the maximum that the Access engine can have in a query), even though there are only 58 columns in the sheet. So that one extra cdate() column overloaded the engine and produced the error.

我发现select *查询产生的记录集包含255个字段（Access引擎在查询中可以拥有的最大值），即使工作表中只有58列。因此，一个额外的cdate（）列重载了引擎并产生了错误。

Is there a way that I can keep a select * query from picking up blank columns from an Excel sheet? Or some parameters that I can set that will allow more than 255 fields?

有没有办法可以保持select *查询从Excel工作表中拾取空白列？或者我可以设置一些允许超过255个字段的参数？

1 个解决方案

#1

You can specify the range which should be read and so reduce the number of columns entering into the query. Here from cell A1 to column BF (58) all rows:

您可以指定应读取的范围，从而减少进入查询的列数。这里从单元格A1到列BF（58）所有行：

FROM [Source_sheet$A1:BF]

FROM [Source_sheet $ A1：BF]

Sub main()
    Dim reultingRecordset As ADODB.Recordset

    Set reultingRecordset = WorksheetRecordsetSQL( _
        "C:\Temp\VBA\ReadWithADOSource.xlsx", _
        "Source_sheet", _
        "Select * FROM [Source_sheet$]")

    Debug.Print "Select * FROM [Source_sheet$] >"
    Debug.Print "Fields: " & reultingRecordset.Fields.Count & " Records: " & reultingRecordset.RecordCount

    Set reultingRecordset = WorksheetRecordsetSQL( _
        "C:\Temp\VBA\ReadWithADOSource.xlsx", _
        "Source_sheet", _
        "Select *,cdate(Col2) FROM [Source_sheet$A1:BF]")

    Debug.Print "Select *,cdate(Col2) FROM [Source_sheet$A1:BC] > "
    Debug.Print "Fields: " & reultingRecordset.Fields.Count & " Records: " & reultingRecordset.RecordCount

End Sub

Output:

输出：

Select * FROM [Source_sheet$] >
Fields: 255 Records: 8

选择* FROM [Source_sheet $]>字段：255记录：8

Select *,cdate(Col2) FROM [Source_sheet$A1:BC] >
Fields: 59 Records: 8

选择*，cdate（Col2）FROM [Source_sheet $ A1：BC]>字段：59条记录：8

#1