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.
mapalaska2003Asked:
Who is Participating?
 
harfangCommented:
Again, do not forget the quotes around "TBL_PermitHolder" ;)
(°v°)
0
 
dmitryz6Commented:
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
0
 
harfangCommented:
Hello,

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"

Cheers!
(°v°)
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.

 
dmitryz6Commented:
Markus.

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
0
 
mapalaska2003Author Commented:
Dmitry,

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
0
 
mapalaska2003Author Commented:
Also, I'm using Excel 2003.
 Thanks!!
0
 
harfangCommented:
Hello,

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!
(°v°)
0
 
dmitryz6Commented:
You are missing last quote
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel5, TBL_PermitHolder, "C:\" & Format(Time, "dd-mm-yy-hh-nn-ss") & BNC.xls"
0
 
dmitryz6Commented:
typo
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel5, TBL_PermitHolder, "C:\" & Format(Time, "dd-mm-yy-hh-nn-ss") & "BNC.xls"
0
 
dmitryz6Commented:
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"
0
 
dmitryz6Commented:
MArkus you are correct

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

All Courses

From novice to tech pro — start learning today.