export query to Excel file (1 Viewer)

[eugzl]

Hi All.
How create VBA Function or Procedure to perform following:

1. Filter table data by current date
2. Export result to Excel file
3. Locate created Excel file to certain network folder

If this is possible, how to do it?
Thanks

 

[The_Doc_Man]

This is best approached using a "divide and conquer" method.

1. Write a query to filter your table data based on the current date, which is returned by function Date$()

2. Export using the DoCmd.TransferSpreadsheet action, which allows you to name the query that will contain your input and will allow you to specify the output file name AND PATH.

For part 2, here is a link to an article that might explain this well enough for you.

How to Export Access Data to Excel using VBA

How export Access database data to Excel: a VBA program to export data from a query or table to a spreadsheet and to format the spreadsheet

www.consultdmw.com

As to how you trigger this, there are many options. Two of them would include

A: A macro using a RunCode option, in which you trigger the TransferSpreadsheet to run on a pre-defined query. Then just run the Macro.

B: An event procedure triggered by a Button-Click event on a form. Then load the form and click the command button.