ExportWithFormatting with current date in file name (1 Viewer)

dbshaw

New member
Local time
Today, 11:31
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, 18:31
Joined
May 28, 2014
Messages
452
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
Today, 14:31
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
Today, 11:31
Joined
Oct 29, 2018
Messages
21,358
& "_" & 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
Today, 14:31
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
Today, 11:31
Joined
Oct 29, 2018
Messages
21,358
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
Today, 14:31
Joined
Oct 20, 2018
Messages
3,476
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, 19:31
Joined
Mar 3, 2013
Messages
123
Stupid idea (maybe?): .xls instead of .xlsx ??
 

mistyinca1970

Member
Local time
Today, 11:31
Joined
Mar 17, 2021
Messages
117
Hi, I am aware this is an old thread, but in the interest of not starting a new one when not necessary, I have a question about this. I have successfully used the suggestion in this thread to append the date:
Code:
& "_" & Format(Date(),"yyyy_mm_dd") & ".xlsx"
How can I specify a default path. It looks like this is automatically saving in my c:\documents.

Thanks!
 

Isaac

Lifelong Learner
Local time
Today, 11:31
Joined
Mar 14, 2017
Messages
8,738
Please post your full current code
 

NahualXl

New member
Local time
Today, 14:31
Joined
Sep 23, 2021
Messages
19
Hi, I am aware this is an old thread, but in the interest of not starting a new one when not necessary, I have a question about this. I have successfully used the suggestion in this thread to append the date:
Code:
& "_" & Format(Date(),"yyyy_mm_dd") & ".xlsx"
How can I specify a default path. It looks like this is automatically saving in my c:\documents.

Thanks!
Code:
sSource = Application.CurrentProject.Path & "\"   ' "E:\OP60\Archive\"
Dim sFilename As String
    sFilename = sSource & "Test data report" & "-" & result & "_" & Format(Date, "yyyy_mm_dd") & ".xlsx"
    DoCmd.OutputTo acOutputQuery, "QryLog", acFormatXLSX, sFilename, Autostart:=False

Note: Access 2016 for some reason wipes out the parenthesis as soon as I type/paste the code from the Date()
this is the output file:
Test data report-_.xlsx
Any ideas as to how to fix this?
Thank you
 

Gasman

Enthusiastic Amateur
Local time
Today, 18:31
Joined
Sep 21, 2011
Messages
14,046
Works for me?
From the immediate window.
Code:
? format(date,"yyyy-mm-dd")
2021-10-14
Code:
? sSource & "Test data report" & "-" & result & "_" & Format(Date, "yyyy_mm_dd") & ".xlsx"
Test data report-_2021_10_14.xlsx
 

NahualXl

New member
Local time
Today, 14:31
Joined
Sep 23, 2021
Messages
19
Works for me?
From the immediate window.
Code:
? format(date,"yyyy-mm-dd")
2021-10-14
Code:
? sSource & "Test data report" & "-" & result & "_" & Format(Date, "yyyy_mm_dd") & ".xlsx"
Test data report-_2021_10_14.xlsx
worked restart my PC.... IDK what happened there thank you
 

Attachments

  • Capture.PNG
    Capture.PNG
    2.4 KB · Views: 290
Last edited:

Users who are viewing this thread

Top Bottom