ExportWithFormatting with current date in file name

D

dbshaw

New member
Local time
Yesterday, 18:03
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!!
 
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"
 
& "_" & 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:
SP_Brandon said:
& "_" & 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
Click to expand...
Hi. Welcome to AWF!

This is an old thread. What error message were you getting?
 
theDBguy said:
Hi. Welcome to AWF!

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

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! :)
 
SP_Brandon said:
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! :)
Click to expand...
Hi. What is the SQL statement for your query?
 
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.
 
Stupid idea (maybe?): .xls instead of .xlsx ??
 
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!
 
Please post your full current code
 
mistyinca1970 said:
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!
Click to expand...
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
 
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
 
Gasman said:
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
Click to expand...
worked restart my PC.... IDK what happened there thank you
 

Attachments

  • Capture.PNG
    Capture.PNG
    2.4 KB · Views: 318
Last edited:
You must log in or register to reply here.

Similar threads

D
Solved Command button on Report to export to excel
Replies
8
Views
196
Eugene-LS
Eugene-LS
Y
Export to excel with Date format
Replies
17
Views
2,086
CJ_London
C
DickyP
Solved Allowable file types for import
Replies
18
Views
949
Jon
Jon
P
VBA code with Date() function not working in runtime mode
Replies
9
Views
200
Pat Hartman
P
T
Export report in Desktop with account name
Replies
2
Views
318
Pat Hartman
P

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom