How do I save access as multiple excel tables?

youxiwuxu11 注册会员
2023-02-27 23:26

This answer quotes ChatGPT

Requirements can be realized through a combination of SQL statements and VBA code.

First, the operation to obtain the dbb table only takes one operation, which can be written as an SQL query or stored procedure to facilitate subsequent calls. Assuming the name of the query is query1, you can execute the query in VBA with the following code:

Dim db As DAO.Database
Set db = CurrentDb()
db.Execute "query1"

Next, you need to export the dbb table data into the three sheets in the Excel file. You can use the ADODB library in VBA to connect the Access database and Excel files, query the data in the dbb table, and then write them into different sheets of Excel files. The specific code is as follows:

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Set cn = New ADODB.Connection
cn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\path\to\access.accdb;Persist Security Info=False;"

Set rs = New ADODB.Recordset
rs.Open "SELECT * FROM dbb", cn

Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet1 As Excel.Worksheet
Dim xlSheet2 As Excel.Worksheet
Dim xlSheet3 As Excel.Worksheet
Set xlApp = New Excel.Application
Set xlBook = xlApp.Workbooks.Add
Set xlSheet1 = xlBook.Worksheets(1)
Set xlSheet2 = xlBook.Worksheets(2)
Set xlSheet3 = xlBook.Worksheets(3)
xlSheet1.Name = "Sheet1"
xlSheet2.Name = "Sheet2"
xlSheet3.Name = "Sheet3"

Dim i As Long
For i = 1 To rs.RecordCount
    If i <= 1000000 Then
        xlSheet1.Cells(i, 1).CopyFromRecordset rs
    ElseIf i <= 2000000 Then
        xlSheet2.Cells(i - 1000000, 1).CopyFromRecordset rs
        xlSheet3.Cells(i - 2000000, 1).CopyFromRecordset rs
    End If
Next i

xlBook.SaveAs "C:\path\to\output.xlsx"
xlBook.Close False