[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1399
  • Last Modified:

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.
0
mapalaska2003
Asked:
mapalaska2003
  • 6
  • 3
  • 2
2 Solutions
 
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
 
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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
 
harfangCommented:
Again, do not forget the quotes around "TBL_PermitHolder" ;)
(°v°)
0
 
dmitryz6Commented:
MArkus you are correct

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

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 6
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now