ExportWithFormatting with current date in file name (1 Viewer)

dbshaw

New member
Local time
Yesterday, 19:04
Joined
Mar 23, 2012
Messages
1
I am running a Macro: ExportWithFormatting to export report data to an excel spreadsheet. I do not want the new file to overwrite the previously created excel files when the macro is run each time. Nor do I want to be asked if I want to overwrite the existing file. How do I include the current date in the File name as it is saved?

ExportWithFormatting
Object Type: report
Object Name: rptInvoicedToUpload
Output Format: Excel 97 - Excel 2003 Workbook (*.xls)

Output File = [application].[currentproject].[path] & "\Reports\MonthlyExport*****{{{here is where I want current date}}}*** .xls"

Auto Start: Yes
Template File:
Encoding:
Output Quality: Screen

I am STUMPED!!
 

Snowflake68

Registered User.
Local time
Today, 03:04
Joined
May 28, 2014
Messages
420
you just need to add this to the end of the Output File path

& "_" & Format(Date(),"yyyy_mm_dd") & ".xls"

so yours would be something like;
= [application].[currentproject].[path] & "\Reports\MonthlyExport\Filename_" & "_" & Format(Date(),"yyyy_mm_dd") & ".xls"
 

SP_Brandon

New member
Local time
Yesterday, 22:04
Joined
Jul 4, 2020
Messages
4
& "_" & Format(Date(),"yyyymmdd") & ".xlsx" is not working for me!

I tried using DoCmd.OutputTo and DoCmd.TransferSpreadsheet--both of which gave me error messages. Please see below:
  • DoCmd.OutputTo
    • Folder Path and Filename: DoCmd.OutputTo acOutputQuery, "Missing Info", acFormatXLSX, _"NetworkPath\Reports\Missing Info " & Format(Date, "yyyymmdd") & ".xlsx", True
    • Error: Query input must contain at least one table or query. I can't understand why this error is coming up because "Missing Info" is the query name, and it's in the right place on the code.
  • DoCmd.TransferSpreadsheet
    • Folder Path and Filename: DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "Missing Info", _
      "NetworkPath\Reports\Missing Info " & Format(Date, "yyyymmdd") & ".xlsx", True
    • Error: No current record.
Thank you for your assistance! :)
 
Last edited:

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 19:04
Joined
Oct 29, 2018
Messages
13,100
& "_" & Format(Date(),"yyyymmdd") & ".xlsx" is not working for me!

I tried using DoCmd.OutputTo and DoCmd.TransferSpreadsheet--both of which gave me error messages. Please see below:
  • DoCmd.OutputTo
    • Folder path and filename: DoCmd.OutputTo acOutputQuery, "Missing Info", acFormatXLSX, _"\\10.1.0.7\Departments\Implementation\Implementation Client Tracking List\Reports\Missing Info " & Format(Date, "yyyymmdd") & ".xlsx", True
Hi. Welcome to AWF!

This is an old thread. What error message were you getting?
 

SP_Brandon

New member
Local time
Yesterday, 22:04
Joined
Jul 4, 2020
Messages
4
Hi. Welcome to AWF!

This is an old thread. What error message were you getting?

Hi! Thanks for the welcome! Yes, I noticed it's quite old indeed! :)

& "_" & Format(Date(),"yyyymmdd") & ".xlsx" is not working for me!

I tried using DoCmd.OutputTo and DoCmd.TransferSpreadsheet--both of which gave me error messages. Please see below:
  • DoCmd.OutputTo
    • Folder Path and Filename: DoCmd.OutputTo acOutputQuery, "Missing Info", acFormatXLSX, _"NetworkPath\Reports\Missing Info " & Format(Date, "yyyymmdd") & ".xlsx", True
    • Error: Query input must contain at least one table or query. I can't understand why this error is coming up because "Missing Info" is the query name, and it's in the right place on the code.
  • DoCmd.TransferSpreadsheet
    • Folder Path and Filename: DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "Missing Info", _
      "NetworkPath\Reports\Missing Info " & Format(Date, "yyyymmdd") & ".xlsx", True
    • Error: No current record.
Thank you for your assistance! :)
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 19:04
Joined
Oct 29, 2018
Messages
13,100
Hi! Thanks for the welcome! Yes, I noticed it's quite old indeed! :)

& "_" & Format(Date(),"yyyymmdd") & ".xlsx" is not working for me!

I tried using DoCmd.OutputTo and DoCmd.TransferSpreadsheet--both of which gave me error messages. Please see below:
  • DoCmd.OutputTo
    • Folder Path and Filename: DoCmd.OutputTo acOutputQuery, "Missing Info", acFormatXLSX, _"NetworkPath\Reports\Missing Info " & Format(Date, "yyyymmdd") & ".xlsx", True
    • Error: Query input must contain at least one table or query. I can't understand why this error is coming up because "Missing Info" is the query name, and it's in the right place on the code.
  • DoCmd.TransferSpreadsheet
    • Folder Path and Filename: DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "Missing Info", _
      "NetworkPath\Reports\Missing Info " & Format(Date, "yyyymmdd") & ".xlsx", True
    • Error: No current record.
Thank you for your assistance! :)
Hi. What is the SQL statement for your query?
 

Micron

AWF VIP
Local time
Yesterday, 22:04
Joined
Oct 20, 2018
Messages
3,471
Both of those error messages are suggesting that the query produces no output? Maybe test that first so you can report that back as well.
 

Rene vK

Member
Local time
Today, 04:04
Joined
Mar 3, 2013
Messages
46
Stupid idea (maybe?): .xls instead of .xlsx ??
 

Users who are viewing this thread

Top Bottom