Add Date to Output Table File Name

[Jeff]

How can I add the current date to the name of an output
file?

DoCmd.OutputTo
acOutputTable, "CustDetailsTable", "Microsoft
Excel", "G:/CustDetailsTable withCurrentDate", No

Also, when I add "Microsoft Excel" as the output format(as
detailed above), I receive the following error:

The formats that enable you to output data as a Microsoft
Excel, rich-text format, MS-DOS text, or HTML file are
missing from the Windows Registry.

However, if I leave the format blank, the user is prompted
to choose which file format and Excel format works, if
chosen.

Thank you in advance for your help!!!
Jeff

 

[Dirk Goldgar]

How can I add the current date to the name of an output
file?

DoCmd.OutputTo
acOutputTable, "CustDetailsTable", "Microsoft
Excel", "G:/CustDetailsTable withCurrentDate", No

Also, when I add "Microsoft Excel" as the output format(as
detailed above), I receive the following error:

The formats that enable you to output data as a Microsoft
Excel, rich-text format, MS-DOS text, or HTML file are
missing from the Windows Registry.

However, if I leave the format blank, the user is prompted
to choose which file format and Excel format works, if
chosen.

Thank you in advance for your help!!!
Jeff

Try this, or something like it:

DoCmd.OutputTo acOutputTable, "Table1", acFormatXLS, _
"G:\CustDetailsTable_" & _
Format(Date, "mmddyyyy") & _
".xls", _
False

As for the error message, make sure that the file doesn't contain any
illegal characters (such as the slash character), and also make sure the
path exists. See this KB article:

http://support.microsoft.com/default.aspx?scid=kb;en-us;226526

 

[John Nurick]

Hi Jeff,

Use the Format()and Date() functions to get the date.

Usually it's preferable to use TransferSpreadsheet instead of OutputTo
(especially if you may need to export more than 16384 records), e.g.:


Dim strFileSpec as String

strFileSpec = "G:\folder\CustDetailsTable " _
& Format(Date(), "yyyymmdd") & ".xls"

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
"CustDetailsTable", strFileSpec, True

For the file format issue, try re-registering the following files:
msexcl40.dll (Excel)
msxbse40.dll (dBase)
mspdox40.dll (Paradox)
mstext40.dll (Text, CSV, tab-delimited))
In Access 97, the filenames contain 35 instead of 40.

To re-register a DLL file,

1) Locate it (usually all these are in the Windows\System32 or
WINNT\System32 folder.

2) Locate the file regsvr32.exe.

3) Drag the DLL file's icon onto the regsvr32.exe icon.