vba上传数据到mysql vba导入excel数据到access

转载

mob6454cc780924 2023-11-23 15:10:37

文章标签 vba上传数据到mysql Access 数据库 Data 文章分类 MySQL 数据库

今天研究了一下很久以前就看到的vb把Excel写入Access和Access写入Excel的函数。

先看这两个函数（原来有些错误，我改掉了，还有记得要引用“Microsoft DAO 3.6 Object Library”）：

Private Sub XlsToMdb(sExcelPath As String, sAccessDBPath As String, sSheetName As String, sAccessTable As String)
    '功能：将Excel文件中的工作簿导出到Access数据库中的表
    '输入参数1、sExcelPath：要导出资料的   Excel   档案路径名称   (Workbook   path)，例如   C:\book1.xls
    '输入参数2、sAccessDBPath：要导入的   Access   档案路径名称，例如   C:\Test.mdb
    '输入参数3、sSheetName：要导出资料的文件名称   (Sheet   name)，例如   Sheet1
    '输入参数4、sAccessTable：要导入的   Access   Table   名称，例如   TestTable
    '作者:YOKI   最后修改：2003-08-21
    '调用：Call   XlsToMdb("c:\book1.xls","c:\test1.mdb",,"$Sheet1","TestTable")
    '结果：將   C:\book1.xls   中的   Sheet1   导入   C:\Test.mdb   成为   TestTable
    Dim db As Database
    Set db = OpenDatabase(sExcelPath, True, False, "Excel 5.0")
    db.Execute ("Select * into [;database=" & sAccessDBPath & "]." & sAccessTable & " FROM [" & sSheetName & "$]")
End Sub

Public Sub MdbToxls(sAccessFileName As String, sExcelPath As String, sSheetName As String, sAccessTable As String)
    '功能：将Access数据库中表导出到Excel文件中
    '输入参数1：cAccessFileName     Access文件全路径             如:c:\temp\test1.mdb
    '输入参数2：sExcelPath               Excel文件的全路径           如:c:\temp\test2.xls
    '输入参数3：sSheetName               Excel中的工作簿名           如：$Sheet1
    '输入参数4：sAccessTable           Access数据库中的表         如:Table1
    '作者:YOKI   最后修改：2003-08-21
    '调用：Call   MdbToxls("c:\temp\test1.mdb","c:\temp\test2.xls","$Sheet1","Table1")
    '结果：将C:\temp\test1.mdb中的Table1表导入到c:\temp\test2.xls的$Sheet1中

    Dim db As DAO.Database
    Set db = Workspaces(0).OpenDatabase(sAccessFileName)
    db.Execute "SELECT * INTO [Excel 8.0;DATABASE=" & sExcelPath & "].[" & sSheetName & "] FROM [" & sAccessTable & "]"
    db.Close
    Set db = Nothing
End Sub

看看这两个函数，都相当简单，就一句sql就完成了。

我发现这两个函数速度非常快，专门弄了一个5000行10列的Excel表格，先复制到Access，再复制回来，大概也就1秒。

想起了原来写的一个波形分析的程序，读取Excel非常慢，所以就想用这种办法改一下那个程序看看。

下面是原来程序读取Excel的部分，从Excel读取数据，存到data数组里面

Private Sub GetData2()
    Dim ColCount As Long, RowCount As Long
    Dim i As Long '行
    Dim j As Long '列
    Dim xlApp As New Excel.Application
    Dim xlBook As Excel.Workbook
    Dim xlsheet As Excel.Worksheet
    
    xlApp.Visible = False
    Set xlBook = xlApp.Workbooks.Open(App.Path & "\aaa.XLS")
    Set xlsheet = xlBook.Worksheets(1)
    ColCount = xlsheet.UsedRange.Cells.Columns.Count       '得到总列
    RowCount = xlsheet.UsedRange.Cells.Rows.Count       '得到总行
    
    ReDim Data(RowCount - 2, ColCount) '前两行不是数据，所以可以少掉2行
    
    For i = 3 To RowCount
        For j = 1 To ColCount
            Data(i - 2, j) = xlsheet.Cells(i, j) '先行后列
        Next j
    Next i
    
    xlBook.Close
    xlApp.Quit
    Set xlApp = Nothing
End Sub
修改后的程序Private Sub GetData()
    Dim db As Database
    Dim rs As Recordset
    Dim i As Integer
    Dim j As Integer
    Set db = OpenDatabase(App.Path + "\aaa.xls", True, False, "Excel 5.0")
    Set rs = db.OpenRecordset("select * from [sheet1$]")
    i = 1
    rs.MoveLast
    ReDim Data(rs.RecordCount - 1, 2)
    rs.MoveFirst
    rs.MoveNext
    While Not rs.EOF
        Data(i, 1) = rs.Fields(0).Value
        Data(i, 2) = rs.Fields(1).Value
        i = i + 1
        rs.MoveNext
    Wend
End Sub

可以看出，用这种办法读取Excel速度比原来快多了。这个程序最重要的地方就是那个sql语句，下面分析一下。

Select * into [;database=" & sAccessDBPath & "]." & sAccessTable & " FROM [" & sSheetName & "$]

用几个参数代进去

Select * into [;database="c:\1.mdb"].table1 FROM [Sheet1$]

select是选择语句，加个into [;database="c:\1.mdb"].table1就是把结果直接存入数据库，from字句表示了把Sheet1当成一个数据库的表（要在Sheet1后面加个$，不然就找不到表），这其实也就是一句简单的sql语句

从这里可以想象，读取excel的语句大概是这样"select * from sheet1$"这样的形式

引用的DAO组件有rs对象，db对象，经过实验后，发现Set rs = db.OpenRecordset("select * from [sheet1$]")就可以把Excel读出来放到rs这个记录集里面，后面的操作就简单了，跟操作数据库的记录集一摸一样，不过其中有两个地方要注意。

1、这个Excel文件前两行都不是数据，如下