Exporting to excel

[Mcgrco]

I want to export a table form my database to excel where the name will contain the date.
in the export directory ive put "C:\WINNT\Profiles\mcgrco\Desktop\test" & (Reports![cob]![Reporting Date] & ".xls"
This just gives me a failure creating file message anyone one got any suggestions.

Thanks in advance

 

[shuff]

I remember a similar problem to this awhile back. I don't have the complete answer, but I remember being told that it can't be done in object code such as you are trying to do (I tried the same approach!). To name dynamically, it has to be done in VB code. Unfortunately, I don't know VB very well, so hopefully someone else can pick up where I left off? Good luck, SLH

 

[The_Doc_Man]

This is a guess, not a certainty - but if the [Date] field (I've shortened the name to keep my response shorter) is actually of type Date/Time, then it is not text. It is a double-float number in binary. It might not contain bit patterns that, when interpreted as text, are legal in file names.

Rather than using [Date], try

Format$([Date], "ddmmyyyy")

(or use just "ddmmyy" if you have a problem with long names.)

 

[Pat Hartman]

It's late and I don't really have time to test this but I think there are two problems. One is that you can't refer to a field in a report from outside of the report (since the control could appear multiple times how would Access be able to figure out which one you were referring to?) and the other is that macros can't do variable substitution.

If you convert the macro to VBA (use the option under the Macro menu item), you will be able to provide a variable for the date but I think you'll need to get it from someplace other than a report control.