如果数据量不大的话,就一个CELL一个CELL 的去读吧 如果数据量比较大,就先读到RecordSet里面,然后访问RecordSet '打开excel文件 Public Sub openExcel(ByVal strName As String) If blnOpenStatus Then closeExcel End If Set xlsApp = CreateObject("Excel.Application") Set xlsBook = xlsApp.Workbooks.Open(strName) Set xlsSheet = xlsBook.ActiveSheet blnOpenStatus = True End Sub'关闭excel文件 Public Sub closeExcel() If blnOpenStatus Then blnOpenStatus = False xlsBook.Close xlsApp.Quit Set xlsApp = Nothing Set xlsBook = Nothing Set xlsSheet = Nothing End If End Sub'取得excel文件的记录集 Public Function GetExcelRs(ByVal strName As String) As ADODB.Recordset Dim Rs As ADODB.Recordset Dim rsTemp As Object Dim i As Long Set Rs = New ADODB.Recordset Dim conn As String Rs.CursorLocation = adUseClient Rs.CursorType = adOpenDynamic Rs.LockType = adLockBatchOptimistic conn = "data provider=msdasql.1;driver=microsoft excel driver (*.xls);dbq=" & strName Rs.Open "SELECT * FROM [sheet1$]", conn
conn = "select * from OpenRowSet('microsoft.jet.oledb.4.0','Excel 8.0;HDR=YES;IMEX=1;database=" & strName & ";','select * from [Sheet1$]')" Set rsTemp = ExecSQL(conn) Rs.MoveFirst rsTemp.MoveFirst
For i = 1 To Rs.RecordCount Rs.Fields(1).Value = rsTemp.Fields(1).Value Rs.MoveNext rsTemp.MoveNext Next Set GetExcelRs = Rs Set Rs = Nothing Set rsTemp = Nothing End Function 如果是SQL2005,还得把OpenRowSet的控制给打开
如果数据量比较大,就先读到RecordSet里面,然后访问RecordSet
'打开excel文件
Public Sub openExcel(ByVal strName As String)
If blnOpenStatus Then
closeExcel
End If
Set xlsApp = CreateObject("Excel.Application")
Set xlsBook = xlsApp.Workbooks.Open(strName)
Set xlsSheet = xlsBook.ActiveSheet
blnOpenStatus = True
End Sub'关闭excel文件
Public Sub closeExcel()
If blnOpenStatus Then
blnOpenStatus = False
xlsBook.Close
xlsApp.Quit
Set xlsApp = Nothing
Set xlsBook = Nothing
Set xlsSheet = Nothing
End If
End Sub'取得excel文件的记录集
Public Function GetExcelRs(ByVal strName As String) As ADODB.Recordset
Dim Rs As ADODB.Recordset
Dim rsTemp As Object
Dim i As Long
Set Rs = New ADODB.Recordset
Dim conn As String
Rs.CursorLocation = adUseClient
Rs.CursorType = adOpenDynamic
Rs.LockType = adLockBatchOptimistic
conn = "data provider=msdasql.1;driver=microsoft excel driver (*.xls);dbq=" & strName
Rs.Open "SELECT * FROM [sheet1$]", conn
conn = "select * from OpenRowSet('microsoft.jet.oledb.4.0','Excel 8.0;HDR=YES;IMEX=1;database=" & strName & ";','select * from [Sheet1$]')"
Set rsTemp = ExecSQL(conn)
Rs.MoveFirst
rsTemp.MoveFirst
For i = 1 To Rs.RecordCount
Rs.Fields(1).Value = rsTemp.Fields(1).Value
Rs.MoveNext
rsTemp.MoveNext
Next
Set GetExcelRs = Rs
Set Rs = Nothing
Set rsTemp = Nothing
End Function
如果是SQL2005,还得把OpenRowSet的控制给打开