VB6.0 读取Excel文件并返还数据集RecordSet

读取Excel文件并返还数据集RecordSet

该方法适用于.xls,.xlsx类型的文件

读取Excel文件的Function：

'取得数据集
Function getRecordSetForExcels(sFilePath As String, _
                                sTableName As String, _
                                Optional sField As String, _
                                Optional strWhere As String, _
                                Optional sOrderBy As String) As ADODB.Recordset
On Error GoTo errHand:
    Dim conn As New ADODB.Connection
    Dim rs As New ADODB.Recordset
    Dim sSQL As String
    If UCase(strType) = UCase(".xls") Then
        conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sFilePath & ";Extended Properties='Excel 8.0;HDR=yes;imex=1';Persist Security Info=False"
    Else
        conn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & sFilePath & ";Extended Properties='Excel 8.0;HDR=yes;imex=1';Persist Security Info=False"
    End If
    sSQL = "SELECT " & IIf(sField = "", "*", sField) & " FROM " & "[" & sTableName & "]"
    If Trim(strWhere) <> "" Then _
       sSQL = sSQL & " WHERE " & strWhere
       
    If Trim(sOrderBy) <> "" Then _
    sSQL = sSQL & " Order BY " & sOrderBy
    rs.Open sSQL, conn, adOpenStatic, adLockReadOnly
    Set getRecordSetForExcels_1 = rs

Exit Function
errHand:
If Err.Number = -2147467259 Then
    rs.Open sSQL, conn, adOpenStatic, adLockReadOnly
    Set getRecordSetForExcels_1 = rs
Else
    MsgErr Err.Description
End If
End Function

调用该方法：

Dim rsData As ADODB.Recordset 'Excel中的所有的数据
dim s_PolicyHoler as string
Set rsData = getRecordSetForExcels(txtFileName.Text, sSheetName & "$", "[投保人名字] AS [PolicyHoler]" & _
        " ,[保单号] AS [CCICPolicynumber],[客户号] AS [AIAIND]" & _
        " ,[投保人ID] AS [HolerID]")

If rsData.RecordCount > 0 Then
    s_PolicyHoler = rsData("PolicyHoler") & ""
end if