Getting the right date format when exporting a file

[MTSPEER]

Hello,

I want to get the name of a file like Example_08302017 1:40PM.xlsx for example.

I tried the code:

Format(Date, mmddyyyy hh:mm AM/PM) & .xlsx but it wouldn't work.

Any suggestions? THanks

[ranman256]

you need the quotes in the format:
Format(Date, "mmddyyyy hh:mm AM/PM")

[MTSPEER]

it's not working. it is creating the file Example_08302017 12 and the file is blank

[aytee111]

What does "not working" mean?? You say it IS creating the file? Please be more specific as to your question(s).

[MTSPEER]

idk what the file type is..It's not in excel format

[JoeM]

Please post your complete VBA code that is exporting the file.

[aytee111]

Is this right
& .xlsx
?
s'be
& ".xlsx"

[Missinglinq]

Date() will always return 12:00 Am as the Time portion...for the Date and Time, which is what you apparently want, you'd need to use Now().

Format(Now, "mmddyyyy hh:mm AM/PM")

and with aytee111's contribution

Format(Now, "mmddyyyy hh:mm AM/PM") & ".xlsx"

although I suspect the spaces in the name will be problematical. Probably better to use

Format(Now, "mmddyyyy_hh:mm_AM/PM") & ".xlsx"

Linq ;0)>

[MTSPEER]

I am still getting the same results. Here is my Code:

Public Function ExportImportTemplate()
Dim strFilePath As String
strFilePath = "RESOURCE FOLDER\ASCEND\Import_Users_Report\ImportUsers_"
strFilePath = strFilePath & Format(Now, "mmddyyyy_hh:mm_AM/PM") & ".xlsx"

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "Import_Template", strFilePath, True

MsgBox "The Import Users Report has been exported to the Import_Users_Report Folder!", vbOKOnly
Here is the picture of the output file

[aytee111]

Take these issues one at a time.

1. File name, add this line of code:
Debug.Print Format(Now, "mmddyyyy_hh:mm_AM/PM")
see what it is displaying

2. No data in file:
run the query, make sure it is producing data

3. xlsx - there are known issues with exporting to xlsx. Change it to xls and use excel9 instead.

[MTSPEER]

1. It shows the correct time when I run Debug.Print

I changed it to .xls and acSpreadsheetTypeExcel9 and got the same results

[aytee111]

Post your database - with one table (the results of your query), and this code.

[MTSPEER]

How do I attach a DB here?

[aytee111]

Click on Go Advanced, then the Attach icon

[MTSPEER]

DateFormat.accdb

Here is the DB