Transferspreadsheet to export to Excel with a unique new filename

I'm using the “Transferspreadsheet” macro in Access 2003 to create an Excel file when the user exits the database. It exports the data just fine, but overwrites the existing spreadsheet data in the existing excel file. I'd like it to create a new spreadsheet and name it, using the current date or something like that so prior exports are not overwritten.
I'm not using SQL--Just plain old Access 2003.--Thanks.
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

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.

Open database modules new->insert function call it test

Public Function test()
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "YourTableName", "C:\" & Format(time, "dd-mm-yy-hh-nn-ss") & "test.xls"
End Function

Open your macro in design view
Action           runCode

Function Name     test()

save and run it

If you don't want to create the tiny VB function, you can use Dmitry's suggestion directly in the macro, somply change this argument:

    File Name     ="C:\TEMP\The data " & Format(Now(), "dd-mm-yy-hh-nn-ss") & ".xls"


I tested in macro it does not take Path this way.My be I missed something,what way I suggested to do it this way
IT Pros Agree: AI and Machine Learning Key

We’d all like to think our company’s data is well protected, but when you ask IT professionals they admit the data probably is not as safe as it could be.

mapalaska2003Author Commented:

I am working with your suggestion, but there is a problem with the table name argument???

Public Function BNC()

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel5, TBL_PermitHolder, "C:\" & Format(Time, "dd-mm-yy-hh-nn-ss") & BNC.xls

End Function
mapalaska2003Author Commented:
Also, I'm using Excel 2003.

The file name arguments works this way, but perhaps you do not have a TEMP folder. I expected you to edit the string to something suitable in your environment, for example:

    ="C:\Documents And Settings\mapalaska2003\Desktop\TEST " & Format(Now(), "dd-mm-yy-hh-nn-ss") & ".xls"

About your other question, note the quotes around "YourTableName" in Dmitry's suggestion.

Good luck!
You are missing last quote
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel5, TBL_PermitHolder, "C:\" & Format(Time, "dd-mm-yy-hh-nn-ss") & BNC.xls"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel5, TBL_PermitHolder, "C:\" & Format(Time, "dd-mm-yy-hh-nn-ss") & "BNC.xls"
One more check latest Excel should be acSpreadsheetTypeExcel9

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, TBL_PermitHolder, "C:\" & Format(Time, "dd-mm-yy-hh-nn-ss") & "BNC.xls"
Again, do not forget the quotes around "TBL_PermitHolder" ;)

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
MArkus you are correct

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "TBL_PermitHolder", "C:\" & Format(Time, "dd-mm-yy-hh-nn-ss") & "BNC.xls"
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.