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.
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
ASKER
I want the creation date and time stamp of the file I used to import data from.
And what do you get instead?
ASKER
An error have you read the question?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you Brian.
Thanks, sq30.
Better is...
ActiveWorkbook.SaveAs strPath & Application.PathSeparator & "Data as of " & Replace(Replace(asof, "/", "-"), ":", "-")
Brian.
Better is...
ActiveWorkbook.SaveAs strPath & Application.PathSeparator & "Data as of " & Replace(Replace(asof, "/", "-"), ":", "-")
Brian.
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()