Forum Discussion
Re: How to make a Saved Export
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"
5 Replies
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
It's well documented if you use ODBC.
Driver={Microsoft Access Driver (*.mdb,*.accdb)};Dbq=YourPath\\YourDB.accdb;Uid=Admin;Pwd=YourPassword;
With the default PowerQuery it should be possible too, but documentation is lacking. Use OleDb there:
OleDb.Query("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=YourPath\\YourDB.accdb;Jet OLEDB:Database Password=YourPassword;", "SELECT * FROM YourTable")
I was not sure about that and was not able to test it at the moment. Thanks for the the improvement.
