Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Chris Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Display last saved date in cell in Excel 8
- Thread starter nigelpull
- Start date
-
6
- #2
DaleWatson123321
Programmer
This will work...
Sub Get_SaveData()
curfile = Application.ActiveWorkbook.Name
SaveData = FileDateTime(curfile)
ActiveCell.Value = SaveData
End Sub
I hope this helps.
Please advise as to how it works.
Regards, ...Dale Watson dwatson@bsi.gov.mb.ca
Sub Get_SaveData()
curfile = Application.ActiveWorkbook.Name
SaveData = FileDateTime(curfile)
ActiveCell.Value = SaveData
End Sub
I hope this helps.
Please advise as to how it works.Regards, ...Dale Watson dwatson@bsi.gov.mb.ca
- Thread starter
- #3
I still need help where do I enter:
Sub Get_SaveData()
curfile = Application.ActiveWorkbook.Name
SaveData = FileDateTime(curfile)
ActiveCell.Value = SaveData
End Sub
How do I reference it in a cell?
Does it update automatically?
Can you send me an example, please?
I have never used VBA.
Sub Get_SaveData()
curfile = Application.ActiveWorkbook.Name
SaveData = FileDateTime(curfile)
ActiveCell.Value = SaveData
End Sub
How do I reference it in a cell?
Does it update automatically?
Can you send me an example, please?
I have never used VBA.
DaleWatson123321
Programmer
nigelpull,
I'm answering this from home, so if you've emailed me at my work address, I'll send you an example file in the morning.
In the meantime, to get you started, here's what to do:
1) To open the "Macro" window, hold down the <Alt> key and hit the <F8> key.
2) At the top, under "Macro Name", type any characters - e.g.: "asdf".
3) Hold down <Alt> and hit "C" (or click "Create" - on the right side of the Macro window).
You should now be in the "Visual Basic Editor" window. Another method of going to the "VB Editor" window, is to hold down the <Alt> key and hit <F11>.
The reason for referring you to the <Alt> <F8> option first, is because by using that method, it will automatically create a "Module" for you. These Modules are sort of like separate "folders" where you can place various VBA (Visual Basic for Applications) code or "routines".
Normally, once a user has created a LARGER number of various subroutines (the example I posted earlier is a "subroutine", then it can be advantageous to "organize" your various subroutines into these "folder-like" Modules. But for starters, only be concerned about using the one Module - named Module1.
Be aware, however, that when you "record" a macro, the macro will normally be placed by Excel into a separate Module - e.g. Module2.
Another reason for pointing you to the <Alt> <F8> option, is because it is here that you can click on "Options" - and be able to assign your macro to a "keyboard shortcut". This means that you'll be able to activate your macro by, for example, holding down the <Control> key and hitting the letter you specify - e.g. "Q". Naturally, you should AVOID using letters that are normally used for regular shortcuts such as <Control> B for Bold, <Control> S for Save, <Control> C for Copy, <Control> X for Cut, <Control> V for Paste.
Now back to the "steps"...
5) Highlight the routine I previously posted - here it is again, so highlight the following, and "Copy" it using <Control> C.
Sub Get_SaveData()
curfile = Application.ActiveWorkbook.Name
SaveData = FileDateTime(curfile)
ActiveCell.Value = SaveData
End Sub
6) At this point, you should ALSO have your Excel window open, and therefore use: <Alt> <Tab> to go from this Tek-Tips window to your Excel window.
Note: If you happen to have multiple windows open, you might have to "cycle" through your open windows until you come to the right one. To do this, you "keep" holding down the <Alt> key, while hitting the <Tab> key until you come to the proper window. Then release the <Alt> key.
7) Highlight the "temporary" subroutine that you initially created, and "Paste", using <Control> V.
8) To leave the "VB Editor" window, use <Alt> Q.
9) Now assign a keyboard shortcut to the macro you've just created. Do this by starting with: <Alt> <F8>.
10) Now use <Alt> O to choose "Options", and then under where it says "Shortcut key:", enter the letter "q". Note that this is a lowercase "q". If you enter a (capital) "Q", then Excel will show you "Ctrl+Shift" to the left of the "Q". This means that to activate your macro, you'll then need to hold down BOTH the <Control> key and the <Shift> key while you hit the letter "Q".
11) Click "OK" (or <Enter>) to exit the "Macro Options" window.
12) To exit from the "Macro" window, click the "X" in the top-right-corner of the window.
You are (finally) ready to run your macro.
The macro is written such that the Date and Time Last Saved will be placed into the "Active" cell - meaning whatever cell your cursor is on.
Therefore place your cursor on the cell where you want the Date/Time to be entered, and THEN activate your macro - by holding down the <Control> key and hitting "Q". (I show a "capital" Q because that's how it's displayed on the keyboard.)
If the column width is not wide enough, you will get ######## in the cell. This of course means the cell width needs to be made wider to display the Date and Time.
A final option...
If you should want to place the Date/Time Last Saved into a "specific" cell - separate from where your cursor is located, then you should modify the one line of the code. Here's the "modified" routine...
Sub Get_SaveData()
curfile = Application.ActiveWorkbook.Name
SaveData = FileDateTime(curfile)
Range("dtsaved".Value = SaveData
End Sub
It is IMPORTANT to note that the reference to "dtsaved" above, refers to a cell that has been assigned this name as a "range name". You can assign any name of your choosing, so long as you change the name in your VBA code to match.
Method of Assigning a Range Name:
a) Highlight a cell or range-of-cells
b) Hold down the <Control> key and hit <F3>
c) Type the name
d) Hit <Enter>
Before someone "accuses me of writing a book", I'll end this, and hope that you have been able to follow along.
I hope it's helped. Please advise as to how you make out.
Regards, ...Dale Watson dwatson@bsi.gov.mb.ca
I'm answering this from home, so if you've emailed me at my work address, I'll send you an example file in the morning.
In the meantime, to get you started, here's what to do:
1) To open the "Macro" window, hold down the <Alt> key and hit the <F8> key.
2) At the top, under "Macro Name", type any characters - e.g.: "asdf".
3) Hold down <Alt> and hit "C" (or click "Create" - on the right side of the Macro window).
You should now be in the "Visual Basic Editor" window. Another method of going to the "VB Editor" window, is to hold down the <Alt> key and hit <F11>.
The reason for referring you to the <Alt> <F8> option first, is because by using that method, it will automatically create a "Module" for you. These Modules are sort of like separate "folders" where you can place various VBA (Visual Basic for Applications) code or "routines".
Normally, once a user has created a LARGER number of various subroutines (the example I posted earlier is a "subroutine"
, then it can be advantageous to "organize" your various subroutines into these "folder-like" Modules. But for starters, only be concerned about using the one Module - named Module1.Be aware, however, that when you "record" a macro, the macro will normally be placed by Excel into a separate Module - e.g. Module2.
Another reason for pointing you to the <Alt> <F8> option, is because it is here that you can click on "Options" - and be able to assign your macro to a "keyboard shortcut". This means that you'll be able to activate your macro by, for example, holding down the <Control> key and hitting the letter you specify - e.g. "Q". Naturally, you should AVOID using letters that are normally used for regular shortcuts such as <Control> B for Bold, <Control> S for Save, <Control> C for Copy, <Control> X for Cut, <Control> V for Paste.
Now back to the "steps"...
5) Highlight the routine I previously posted - here it is again, so highlight the following, and "Copy" it using <Control> C.
Sub Get_SaveData()
curfile = Application.ActiveWorkbook.Name
SaveData = FileDateTime(curfile)
ActiveCell.Value = SaveData
End Sub
6) At this point, you should ALSO have your Excel window open, and therefore use: <Alt> <Tab> to go from this Tek-Tips window to your Excel window.
Note: If you happen to have multiple windows open, you might have to "cycle" through your open windows until you come to the right one. To do this, you "keep" holding down the <Alt> key, while hitting the <Tab> key until you come to the proper window. Then release the <Alt> key.
7) Highlight the "temporary" subroutine that you initially created, and "Paste", using <Control> V.
8) To leave the "VB Editor" window, use <Alt> Q.
9) Now assign a keyboard shortcut to the macro you've just created. Do this by starting with: <Alt> <F8>.
10) Now use <Alt> O to choose "Options", and then under where it says "Shortcut key:", enter the letter "q". Note that this is a lowercase "q". If you enter a (capital) "Q", then Excel will show you "Ctrl+Shift" to the left of the "Q". This means that to activate your macro, you'll then need to hold down BOTH the <Control> key and the <Shift> key while you hit the letter "Q".
11) Click "OK" (or <Enter>) to exit the "Macro Options" window.
12) To exit from the "Macro" window, click the "X" in the top-right-corner of the window.
You are (finally) ready to run your macro.
The macro is written such that the Date and Time Last Saved will be placed into the "Active" cell - meaning whatever cell your cursor is on.
Therefore place your cursor on the cell where you want the Date/Time to be entered, and THEN activate your macro - by holding down the <Control> key and hitting "Q". (I show a "capital" Q because that's how it's displayed on the keyboard.)
If the column width is not wide enough, you will get ######## in the cell. This of course means the cell width needs to be made wider to display the Date and Time.
A final option...
If you should want to place the Date/Time Last Saved into a "specific" cell - separate from where your cursor is located, then you should modify the one line of the code. Here's the "modified" routine...
Sub Get_SaveData()
curfile = Application.ActiveWorkbook.Name
SaveData = FileDateTime(curfile)
Range("dtsaved"
.Value = SaveDataEnd Sub
It is IMPORTANT to note that the reference to "dtsaved" above, refers to a cell that has been assigned this name as a "range name". You can assign any name of your choosing, so long as you change the name in your VBA code to match.
Method of Assigning a Range Name:
a) Highlight a cell or range-of-cells
b) Hold down the <Control> key and hit <F3>
c) Type the name
d) Hit <Enter>
Before someone "accuses me of writing a book", I'll end this, and hope that you have been able to follow along.
I hope it's helped.
Please advise as to how you make out.Regards, ...Dale Watson dwatson@bsi.gov.mb.ca
![[rockband]](/data/assets/smilies/rockband.gif "[rockband] [rockband]")
Rgds- Thread starter
- #6
- Thread starter
- #7
It worked!
I am trying to keep a list of various issues together in a list. This list is changed often and by different individuals. I was hoping to create a SS with a cell at the top that will always display the last time the file was saved, to save some confusion.
Is there a way to invoke a macro to run when the file is opened?
I am trying to keep a list of various issues together in a list. This list is changed often and by different individuals. I was hoping to create a SS with a cell at the top that will always display the last time the file was saved, to save some confusion.
Is there a way to invoke a macro to run when the file is opened?
DaleWatson123321
Programmer
~Geoff~,
Thanks for the complement ;-)
nigelpull,
You asked... "Is there a way to invoke a macro to run when the file is opened?"
You could take the previous routine and use it in a "Workbook_Open" event. There is one addition that should be made. The modified routine below includes two new lines regardin the "path" of the ActiveWorkbook. This will be required in situations where the file previously open was in a DIFFERENT folder.
Sub Get_SaveData()
pth = ActiveWorkbook.Path
ChDir (pth)
curfile = Application.ActiveWorkbook.Name
SaveData = FileDateTime(curfile)
ActiveCell.Value = SaveData
End Sub
HOWEVER, there is a GOOD reason NOT to use the above as a Workbook_Open event. This is because, while the "FileDataTime" function provides the date/time the file was last "accessed", it is ALSO AFFECTED and CHANGED when the file is opened - i.e. the date/time is UPDATED to the date/time when the file is opened.
Therefore, your BEST solution, is to use a Workbook_BeforeSave event... as follows...
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
curtime = Now()
Range("dtsaved".Value = curtime
End Sub
This will update the cell named "dtsaved" whenever the file is saved, and of course this will be an accurate record of the date/time last saved - whenever the file is next opened.
I hope this helps. Please advise as to how you make out.
Regards, ...Dale Watson dwatson@bsi.gov.mb.ca
Thanks for the complement ;-)
nigelpull,
You asked... "Is there a way to invoke a macro to run when the file is opened?"
You could take the previous routine and use it in a "Workbook_Open" event. There is one addition that should be made. The modified routine below includes two new lines regardin the "path" of the ActiveWorkbook. This will be required in situations where the file previously open was in a DIFFERENT folder.
Sub Get_SaveData()
pth = ActiveWorkbook.Path
ChDir (pth)
curfile = Application.ActiveWorkbook.Name
SaveData = FileDateTime(curfile)
ActiveCell.Value = SaveData
End Sub
HOWEVER, there is a GOOD reason NOT to use the above as a Workbook_Open event. This is because, while the "FileDataTime" function provides the date/time the file was last "accessed", it is ALSO AFFECTED and CHANGED when the file is opened - i.e. the date/time is UPDATED to the date/time when the file is opened.
Therefore, your BEST solution, is to use a Workbook_BeforeSave event... as follows...
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
curtime = Now()
Range("dtsaved"
.Value = curtimeEnd Sub
This will update the cell named "dtsaved" whenever the file is saved, and of course this will be an accurate record of the date/time last saved - whenever the file is next opened.
I hope this helps.
Please advise as to how you make out.Regards, ...Dale Watson dwatson@bsi.gov.mb.ca
DaleWatson123321
Programmer
Important Update...
For anyone reading this thread, it's IMPORTANT to recognize the NEED to...
...place "Workbook" events (including a Workbook_BeforeSave or a Workbook_Open routine) in "ThisWorkbook" - instead of in a Module.
Regards, ...Dale Watson dwatson@bsi.gov.mb.ca
For anyone reading this thread, it's IMPORTANT to recognize the NEED to...
...place "Workbook" events (including a Workbook_BeforeSave or a Workbook_Open routine) in "ThisWorkbook" - instead of in a Module.
Regards, ...Dale Watson dwatson@bsi.gov.mb.ca
-
1
- #11
Elizabeth,
This will work for you:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim curtime As String, DTSaved As String
curtime = Now()
DTSaved = curtime
With ActiveSheet.PageSetup
.RightHeader = DTSaved
End With
End Sub
Vr,
Eugene
This will work for you:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim curtime As String, DTSaved As String
curtime = Now()
DTSaved = curtime
With ActiveSheet.PageSetup
.RightHeader = DTSaved
End With
End Sub
Vr,
Eugene
I know this is somewhat off topic as the title of this thread is regarding Excel, but can I get advice on how to accomplish the same thing in a Word document? I guess this would be different as Word has one header section so I'd have to delete the previous date before entering the new.
BTW, I altered my Excel module to:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim curtime As String, DTSaved As String
curtime = Now()
DTSaved = curtime
With ActiveSheet.PageSetup
.RightHeader = "Last saved " & DTSaved
End With
End Sub
So the part of the header to be deleted would always begin with "Last saved" and continue through the end.
BTW, I altered my Excel module to:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim curtime As String, DTSaved As String
curtime = Now()
DTSaved = curtime
With ActiveSheet.PageSetup
.RightHeader = "Last saved " & DTSaved
End With
End Sub
So the part of the header to be deleted would always begin with "Last saved" and continue through the end.
DeLaMartre
Technical User
Dale, you ol' son-of-a-gun,
What brilliant and useful code from your typically brilliant self! Another star for the star!
It is truly a treat seeing you in the forum again.
Best Regards,
-Bob in California
What brilliant and useful code from your typically brilliant self! Another star for the star!
It is truly a treat seeing you in the forum again.
Best Regards,
-Bob in California
-
1
- #15
elizabeth-
This is much easier in Word. I'm not sure about all versions of Word, but if you go to View > Header / Footer, you should be able to accomplish this.
Select Insert > Field from the Menu Bar. Under Categories, select (All) and in the Field Names box, scroll down to SaveDate. Select this, click OK. This will print the last save date (and time, unfortunately) for you.
Hope that helps.
*cLFlaVA
----------------------------
A polar bear walks into a bar and says, "Can I have a ... beer?"
The bartender asks, "What's with the big pause?
This is much easier in Word. I'm not sure about all versions of Word, but if you go to View > Header / Footer, you should be able to accomplish this.
Select Insert > Field from the Menu Bar. Under Categories, select (All) and in the Field Names box, scroll down to SaveDate. Select this, click OK. This will print the last save date (and time, unfortunately) for you.
Hope that helps.
*cLFlaVA
----------------------------
A polar bear walks into a bar and says, "Can I have a ... beer?"
The bartender asks, "What's with the big pause?
- Status
Similar threads
- Locked
- Question