Forum Discussion
How to make a Saved Export
Hello All,
My very first post in the MS-Access lounge.
I need to automate these steps:
- Press F-11 to get to the All Access Objects pane.
- Select a Table named Priority
- The Priority table has a Date field and I click on the arrow at the right edge of the field name.
- I move the mouse to the date Filter option in the dropdown menu.
- I select Today.
- Now the table has all priority with today's date in the date field.
- I click on the left most corner to select all the table rows.
- I copy the selection
- I paste into MS-Excel.
Is there a way to save all these steps into a Saved Export so that it can be made easier and standardized.
If it can be done without the use of VBA so much the better.
Still using Access 2007 - 2016 file format
Thanks in advance.
Gigi...
This can be done with a query (e.g. "PriorityToday") that has a where clause for the current date. Change Datefield to internal date name in table Priority:
SELECT * FROM Priority WHERE [Datefield] = Date();Export "PriorityToday" as an Excel workbook (right-click menu). On the last page of the export wizard, name a new Saved Export. If you wish, create an Outlook Task.
Done.
By the way, allowing MS Access but not VBA does not make sense to me.Why no VBA? You can do that in VBA with a single line of code. Something like
DoCmd.TransferSpreadsheet acExport,, "SELECT *FROM Priority WHERE Your Date = Date()", "YourExcel.xlsx"
Thank You XPS35,
The reason for no VBA is because this is a work assignment and to go the VBA way, will require approvalS, notice the uppercase S, which would be probably approved in the year 2065! :-) At times I do dislike my job, but most of the times I love it.
you can't use an SQL string in any Transfer() functions.
create a Query and use the Query in your Transfer.
example:
SELECT * FROM Priority Where YourDateField = Date;save the query (say Query1).
now use Query1 to your TransferSpreadsheet.
(this will save to Priority.xlsx in Documents folder)
Docmd.TransferSpreadsheet acExport,acSpreadsheetTypeExcel12Xml,"Query1",environ$("userprofile") & "\Documents\Priority.xlsx"Thank You arnel_gp
Could you tell me if I could run this from MS-Excel directly say as if I was doing a Get External Data.
From MS-Excel Data Ribbon, Get Data, From Database, From Microsoft Access Database
Will any thing change if the database has a password?
Thanks,
GiGi
