MS Access: Export Qry to Excel with Timestamp Name

Hello,

I have an MS Access 2007 database. When the database is closed, I would like to:

A. Save the query: qry_members as a .xls file to the location: C:\members

B. Save the filename with the current date appended to the end - and a date + 24 hour timestamp. E.g If it is 8 April at 2:05pm the file would be saved as:

C:\members\qry_members_8Apr15_1405.xls

If this is possible, how would I do this?

Thanks
LVL 1
dabug80Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Gustav BrockCIOCommented:
You can run this command:
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qry_members", "C:\members\qry_members" & Format(Now, "_dmmmyy_hhnn"), True

Open in new window

/gustav
0
Rey Obrero (Capricorn1)Commented:
try this

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qry_members", "C:\members\qry_members" & Format(Now, "_dmmmyy_hhnn") & ".xls", True

or, since you are using A2007, use the Excel 2007 file format of  ".xlsx"

DoCmd.TransferSpreadsheet acExport, 10, "qry_members", "C:\members\qry_members" & Format(Now, "_dmmmyy_hhnn") & ".xlsx", True
0
dabug80Author Commented:
Thanks. I would like this to execute when a user closes the form 'switchboard'. Is this possible?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Gustav BrockCIOCommented:
Yes, use the OnUnload event of that form:
Private Sub Form_Unload(Cancel As Integer)
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qry_members", "C:\members\qry_members" & Format(Now, "_dmmmyy_hhnn"), True
End Sub

Open in new window

/gustav
0
Rey Obrero (Capricorn1)Commented:
better place it on the click event of the Close/Exit button of your switchboard
- and use the correct command line

private sub cmdClose_click()

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qry_members", "C:\members\qry_members" & Format(Now, "_dmmmyy_hhnn") & ".xls", True

end sub
0
Gustav BrockCIOCommented:
Please, there is nothing more "correct" at that command line.

The .xls extension is applied by default for the format acSpreadsheetTypeExcel9 which is what is requested.

/gustav
0
dabug80Author Commented:
Private Sub Form_Unload(Cancel As Integer)
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qry_members", "C:\members\qry_members\" & Format(Now, "_dmmmyy_hhnn"), True
End Sub

Open in new window


Thanks - I used the above code - and added a '/' after the file path. Now the file transfers - but it's named "_10Apr15_1254" - is it possible to add the characters 'qry_members' at the front of this file name?
0
Rey Obrero (Capricorn1)Commented:
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qry_members", "C:\members\qry_members\qry_members" & Format(Now, "_dmmmyy_hhnn") & ".xls", True
0
Gustav BrockCIOCommented:
> .. added a '/' after the file path

No, it is a backslash you added and that turns the filename into a folder name.

You asked for:

> Save the query: qry_members as a .xls file to the location: C:\members

Are you really sure you now wish to:

> Save the query: qry_members as a .xls file to the location: C:\members\qry_members

If so, append "qry_members" to the path:
Private Sub Form_Unload(Cancel As Integer)
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qry_members", "C:\members\qry_members\qry_members" & Format(Now, "_dmmmyy_hhnn"), True
End Sub

Open in new window

/gustav
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
dabug80Author Commented:
I split the points as you were both helpful. Rey got in first with the file name modification - But Gustav provided the more complete solution.

Thanks for your help.
0
Gustav BrockCIOCommented:
You are welcome!

/gustav
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.