通过sql提取excel数据；日期列不起作用 [英] extract excel data via sql; date column not working

问题描述

我正在使用下面的函数从另一个工作簿中提取数据.它适用于非日期列，但不适用于保存日期的列

I am using the function below to extract data from another workbook. It works for non-Date columns, but does not work for a column which holds dates

工作表看起来像

适用于不是日期列:

Debug.Print GetSheetSQL(workbookPath, "Select columna from [sheet$]")(1)("columna")

--> 返回palim"

--> Returns "palim"

如果 a 是日期列

Debug.Print GetSheetSQL(workbookPath, "Select columnb from [sheet$]")(1)("columnb")

--> 不返回任何内容

--> Returns Nothing

Function GetSheetSQL(path As String, sqlStr As String) As Collection

'''''''''''''''''''''''''''''''''''''''
'Open ADOB Connection and query via sql
'   Connection string is standard and
'   taken from:
'        https://www.connectionstrings.com/microsoft-jet-ole-db-4-0/standard-excel/

Dim objConnection As Object
Dim objRecordSet As Object
Set objConnection = CreateObject("ADODB.Connection")
Set objRecordSet = CreateObject("ADODB.Recordset")

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

objConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & path & ";Extended Properties=""Excel 8.0;HDR=Yes"""

objRecordSet.Open sqlStr, objConnection, adOpenStatic, adLockOptimistic, adCmdText
'''''''''''''''''''''''''''''''''''''''
'Iterate through queried table
'   table is a collection with a row per row in the table
'   row is a dictionary with table headings as key, returning the corresponding value
Dim table As Collection
Set table = New Collection
Dim row As Object
Dim fld
'iterate through recordset rows
Do Until objRecordSet.EOF
    With objRecordSet
        Set row = CreateObject("Scripting.Dictionary")

        For Each fld In .Fields
            row.Add fld.Name, fld.Value
        Next fld

        table.Add row

        .MoveNext

    End With
Loop

Set GetSheetSQL = table

'Close Connection; reset Error Handling to default
objConnection.Close
On Error GoTo 0
Exit Function
ErrorCloseConn:
    objConnection.Close
    On Error GoTo 0
    Resume
End Function

推荐答案

我能够重现这个问题，也许这有帮助

I was able to re-produce the issue, maybe this is of help

这看起来不错，但我将第一个带有日期的单元格更改为文本.调试代码会给你

This looks fine but I changed the first cell with a date to text. Debugging the code will give you

因此，ADODB 需要一个日期，Type 是 adDate 并将文本转换为空字符串.

So, ADODB expects a date, Type is adDate and converts text to an empty string.

以下更新最终解决了问题

UPDATE 将连接字符串更改为

objConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & path & ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"""

有关详细信息，请查看此处

这篇关于通过sql提取excel数据；日期列不起作用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！