PDA

View Full Version : Insert The Created Time And Last Modified Time In Cells In Excel With VBA Code



Ladyj205
06-26-2018, 02:09 PM
I have a win.7/MS Office 2010

I'm trying to get a file creation date on a footer on my sheet but i dont want it on my sheet.

I tried using the example off this site : https://www.extendoffice.com/documents/excel/954-excel-created-last-modified-time.html
I'm not allowed to download that site on my computer. (dont want to get in trouble)

Paul_Hossler
06-26-2018, 02:32 PM
To be clear, you want the file Creation Date, not the Last Modified Date ... correct?





Option Explicit

Sub InsertCreationDate()
Dim D As Date

D = CreateObject("Scripting.FileSystemObject").GetFile(ThisWorkbook.FullName).DateCreated

MsgBox D

'the Date, etc. can get formatted: font bold point, etc
ActiveSheet.PageSetup.LeftFooter = "&""Arial,Bold""&12 " & Format(D, "yyyy-mm-dd hh:mm")

End Sub

Ladyj205
06-26-2018, 03:23 PM
You are correct, just the creation date. Do I have to fill in or replace anything in the vba area or does it automatically show up

Paul_Hossler
06-26-2018, 04:32 PM
You are correct, just the creation date. Do I have to fill in or replace anything in the vba area or does it automatically show up

All depends on what you want

The macro just shows the creation date being added to the left footer for the active sheet with a manually run macro

You could add the code to the Before_Print event and you could also have the creation date added to any or all worksheets

You could add text or formatting to this left footer or to the center and right footers



Edit --

Another way - I just had the first FSO code handy, but I should have thought of this first




Sub InsertCreationDate_2()

ActiveSheet.PageSetup.LeftFooter = "&""Arial,Bold""&12 " & Format(ThisWorkbook.BuiltinDocumentProperties("Creation Date"), "yyyy-mm-dd hh:mm")

End Sub

Ladyj205
06-27-2018, 06:57 AM
how about if i want creation date from a previous workbook?

Paul_Hossler
06-27-2018, 07:31 AM
how about if i want creation date from a previous workbook?


Then I'd use the FSO approach from #2 with the workbook of interest




CreateObject("Scripting.FileSystemObject").GetFile("C:\Users\Me\Documents\MyWorkbook.xlsx").DateCreated

Ladyj205
06-27-2018, 01:47 PM
i got a error message. when i try to do it from my T: :(

Paul_Hossler
06-27-2018, 03:33 PM
There's lots of error messages - which one?

I don't understand what a T: is

What is your whole macro?

These work for me




Option Explicit


'this adds the creation date for THIS workbook to the left footer on the active sheet in THIS workbook
Sub InsertCreationDate_2()
ActiveSheet.PageSetup.LeftFooter = "&""Arial,Bold""&12 " & Format(ThisWorkbook.BuiltinDocumentProperties("Creation Date"), "yyyy-mm-dd hh:mm")
End Sub



'this adds the creation date for a SAVED workbook to the left footer on the active sheet in THIS workbook
Sub InsertCreationDate()
Dim D As Date

D = CreateObject("Scripting.FileSystemObject").GetFile("C:\Users\Daddy\Projects\Chemistry\ChemHelper_20.xlsm").DateCreated

'the Date, etc. can get formatted: font bold point, etc
ActiveSheet.PageSetup.LeftFooter = "&""Arial,Bold""&12 " & Format(D, "yyyy-mm-dd hh:mm")
End Sub

Ladyj205
06-28-2018, 06:16 AM
Run-time error '53': file not found....i had pressed f5

Paul_Hossler
06-28-2018, 06:26 AM
If you put your own file name in it should not be not found

Ladyj205
06-28-2018, 08:39 AM
T: is a drive i have on my computer thats all. sorry for the confusion.