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
ADODB recordset in VBA says excel field is empty when it's not
提问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/。
