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/。