VBA中的ADODB记录集说excel字段为空 [英] ADODB recordset in VBA says excel field is empty when it's not
问题描述
我有一个需要导入到Access数据库的Excel工作表. 该工作表如下所示:
I have an excel sheet I need to import in my Access database. The sheet looks like this:
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
然后我使用adodb将此文件传输到数据库中:
I then transfer this file into the database using 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
现在是我的问题:某些字段中包含文本.
字段值应为item0001
,但据报道为NULL
Now my problem: certain fields have text in them.
The field value should then be item0001
, but it's reportedly 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.
推荐答案
在您的扩展属性部分中更具体(并且不要在其中省略内引号).
Be more specific in your Extended Properties portion (and don't omit inner quotes there).
尤其要尝试使用Extended Properties="Excel 8.0;HDR=Yes;IMEX=1"
允许带混合数字和文本的数据.
In particular, try Extended Properties="Excel 8.0;HDR=Yes;IMEX=1"
to allow mixed numbers&text data.
更多详细信息,请参见 http://connectionstrings.com/.
More details at http://connectionstrings.com/.
这篇关于VBA中的ADODB记录集说excel字段为空的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!