读取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.Recordset10 Dim sSQL As String11 If UCase(strType) = UCase('.xls') Then12 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 Else14 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 If16 sSQL = 'SELECT ' & IIf(sField = '', '*', sField) & ' FROM ' & '[' & sTableName & ']'17 If Trim(strWhere) <> '' Then _18 sSQL = sSQL & ' WHERE ' & strWhere19 20 If Trim(sOrderBy) <> '' Then _21 sSQL = sSQL & ' Order BY ' & sOrderBy22 rs.Open sSQL, conn, adOpenStatic, adLockReadOnly23 Set getRecordSetForExcels_1 = rs24 25 Exit Function26 errHand:27 If Err.Number = -2147467259 Then28 rs.Open sSQL, conn, adOpenStatic, adLockReadOnly29 Set getRecordSetForExcels_1 = rs30 Else31 MsgErr Err.Description32 End If33 End Function 调用该方法: 1 Dim rsData As ADODB.Recordset 'Excel中的所有的数据2 dim s_PolicyHoler as string3 Set rsData = getRecordSetForExcels(txtFileName.Text, sSheetName & '$', '[投保人名字] AS [PolicyHoler]' & _4 ' ,[保单号] AS [CCICPolicynumber],[客户号] AS [AIAIND]' & _5 ' ,[投保人ID] AS [HolerID]')6 7 If rsData.RecordCount > 0 Then8 s_PolicyHoler = rsData('PolicyHoler') & ''9 end if 如果您看了本篇博客,觉得对您有所收获,请点击右下角的 [推荐] 如果您想转载本博客,请注明出处 如果您对本文有意见或者建议,欢迎留言 感谢您的阅读,请关注我的后续博客 |
|