VBA 中的 ADODB 记录集表示 excel 字段不是空的

声明:本页面是StackOverFlow热门问题的中英对照翻译,遵循CC BY-SA 4.0协议,如果您需要使用它,必须同样遵循CC BY-SA许可,注明原文地址和作者信息,同时你必须将它归于原作者(不是我):StackOverFlow 原文地址: http://stackoverflow.com/questions/2065789/
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

提示:将鼠标放在中文语句上可以显示对应的英文。显示中英文
时间:2020-09-11 11:12:49  来源:igfitidea点击:

ADODB recordset in VBA says excel field is empty when it's not

excelms-accessvbaadodbrecordset

提问by skerit

I have an excel sheet I need to import in my Access database. The sheet looks like this:

我有一个 Excel 工作表需要导入到我的 Access 数据库中。该表如下所示:

DATE RECEPTION  DENOMINATION        ITEM N°     QUANTITE RECUE
06/01/2010  DVD-Sex & the City  PCR-PA21550167  5
06/01/2010  DVD-Avatar Natie 2  PCR-PA21550209  10

I then transfer this file into the database using adodb:

然后我使用 adodb 将此文件传输到数据库中:


Dim rs2 As New ADODB.Recordset
Dim cnn2 As New ADODB.Connection
Dim cmd2 As New ADODB.Command
Dim intField As Integer
Dim strFile As String

strFile = fncOpenFile
If strFile = "" Then Exit Sub

With cnn2
    .Provider = "Microsoft.Jet.OLEDB.4.0"
    .ConnectionString = "Data Source=" & strFile& "; " & "Extended Properties=Excel 8.0"
    .Open
End With

Set cmd2.ActiveConnection = cnn2
cmd2.CommandType = adCmdText
cmd2.CommandText = "SELECT * FROM [PCR$]"
rs2.CursorLocation = adUseClient
rs2.CursorType = adOpenDynamic
rs2.LockType = adLockOptimistic

rs2.Open cmd2

While Not rs2.EOF
        strNaam = rs2.Fields(3).Value
Loop

Now my problem: certain fields have text in them. The field value should then be item0001, but it's reportedly NULL

现在我的问题是:某些字段中有文本。字段值应该是item0001,但据报道它是 NULL

When the field has a regular number it works fine.

当该字段具有常规编号时,它可以正常工作。

The strange thing is: there are other text fields in the sheet and they work FINE.

奇怪的是:工作表中还有其他文本字段,它们工作正常。

回答by GSerg

Be more specific in your Extended Properties portion (and don't omit inner quotes there).

在您的扩展属性部分更具体(并且不要在那里省略内部引号)。

In particular, try Extended Properties="Excel 8.0;HDR=Yes;IMEX=1"to allow mixed numbers&text data.

特别是,尽量Extended Properties="Excel 8.0;HDR=Yes;IMEX=1"允许混合数字和文本数据。

More details at http://connectionstrings.com/.

更多详细信息,请访问http://connectionstrings.com/