Filter Table and Export to Multiple Excel Workbooks

[Neilbees]

Hi all

After a thorough search I can't find a solution to my issue. Any help gratefully received.

I have a table (tblMaster). In the table is a field called Agency. The table is 200,000+ records and there are around 35 Agencies. The table grows monthly, with potentially new Agencies added all the time.

What I have been asked to do is to have a button on a Form which, when clicked, exports to Excel a separate workbook with all the table data for each Agency in the table.

I have suggested a combo-box on the Form that passes the Agency name to a query and then exports (so they could have control of which Agency to export) but no - they just want one click, spool through the table and create the 35 (or so) exports.

Can anyone point me in the right direction?

 

[pr2-eugin]

Create a Recordset and loop through the Recordset to dish out the table data.. Something along the lines of..

Private Sub someButtonName_Click()
    Dim tmpRS As DAO.Recordset, strSQL As String
    Set tmpRS = CurrentDB.OpenRecordset "SELECT theAgencyFieldName FROM theTableName GROUP BY theAgencyFieldName;"
    Do While Not tmpRS.EOF
        strSQL = "SELECT theTableName.* FROM theTableName WHERE theAgencyFieldName = '" & tmpRS.Fields(0) & "'"
        DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _ 
                strSQL,"C:\yourFolder\Agency - " & tmpRS.Fields(0) & ".xlsx", True, "A1:G12"
        tmpRS.MoveNext
    Loop
    Set tmpRS = Nothing
End Sub

 

[Neilbees]

Hi Paul

Thanks for your very quick reply. I understand the VBA I think but I've had a couple of problems.

The first one was that the 'Set tmprs =' line was highlighted red with a Compile Error expected end of statement but I think I've fixed that with brackets. Is that right?!?

However, when I try and click the button I get a Runtime Error 3011: The Microsoft Access Database Engine could not find the object 'SELECT tblMaster* etc etc. Then the debug highlights the transferspreadsheet line in yellow.

Can you help?

 

[pr2-eugin]

The Query should be SELECT tblMaster.* you are missing a full stop after the table name and before the asterisk..

 

[Neilbees]

Hi Paul

Apologies - that was a typo in my post. I've attached a screenshot of the VBA window with the code and error message.

Hope you can help!