导出访问数据导入Excel工作簿和分裂数据分成多个表基于列的值 [英] Export Access data into Excel workbook and split data into multiple sheets based on column value

问题描述

的样本数据（称为Pets_data_table本地Access表）

ID | Pet_Type | Pet_Owner

1      Dog        Jane Doe         
2      Cat        John Doe
3      Hamster    Bob Doe
4      Dog        Melissa Doe 
5      Cat        Aaron Doe

目前，我可以在这个表中的数据导出一个Excel工作簿，并将数据分割成按一个特定领域的不同值的Excel工作簿中的多个工作表。我用下面的VBA根据Pet_Type'字段的不同值拆分数据：

At the moment, I can export the data in this table to one Excel workbook, and split the data into multiple sheets within that Excel workbook according to distinct values of a specific field. I use the following VBA to split the data according to distinct values of the 'Pet_Type' field:

    Dim db As DAO.Database
    Set db = CurrentDb()
    Dim strPath As String
    strPath = "C:\Desktop\" & "Pets_dataset_export_" & format(date(),"yyyy-mm-dd") & ".xlsx" 
    DoCmd.TransferSpreadsheet acExport, 10, "Qry - Dog", strPath, True, "Dog"
    DoCmd.TransferSpreadsheet acExport, 10, "Qry - Cat", strPath, True, "Cat"
    DoCmd.TransferSpreadsheet acExport, 10, "Qry - Hamster", strPath, True, "Hamster"

    Set db = Nothing
    MsgBox "Export operation completed"

这表现良好时，现场我分裂与有少量不同值的数据。

This performs well when the field I am splitting the data with has a small number of distinct values.

但是，它是低效率的，当有大量的在现场的不同值的予想与分割数据

However, it is inefficient when there are a large number of distinct values in the field I want to split the data with.

我想实现一个更加动态的方法不同的值，让我的数据集拆配有1场...... N多。

I would like to implement a more dynamic approach that allows me to split a dataset with a field that has 1...n number of distinct values.

推荐答案

加载基于查询一个记录集，让你的独特的宠物类型...

Load a single recordset based on a query which gives you the unique pet types ...

SELECT DISTINCT p.Pet_Type
FROM Pets_data_table AS p;

然后走这条记录，修改已保存的查询（ qryExportMe 的），以 SELECT 当前 Pet_Type ，并导出查询...

Then walk that recordset, alter a saved query (qryExportMe) to SELECT the current Pet_Type, and export the query ...

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim rs As DAO.Recordset
Dim strPath As String
Dim strSelectOneType As String
Dim strSelectPetTypes As String

' (change strPath back to what you need)
strPath = CurrentProject.Path & Chr(92) & "Pets_dataset_export_" & _
    Format(Date, "yyyy-mm-dd") & ".xlsx"
strSelectPetTypes = "SELECT DISTINCT p.Pet_Type" & vbCrLf & _
    "FROM Pets_data_table AS p;"

Set db = CurrentDb
Set rs = db.OpenRecordset(strSelectPetTypes, dbOpenSnapshot)
Do While Not rs.EOF
    strSelectOneType = "SELECT p.ID, p.Pet_Type, p.Pet_Owner" & vbCrLf & _
        "FROM Pets_data_table AS p" & vbCrLf & _
        "WHERE p.Pet_Type='" & rs!Pet_Type.Value & "';"
    Debug.Print strSelectOneType
    Set qdf = db.QueryDefs("qryExportMe")
    qdf.SQL = strSelectOneType
    qdf.Close
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, _
        "qryExportMe", strPath, True, rs!Pet_Type.Value
    rs.MoveNext
Loop
rs.Close

注意：code要求保存的查询， qryExportMe 的，存在。但它的SQL属性没有关系，因为你每次都通过主改做的，而循环。

Note that code requires that the saved query, qryExportMe, exists. But its SQL property doesn't matter because you'll change it each time through the main Do While loop.

这篇关于导出访问数据导入Excel工作簿和分裂数据分成多个表基于列的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！