Export filename includes Date

  • Thread starter Thread starter Jackson via AccessMonster.com
  • Start date Start date
J

Jackson via AccessMonster.com

Hi,

I've had a look through these forums but I'm still not having any luck with
this. I simply want to export a query that saves it as an excel file with a
fixed text prefix and then today's date, ie ABC220605. I'd even settle for
simply 220605.xls as the filename. I've created the simple
TransferSpreadsheet Macro then I've tried to alter it in VBA. When I try to
add anything such as "\\Directory\ABC & now() & .xls
it litterally saves it as ABC & Now.xls

I've tried to use date(), even just have no text using either date or now but
it still saves it literally. What am I doing wrong?
 
Jackson,

You need to leave the function ouside the quotes, so Access understands
it's a function and treats it as such:

"\\Directory\ABC" & Date() & ".xls"

HTH,
Nikos
 
That's getting better, but its making it ABC22\06\2005.xls and as such Access
is saying it can't find the directory. But why would it add in the slashes
like that? They're backwards for a start plus if I change it to Format(date(),
ddmmyy) it does the same thing...?
 
Also if I use the now() expression, it comes up failure creating file.
 
Good! By the way, the answer is the Format() function, if you have not
figured that out. Also, the difference betweeen Date() and Now() is the
former returns the current date only, while the latter returns current
date and time.

Nikos
 
Back
Top