Conditionally Export Access Table to Excel With Specific Criteria

[jimboperrone]

Is it possible to automate the conditional export of an Access table to Excel if it meets specific criteria? I would like to export a final Access table to Excel only if the Updates field value is >10 after I run my macro. Otherwise, I do not want the table to be exported from Access to Excel. I am a beginner in Access and currently use a macro to export my final table to Excel. I'm unsure if code can be used to only export the table if it meets the above criteria or how to go about implementing the code.

[June7]

Yes, it's possible. Use an If Then Else structure. The real trick is figuring out what event to put code into. Are users entering value into Updates field? If you want to provide db for analysis, follow instructions at bottom of my post.

[jimboperrone]

I have attached a sample database.

In summary, I run an audit each day to see if any providers need to be added or removed to/from a specific DB2 table based upon criteria. I create summary tables with counts of how many providers are being added or deleted every day when I run the audit so that I can add the counts to a dashboard report. (Note: the report criteria have changed since I initially posted as I now need to extract all updates >0.) I only want to export the summary tables to Excel if they contain actual counts. If the table is empty, I don't want to export the table to Excel.

In the sample database, I want the Updates_080Adds table to export, but I do not want the Updates_080Deletes table to be exported. Currently the Updates_080Deletes table exports with the header fields to Excel.

[June7]

I don't use macros, only VBA. However, both have an If Then Else structure. Whichever way you go, need code to look at tables to see if there are records to export. For each table, use DLookup() or DCount() for that.

Shouldn't have to write summary data to tables. Build aggregate queries and export those.

Could use a form with a button called Export. VBA could look like:

If DCount("*", "FinalTable080Adds") > 0 Then
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "qryUpdates_080Adds", "C:\Users\sk3998\Desktop\Updates080Adds.xls"
End If
If DCount("*", "FinalTable080Deletes") > 0 Then
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "qryUpdates_080Deletes", "C:\Users\sk3998\Desktop\Updates080Deletes.xls"
End If

Or use macro If Then action.

[jimboperrone]

This helped a lot - thank you! I was able to add code to the macro and it successfully worked.