读取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
如果您看了本篇博客,觉得对您有所收获,请点击右下角的 [推荐]
如果您想转载本博客,请注明出处
如果您对本文有意见或者建议,欢迎留言
感谢您的阅读,请关注我的后续博客
联系客服