Save with original file created date AND time

Question

I've a folder with thousands of .txt files I'm delimiting and creating xlsx files. I need to save the new xlsx files with date AND time (even if it's just the hour) into the file name.

Reason is, each day three files are saved at random intervals and I'll need to identify the files that were the last ones saved each day whilst I'm converting these ugly notepad files into excel workbooks.

Any help welcomed!

Sub LoopAllFiles()
    Dim sPath As String, sDir As String
    Dim sLoc As String

    
    sPath = "C:\Users\MyUserName\OneDrive\DailySnapshots\"
    sLoc = "C:\Users\MyUser\OneDrive\Repository\"
    
    If Right(sPath, 1) <> "\" Then sPath = sPath & "\"
    sDir = Dir$(sPath & "*.txt", vbNormal)
    Do Until Len(sDir) = 0
        Workbooks.Open (sPath & sDir)
        With ActiveWorkbook
        
            Columns("A:A").TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
                TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=False, _
                Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
                :="|", FieldInfo:=Array(1, 1), TrailingMinusNumbers:=True
            .SaveAs Filename:=sLoc & Left(.Name, InStrRev(.Name, ".")) & "xlsx", FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
            .Close
        End With
        sDir = Dir$
    Loop
End Sub

I'm unable to come up with a solution that pulls the .txt file properties to get date and time of initial creation. Tried adding in BuiltinDocumentProperties("Creation Date") But it's bugging out with "Run-time error '-2147467259 (80004005)': Automation error Unspecified error"

Sub LoopAllFiles()
    Dim sPath As String, sDir As String
    Dim sLoc As String
    Dim creationDate As Date
    
    sPath = "C:\Users\MyUserName\OneDrive\DailySnapshots\"
    sLoc = "C:\Users\MyUser\OneDrive\Repository\"
    
    If Right(sPath, 1) <> "\" Then sPath = sPath & "\"
    sDir = Dir$(sPath & "*.txt", vbNormal)
    Do Until Len(sDir) = 0
        Workbooks.Open (sPath & sDir)
        With ActiveWorkbook
        
            Columns("A:A").TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
                TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=False, _
                Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
                :="|", FieldInfo:=Array(1, 1), TrailingMinusNumbers:=True

creationDate = ActiveWorkbook.BuiltinDocumentProperties("Creation Date")

            .SaveAs Filename:=sLoc & Left(.Name, 2) & creationDate & "xlsx", FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
            .Close
        End With
        sDir = Dir$
    Loop
End Sub

and then using creationDate in the file name but it's just throwing constant errors.

Do you mean the date the excel file was created or the notepad file? — Nathan_Sav
– Nathan_Sav, Commented Oct 14 at 8:48
What value is stored in creationDate? For example 14/10/2025 18:20:30 — motosann
– motosann, Commented Oct 14 at 9:23
Does it contain any characters that cannot be used in a file name? — motosann
– motosann, Commented Oct 14 at 9:30
@Nathan_Sav the notepad file created date and time (which is the file that's opened into xlsx format via the macro, so hoping it's still technically that file that's opened.) — JC-678
– JC-678, Commented Oct 14 at 10:02

FunThomas · Accepted Answer · 2025-10-14 11:16:27Z

4

A text file doesn't have BuiltinDocumentProperties - those are created only when you save the file as Excel file. Which is too late for you, plus it will set the date to the current date (as this is the moment the Excel file is created).

You could try one of the 2 methods:

Dim fileName As String, creationDate As Date
fileName = sPath & sDir

' Use FileSystemObject (will not work on a Mac)
With CreateObject("Scripting.FileSystemObject")
    creationDate = .getFile(fileName).DateCreated
End With

Or

' Use the (really old) VBA method:
creationDate = FileDateTime(fileName)

The date format doesn't play a role, that's only how a date is displayed - a date is a date no matter how it is displayed on your computer. But to use the date in a file name, you have to convert it into a string. In VBA, you use the command Format for that, for example

Dim excelFileName As String
excelFileName = sLoc & Left(.Name, 2) & Format(creationDate, "_yyyymmdd_hhnn")

You can change that format however you want, but note that certain characters are not allowed in a file name, for example the slash character / (my advice would be to stick to the format I suggested).

answered Oct 14 at 11:16

FunThomas

31.3k4 gold badges24 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

JC-678 Oct 14 at 14:45

Not using a mac, but the FileSystemObject didn't work, however the really old method worked perfectly thank you! Now looping through thousands of really randomly named files, delimiting them and saving with their appropriate dates for some snapshot analysis! Thank you so much again :)

Tim Williams Oct 15 at 1:00

FileSystemObject works fine for me (also not on a Mac)

motosann · Accepted Answer · 2025-10-14 09:45:10Z

0

[ActiveWorkbook.BuiltinDocumentProperties("Creation Date")] is a date type. It needs to be converted to a string. Do it as follows.

Format(ActiveWorkbook.BuiltinDocumentProperties("Creation Date"), "yyyymmdd_hhnnss")

I hope this is helpful.

answered Oct 14 at 9:45

motosann

93 bronze badges

1 Comment

JC-678 Oct 14 at 10:03

Thank you for this, was a good shout but sadly still throws the same error :( The files are in sharepoint and the date format is indeed dd/mm/yyyy hh:mm so i'm gutted that didn't work.

Collectives™ on Stack Overflow

Save with original file created date AND time

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related