Link to home
Start Free TrialLog in
Avatar of sq30
sq30

asked on

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

Avatar of Martin Liss
Martin Liss
Flag of United States of America image

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()
Avatar of sq30
sq30

ASKER

I want the creation date and time stamp of the file I used to import data from.
And what do you get instead?
Avatar of sq30

ASKER

An error have you read the question?
ASKER CERTIFIED SOLUTION
Avatar of redmondb
redmondb
Flag of Afghanistan image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of sq30

ASKER

Thank you Brian.
Thanks, sq30.

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

Brian.