Save file with creation date

Hello,

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
            .Activate
            .Worksheets(.Worksheets.Count).Select
        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
        
        
QuitSub:
End Sub

Open in new window

sq30Asked:
Who is Participating?
 
redmondbConnect With a Mentor Commented:
sq30,

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

Brian.
0
 
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()
0
 
sq30Author Commented:
I want the creation date and time stamp of the file I used to import data from.
0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

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

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

Brian.
0
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.