ADO 读取Excel文件数据， 丢失数据或数据错误问题。

在用ADO读取Excel文件数据，常容易发生丢失数据的现象。

例如，用ADO打开一个Excel文件的代码如下：
 Dim objConn As ADODB.Connection
    Dim objRS As ADODB.Recordset
    Dim objRSTable As ADODB.Recordset
   
    Set objConn = New ADODB.Connection
    Set objRS = New ADODB.Recordset
    objRS.CursorLocation = adUseClient
   
    With objConn
        .Provider = "Microsoft.Jet.OLEDB.4.0"
        .ConnectionString = "Data Source=" & Me.ExcelPath & ";Extended Properties=""Excel 8.0;HDR=NO;"""
        .CursorLocation = adUseClient
        .Open
    End With
   
    ' Check whether there is a sheet named "master" in the excel file
    Set objRSTable = objConn.OpenSchema(adSchemaTables)
blnMasterSheet = False
    For i = 1 To objRSTable.RecordCount
        If UCase(CStr(objRSTable.Fields("TABLE_NAME").Value)) = "MASTER$" Then
            blnMasterSheet = True
            Exit For
        End If
        objRSTable.MoveNext
    Next i
       
    Set objRSTable = Nothing
    If Not blnMasterSheet Then
        MsgBox ("Master Worksheet Is Not Found")
        Set objConn = Nothing
        Set objRS = Nothing
        GetExcelData = False
        Exit Function
    End If
   
    strExcuteScript = "select * from [Master$A1:F]"
    objRS.Open strExcuteScript, objConn, adOpenStatic, adLockOptimistic
运行后发现第一列的某些行数据丢失了。这发生在第一行是数字，第二行是文字的情况。
因为在读取excel的时候，ODBC会根据第一行的文字来设定返回的recordset的字段类型，当第一行是数字时，就会认为整列都是数字的。结果后面的字符无法读出。

这时，需要把连接字符串改为.ConnectionString = "Data Source=" & Me.ExcelPath & ";Extended Properties=""Excel 8.0;HDR=NO;IMEX=1;"""

其中，IMEX=1;这个属性的意思是，以文本方式读取excel表。 那么就算第一行是数字，后面的字符串也可以正常读出了。

问题好像是解决了。但过一段时间后，又出现了问题，读取某个列的某个行时，会出现错误。错误现象为

我们读取Excel表格后，会把数据传到另一个recordset （假定为R2)再进行处理的。在给另一个recordset付值时出错。根据错误信息，得知如果把一个超长的或者错误的数据付给一个字段时会出现这样的错误。但在R2中，我们设定了对于的字段的长度是1000的，而要付值的数据长度是628，为什么还会出错呢。这个R1的字段出来的值后面也带有很多的乱码。就是说，本来从Excel中读出来的数据已经是乱码了。

      我们把怀疑产生乱码的所有空格去掉，结果错误到了下一行。再把正常的数据复制变成很长，结果也会产生错误。再尝试把前面的正常行去掉，居然错误就不见了。
        把断点设在错误的那行，查数据的长度，是628, 再看rs.fileds(11).defineSize, 发现只有255. 那就说明，它实际字段是认为长255的，但把数据都读出来了，结果是乱码，再把这些乱码付值给另一个recordset时才报错。
        把产生错误的数据放在第一行时，是不会有这个错误的，查看它的defineSize, 发现不再是255了，而是很大的数字。
        再尝试把错误的数据放在1－8行，都不会产生问题，在9行以上就会产生。

        原来，ODBC在读取Excel时，会有个值设置TypeGuessRows=8， 意思是ODBC会先在前8行的数据中去比较，如果所有的数据都在255或以下，那这个字段长度就是varchar(255),否则，这个字段将是text类型。

      于是，我们加上TypeGuessRow=100，再试，结果错误依然存在。

      原来，虽然可以设置这个属性，但ODBC是不会去用它的，要使这个属性有效，必须修改注册表。对于注册表键是

•	Excel 97 HKEY_LOCAL_MACHINE/SOFTWARE/Microsoft/Jet/3.5/Engines/Excel
•	Excel 2000 and later versions HKEY_LOCAL_MACHINE/SOFTWARE/Microsoft/Jet/4.0/Engines/Excel

修改了里面的TypeGuessRow后，这个问题暂时解决了。

但是，如果大的数据在表的后面位置的话，这个问题还是有可能发生的。所以要彻底的解决的话，在打开一个recordset前，就要先定义好它的字段长度了。

 

 

来源：http://www.excelhome.net/