Not a problem.
Create a new Macro.
The Action you want is TransferSpreadsheet
Transfer Type is Export
Spreadsheet Type will depend on your version of office, most likely, it will
be
Microsoft Excel 8-10
Table Name is the name of the table or query you want to export
File Name (See Below)
Has Field Names is Yes
Range - leave it blank
The FileName issue.
The problem here is that Macros are not terribly smart. If you were always
going to export to the same file, you would just put in the full path an file
name you want the spreadsheet to go to. Because you want it to be different
each time, we need a way to pass that information to it. Macros can
understand functions, so we need a function that will create the string that
has the full path, filename(based on the date), and the file extension. Here
is a version you can use that will make it the date into yyyymmdd format,
which, for today would be 20061116. I use that format, because it will sort
in date order when you are browsing your folder.
Public Function GetTheFileName() As String
GetTheFileName = "c:\documents and settings\frodo\my documents\" & _
Format(date, "yyyymmdd") & ".xls"
End Function
(make sure you use a real path, not the same here)
To use it, copy the text into a standard module and save it. then in the
File Name box in the macro type this (exactly)
=GetTheFileName()
Now, when the Macro runs, it will call the function which will create and
pass back the file name which will then be used by the TransferSpreadsheet
Action.
(And all the munchkins in the Emerald City are happy)
