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

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

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

读取Excel文件的Function：

 1 '取得数据集
 2 Function getRecordSetForExcels(sFilePath As String, _
 3                                 sTableName As String, _
 4                                 Optional sField As String, _
 5                                 Optional strWhere As String, _
 6                                 Optional sOrderBy As String) As ADODB.Recordset
 7 On Error GoTo errHand:
 8     Dim conn As New ADODB.Connection
 9     Dim rs As New ADODB.Recordset
10     Dim sSQL As String
11     If UCase(strType) = UCase(".xls") Then
12         conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sFilePath & ";Extended Properties='Excel 8.0;HDR=yes;imex=1';Persist Security Info=False"
13     Else
14         conn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & sFilePath & ";Extended Properties='Excel 8.0;HDR=yes;imex=1';Persist Security Info=False"
15     End If
16     sSQL = "SELECT " & IIf(sField = "", "*", sField) & " FROM " & "[" & sTableName & "]"
17     If Trim(strWhere) <> "" Then _
18        sSQL = sSQL & " WHERE " & strWhere
19        
20     If Trim(sOrderBy) <> "" Then _
21     sSQL = sSQL & " Order BY " & sOrderBy
22     rs.Open sSQL, conn, adOpenStatic, adLockReadOnly
23     Set getRecordSetForExcels_1 = rs
24 
25 Exit Function
26 errHand:
27 If Err.Number = -2147467259 Then
28     rs.Open sSQL, conn, adOpenStatic, adLockReadOnly
29     Set getRecordSetForExcels_1 = rs
30 Else
31     MsgErr Err.Description
32 End If
33 End Function

调用该方法：

1 Dim rsData As ADODB.Recordset 'Excel中的所有的数据
2 dim s_PolicyHoler as string
3 Set rsData = getRecordSetForExcels(txtFileName.Text, sSheetName & "$", "[投保人名字] AS [PolicyHoler]" & _
4         " ,[保单号] AS [CCICPolicynumber],[客户号] AS [AIAIND]" & _
5         " ,[投保人ID] AS [HolerID]")
6 
7 If rsData.RecordCount > 0 Then
8     s_PolicyHoler = rsData("PolicyHoler") & ""
9 end if