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

'取得数据集
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 = - 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 > Then
s_PolicyHoler = rsData("PolicyHoler") & ""
end if