VB6操作EXCEL导入数据库
Private Function FunImpExcel(ByVal strFilePath As String) As Integer
'Excel文件格式
'第一行为表名,第二行为列名,其余行均为数据
On Error GoTo hErr
Dim objConn As New ADODB.Connection
Dim objRS As New ADODB.Recordset
If Dir(strFilePath) = "" Then
MsgBox "文件不存在",vbCritical,"错误"
Exit Function
End If
'定义Excel对象
Dim xlsApp As Object
Dim xlsWb As Object
Dim xlsWs As Object
Set xlsApp = CreateObject("Excel.Application") '建立excel对象
Set xlsWb = xlsApp.Workbooks.Open(strFilePath) '要打开的文档路径
Set xlsWs = xlsWb.Worksheets(1) '选工作表,有多张表时,可以参考此,变换序号指定不同的表
xlsWs.Activate
xlsApp.Visible = false '隐藏,否则会在界面显示出来
'Excel表格的行数和列数
Dim iRowCnt As Integer
Dim iColCnt As Integer
iRowCnt = xlsWs.UsedRange.Rows.Count '这个并不完全准确,在操作数据时要设置退出条件
iColCnt = xlsWs.UsedRange.Columns.Count'这个并不完全准确,在操作数据时要设置退出条件
'下面要根据具体的表格情况决定,这里前面两行是表名和列名
If iRowCnt <= 2 Then
MsgBox "没有需要导入的明细数据","错误"
GoTo hErr
End If
'从第3行开始是明细数据
For i = 3 To iRowCnt
'设置退出条件
If Trim$(xlsWs.Cells(i,3).Value) = "" Then
mdlPub.debug_print "on date found anymore:" & i
Exit For
End If
'第一条数据时,先打开数据库,这里是access
if 3 = i then
'数据库访问操作可以封装成一个公共的函数或过程
Dim strConn as String
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=true;Data Source=test.mdb"
objConn.CursorLocation = adUseClient
objConn.Open strConn
strSQL = "select * from [要导入的表名] where 1=2 "
objRS.CursorLocation = adUseClient
objRS.Open strSQL,objConn,adOpenKeyset,adLockOpt