UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Custom Export File Name, Access 2016    
 
   
meyert
post Feb 7 2019, 06:33 PM
Post#1



Posts: 137
Joined: 22-January 08



Hello,

I have a file that exports data - 2 to 3 times a day. Exporting to excel is my preference

Have the reports exported is easy enough, but is there a way to have the files automatically named differently every time? I am picturing the report name with the date and time of export as part of the file name

then we would not have to manually rename each file

Does that make sense?
Go to the top of the page
 
MadPiet
post Feb 7 2019, 06:41 PM
Post#2



Posts: 2,804
Joined: 27-February 09



You could declare a string variable and then use something along the lines of...

?Format(now(),"mmm-dd-yyyy_hh-nn")
Feb-07-2019_17-39

Of course, you'd have to put all the other pieces in there, but that should give you some idea.
If you want just the date (and not the time), use DATE() instead and change the format string (remove the hhnn stuff)
Go to the top of the page
 
WildBird
post Feb 7 2019, 07:01 PM
Post#3


UtterAccess VIP
Posts: 3,500
Joined: 19-August 03
From: Auckland, Little Australia


I do this all the time, I use format(now(),"yyyymmddhhnnss") & "ReportName.xlsm"

Having it in this format means you can sort it chronologically.

--------------------
Beer, natures brain defragging tool.
Go to the top of the page
 
meyert
post Feb 8 2019, 05:47 AM
Post#4



Posts: 137
Joined: 22-January 08



Thank you so much for your replies. This is good news

Unfortunately, I don't know exactly how to apply what you are telling me.

I have a macro that exports the spreadsheets, but I don't know how to apply that format you reference

Where do I put that?
Go to the top of the page
 
PaulBrand
post Feb 8 2019, 05:52 AM
Post#5



Posts: 1,670
Joined: 4-September 02
From: Oxford UK


Can you post your existing code?

--------------------
Paul
Go to the top of the page
 
R_Durrer
post Feb 8 2019, 02:03 PM
Post#6



Posts: 96
Joined: 19-April 16
From: Guelph, ON, Canada


Do you have any experience with VBA or do you just do Macros?
Go to the top of the page
 
meyert
post Feb 8 2019, 07:39 PM
Post#7



Posts: 137
Joined: 22-January 08



I have some experience with code - but it is something that I am still muddling through.

This particular export is currently in a macro so I don't have any code for it at this time

I think I can try to convert the macro to code and then share it. But it won't be tonight I am tired smile.gif
Go to the top of the page
 
meyert
post Feb 9 2019, 10:09 AM
Post#8



Posts: 137
Joined: 22-January 08



I converted my macro and pasted the code below. Please help me know where I need to add that formatting bit that you mentioned THANK YOU


'------------------------------------------------------------
' export_notes_in_spreadsheet
'
'------------------------------------------------------------
Function export_notes_in_spreadsheet()
On Error GoTo export_notes_in_spreadsheet_Err

DoCmd.OutputTo acOutputQuery, "EXPORT Comments on Open PO's for back up", "ExcelWorkbook(*.xlsx)", "H:\PDEPLOY\MMS Database\NOTES ON ORDERS FROM DATABASE\notes on orders from spreadsheet.xlsx", False, "", , acExportQualityPrint
DoCmd.OutputTo acOutputQuery, "Item Information for export", "ExcelWorkbook(*.xlsx)", "H:\PDEPLOY\MMS Database\NOTES ON ORDERS FROM DATABASE\notes on items from database.xlsx", False, "", , acExportQualityPrint
DoCmd.OutputTo acOutputTable, "Suppliers", "ExcelWorkbook(*.xlsx)", "H:\PDEPLOY\MMS Database\NOTES ON ORDERS FROM DATABASE\supplier notes from database.xlsx", False, "", , acExportQualityPrint


export_notes_in_spreadsheet_Exit:
Exit Function

export_notes_in_spreadsheet_Err:
MsgBox Error$
Resume export_notes_in_spreadsheet_Exit

End Function
Go to the top of the page
 
tina t
post Feb 9 2019, 03:02 PM
Post#9



Posts: 5,740
Joined: 11-November 10
From: SoCal, USA


QUOTE
DoCmd.OutputTo acOutputQuery, "EXPORT Comments on Open PO's for back up", "ExcelWorkbook(*.xlsx)", "H:\PDEPLOY\MMS Database\NOTES ON ORDERS FROM DATABASE\notes on orders from spreadsheet.xlsx", False, "", , acExportQualityPrint

well, the blue text above is the filepath and name that you're outputting the data to, correct? so add the following to your code (Format taken from MadPiet's post), as

CODE
Dim strFileName As String

strFileName = Format(now(),"mmm-dd-yyyy_hh-nn_") & "notes on orders from spreadsheet.xlsx"

or use WildBird's

Format(now(),"yyyymmddhhnnss")

or use whatever format you want. then change the path/name reference in the output code, as

DoCmd.OutputTo acOutputQuery, "EXPORT Comments on Open PO's for back up", "ExcelWorkbook(*.xlsx)", "H:\PDEPLOY\MMS Database\NOTES ON ORDERS FROM DATABASE\" & strFileName, False, "", , acExportQualityPrint

hth
tina
This post has been edited by tina t: Feb 9 2019, 03:05 PM

--------------------
"the wheel never stops turning"
Go to the top of the page
 
meyert
post Feb 9 2019, 08:57 PM
Post#10



Posts: 137
Joined: 22-January 08



THANK YOU!

I got one of them to work smile.gif I should be able to figure out the others now

I truly appreciate your help
Go to the top of the page
 
tina t
post Feb 10 2019, 12:49 PM
Post#11



Posts: 5,740
Joined: 11-November 10
From: SoCal, USA


you're welcome, everyone was happy to help. tina

--------------------
"the wheel never stops turning"
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    16th February 2019 - 03:02 AM