Save file with creation date


I've added row 23-27 to my code to save the active sheet with the created date and time  stamp from the file that was used to import sheet one. It's bugging out on row 27. The file will save if I "Dim asof As Long" but the result is not what I require.

Sub ImportSheet()

        Dim fileName
        Dim wb As Workbook
        Dim strPath As String
        Dim fs, f
        Dim asof As String

        fileName = Application.GetOpenFilename("Other Workbook (*.xl*),*.xl*")
        If fileName = "False" Then
        MsgBox "You have not selected a file. Please try again."
        GoTo QuitSub
        End If
        Set wb = Workbooks.Open(fileName:=fileName)
        With ThisWorkbook
            wb.Worksheets(1).Copy After:=.Worksheets(.Worksheets.Count)
            wb.Close False
        End With
        Set fs = CreateObject("Scripting.FileSystemObject")
        Set f = fs.GetFile(fileName)
        asof = f.DateCreated
        strPath = ThisWorkbook.Path
        ActiveWorkbook.SaveAs strPath & Application.PathSeparator & "Data as of " & asof
End Sub

Open in new window

Who is Participating?
redmondbConnect With a Mentor Commented:

Replace the "SaveAs" line by...
ActiveWorkbook.SaveAs strPath & Application.PathSeparator & "Data as of " & WorksheetFunction.Substitute(WorksheetFunction.Substitute(asof, "/", "-"), ":", "-")

Martin LissOlder than dirtCommented:
What do you get and what do you want?

You could try changing asof to Date, or if you want today's date just do

ActiveWorkbook.SaveAs strPath & Application.PathSeparator & "Data as of " & Now()
sq30Author Commented:
I want the creation date and time stamp of the file I used to import data from.
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Martin LissOlder than dirtCommented:
And what do you get instead?
sq30Author Commented:
An error have you read the question?
sq30Author Commented:
Thank you Brian.
Thanks, sq30.

Better is...
ActiveWorkbook.SaveAs strPath & Application.PathSeparator & "Data as of " & Replace(Replace(asof, "/", "-"), ":", "-")

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.