Exporting tables to Excel

[Aist]

I am trying to export an Access table to an excel spreadsheet with the date
appended on to the filename i.e. OutputTo excel with today's date on the end
of the filename, any ideas how I can do this?

 

[SusanV]

Hi Aist,

"YourFile" & Format$(Date, "mm-dd-yy") & ".xls"

 

[Aist]

Hi Susan,

Unfortunately it doesn't seem to work. I Have specified the following:

c:\"ScannerResults" & Format$(Date, "mm-dd-yy") &".xls" in the output file
section but I get an error message appearing saying "could not create". Am I
using incorrect syntax (Have tried a few combinations of this)?

Many thanks

 

[SusanV]

Your quotes are not right - you dropped the drive letter. Try:

"c:\ScannerResults" & Format$(Date, "mm-dd-yy") &".xls"

This is going to create a file in the root of c - if you meant for
ScannerResults to be a directory on C: then add a training backslash to the
hard-coded part:

"c:\ScannerResults\" & Format$(Date, "mm-dd-yy") &".xls"

 

[SusanV]

*trailing* not training LOL

Your quotes are not right - you dropped the drive letter. Try:

"c:\ScannerResults" & Format$(Date, "mm-dd-yy") &".xls"

This is going to create a file in the root of c - if you meant for
ScannerResults to be a directory on C: then add a training backslash to
the hard-coded part:

"c:\ScannerResults\" & Format$(Date, "mm-dd-yy") &".xls"

 

[Aist]

I knew what you meant ! Still get the same meesage though and no file being
created. it doesn't seem to like the "". Does this work for you? I am using
Access 2003.

Thanks,

Aist,

 

[SusanV]

Perhaps because this is in a macro... won't work for me that way either in a
macro - I use this in code all the time...

 

[Aist]

Thanks anyway. I will see if I can set this up in VB.

 

[SusanV]

The VB is simple:

DoCmd.OutputTo acTable, "TableName", acFormatXLS, "c:\ScannerResults\" &
Format$(Date, "mm-dd-yy") &".xls", -1

 

[Steve Schapel]

Forgot the = and will, I think, need to designate the Date as a function.

="C:\ScannerResults" & Format(Date(), "mm-dd-yy") &".xls"

 

[Steve Schapel]

Aist,

... i.e. OutputTo excel with today's date on the end
of the filename

I would aslo recommend TransferSpreadsheet rather than OutputTo.

 

[Aist]

Thanks Steve, thought Date should be a function! Will let you know how I get
on!

 

[Aist]

fekin sorted !

Aist,


I would aslo recommend TransferSpreadsheet rather than OutputTo.

 

[Steve Schapel]

Cool!