Hello all,

I run a query based on a begin date and end date on a semi-regular basis to find quantity adjustments made during that period.

First, I need advice with exporting the query result to an excel file, hopefully with a file name that includes the current date.

At the same time, I'm trying to save the selected end date somewhere so the next time I run the query I can begin with (last end date +1).

Any help will be greatly appreciated!

supmktg

this can be done but must use VBA instead of macro. As a general rule: use macro's only if MUST have otherwise use VBA.

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel5, "YourQuery", "YourFile" & Format(Date, "yyyymmdd") & ".xls", True
curentdb.execute "some append/update query here"
From personal experience: You dont want to do what you are describing... You might be exporting up to yesterday but someone else might just put in a record dated yesterday after you exported.

My suggestion is:
Add a ExportedDate field to your table and fill that with an update query...

If you have any more questions i will be around to answer them....

Regards

The Mailman

Thanks Mailman,

The excel export works great! Is there a way to export in comma delimited txt instead of .xls? Also, can I specify the location where the file will be saved?

Supmktg

Originally posted by supmktg
Thanks Mailman,

The excel export works great! (1) Is there a way to export in comma delimited txt instead of .xls? (2) Also, can I specify the location where the file will be saved?

Supmktg

1) Yes, acSpreadsheetTypeExcel5 specifies the filetype remove it and change it to what you want to have. ".xls" is a literal string, you can change that to anything you want. (Flexible aint it:) )
2) Yes, add the desired path before the filename, you can hard code it, use a variable or input from a form or something...
Hardcoded: "C:\temp\YourFile"
Variable:
myFile = "C:\Temp\"
, myFile & "YourFile"
Form:
, Me.path & "YourFile"
--- Note: with user input you might need to check for a \ at the end of your path....

Regards

--- Thinking about it.... your will also need to change from docmd.transferspreadsheet to docmd.transfertext

Mailman,

The DoCmd.transfertext worked perfect.

Thanks for all your help!