高难问题：用ODBC或者OLEDB打开Excel文档，数据类型都以第一行数据为准，造成第二行的字符被认为是Null。危急！

[DragonCity]

高难问题：用ODBC或者OLEDB打开Excel文档，如果某列的数值可以是字符型也可以是数字型，则数据类型都以第一行数据为准，造成第二行的字符被认为是Null。危急！
我对打开的记录集的字段数据类型进行打印，发现只有两种：202和5
如何强行指定某列的数据类型？


Public Function OpenExcelFile(ByVal strExcelFileName As String, ByVal strSheetName As String) As ADODB.Recordset
mExcelFileName = strExcelFileName
mSheetName = strSheetName
Dim strcnnODBC As String
strcnnODBC = "Provider=MSDASQL.1;Persist Security Info=False;User ID=Admin;Extended Properties=""DSN=Excel Files;DBQ=" & ExcelFileName & ";DriverId=790;MaxBufferSize=2048;PageTimeout=5;"""
'Provider=MSDASQL.1;Persist Security Info=False;User ID=Admin;Extended Properties="DSN=Excel Files;DBQ=D:\WORKFOLDER\QBRADDING\EXCELFILES\Quotation1001.xls;DefaultDir=D:\WORKFOLDER\QBRADDING\EXCELFILES;DriverId=790;MaxBufferSize=2048;PageTimeout=5;"
strcnnODBC = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & ExcelFileName & ";Extended Properties=Excel 8.0"
strcnnODBC = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & ExcelFileName & ";Extended Properties=Excel 5.0"
Set cnnODBC = New ADODB.Connection
cnnODBC.CursorLocation = adUseClient '一定要设置CursorLocation为adUseClient，否则出来的行中的Item会出现空值。而且记录数也不够！
On Error Resume Next
cnnODBC.Open strcnnODBC
If Err.Number <> 0 Then
Err.Raise Err.Number, Err.Source, "无法打开指定的Excel文档数据源！" & vbCrLf & ExcelFileName & vbCrLf & Err.Description
Exit Function
End If
Dim tmpRstExcel
Set OpenExcelFile = GetODBCRecordset("")
End Function

Private Function GetODBCRecordset(ByVal WhereClause As String) As ADODB.Recordset
If mExcelFileName = "" Then
Err.Raise 100, "ExcelReader", "Missing ExcelFileName!"
Exit Function
End If
On Error GoTo 0
Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset

Dim strSQL As String
strSQL = "select cstr('' & [Customer P/N]) as cpn,* from `" & mSheetName & "$`" & " " & vbCrLf & WhereClause
'strSQL = "SELECT *" & vbCrLf & _
"FROM OpenDataSource('Microsoft.Jet.OLEDB.4.0','Data Source=""" & strExcelFile & """;User ID=Admin;Password=;Extended properties=Excel 8.0')...Sheet1$"

'strSQL = "select * from [Excel 8.0;D:\WorkFolder\QbrAdding\ExcelFiles\quotation1002.xls].[Sheet1$]"
'strSQL = "select * from [Excel 8.0;" & strExcelFile & "].[Sheet1$]"

On Error Resume Next
rst.Open strSQL, cnnODBC, adOpenKeyset, adLockOptimistic, adCmdText
Dim errNum As Long
errNum = Err.Number
Select Case errNum
Case 0
Case Else
Err.Raise errNum, Err.Source, "无法打开Excel文档中的" & mSheetName & "工作簿！" & vbCrLf & "请确认文件是否被其他程序打开，或者不存在此工作簿。" & vbCrLf & Err.Description
Exit Function
End Select
On Error GoTo 0

Set GetODBCRecordset = rst
End Function