Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Insert last saved date into header
I am using Excel 2003. I created a Macro with the following custom function:
Function Last_Saved_Date() Last_Saved_Date = Application.Caller.Parent.Parent. _ BuiltinDocumentProperties("Last Save Time").Value End Function Then in a cell in the worksheet, I have it as "=Last_Saved_Date()" and I make sure to format the cell in a date format. Doing this, I am able to see the last saved date inside the worksheet. My questions are the following: 1. How do I have the header print at the top of each page, the last saved date of the file? May I use the contents of my Last Saved Date cell? 2. Is there a more efficient way to do this in the header without using this macro? Thank you for your help, Amy |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Insert last saved date into header
This will populate the header of each sheet with the last saved date each
time the workbook is opened. This does need to go in a module and not on an individual sheet. Sub Auto_Open Dim Last_Saved as Date wscount=Activeworkbook.Worksheets.Count Last_Saved= Application.Caller.Parent.Parent. _ BuiltinDocumentProperties("Last Save Time").Value For A=1 to wscount With Sheets(A).PageSetup .LeftHeader = Last_Saved End With Next A End Sub -- If this helps, please remember to click yes. "Amy" wrote: I am using Excel 2003. I created a Macro with the following custom function: Function Last_Saved_Date() Last_Saved_Date = Application.Caller.Parent.Parent. _ BuiltinDocumentProperties("Last Save Time").Value End Function Then in a cell in the worksheet, I have it as "=Last_Saved_Date()" and I make sure to format the cell in a date format. Doing this, I am able to see the last saved date inside the worksheet. My questions are the following: 1. How do I have the header print at the top of each page, the last saved date of the file? May I use the contents of my Last Saved Date cell? 2. Is there a more efficient way to do this in the header without using this macro? Thank you for your help, Amy |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Insert last saved date into header
Amy, to add to what Paul C gave you (a good way to do it, by the way). While
you could use the contents of the cell where you put the user defined function, it would just require more code to get it from the cell to the header. So it turns out to be not really needed, and the way Paul C gave you is more efficient. Also, unless my thinking is muddled this morning, remember that the entry you get is going to be exactly that - the last saved date/time. Which means it's not going to be the date/time that the current user saves the file at the end of their session when it is next opened. If you (also) call the routine during the workbook's _Open() event, it would be updated to the true last time saved. "Amy" wrote: I am using Excel 2003. I created a Macro with the following custom function: Function Last_Saved_Date() Last_Saved_Date = Application.Caller.Parent.Parent. _ BuiltinDocumentProperties("Last Save Time").Value End Function Then in a cell in the worksheet, I have it as "=Last_Saved_Date()" and I make sure to format the cell in a date format. Doing this, I am able to see the last saved date inside the worksheet. My questions are the following: 1. How do I have the header print at the top of each page, the last saved date of the file? May I use the contents of my Last Saved Date cell? 2. Is there a more efficient way to do this in the header without using this macro? Thank you for your help, Amy |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Insert last saved date into header
Let me see if I understand this correctly. What you mention below of what I
won't get is actually what I do want. I'm sorry if I did not ask my question more clearly. let me try again using an example. I have a file named Peppers.xls. I open the file, make some changes, save it, and close it. The file properties show 6/16/09 xxtime after I close the file. Now, I edit the Peppers.xls file the next day, save my changes, and exit Excel again. Now, the file properties show 6/17/09 xxtime after closing the file. How do I get the "6/17/09" information in to the header? If I use Paul's version (yup, much more eloquent than my bullied attempt), will I get this as the last time the file was saved? Each time I edit the file, I would like that new date to show up in the header. Hope that makes sense and thanks for your help! - Amy "JLatham" wrote: Amy, to add to what Paul C gave you (a good way to do it, by the way). While you could use the contents of the cell where you put the user defined function, it would just require more code to get it from the cell to the header. So it turns out to be not really needed, and the way Paul C gave you is more efficient. Also, unless my thinking is muddled this morning, remember that the entry you get is going to be exactly that - the last saved date/time. Which means it's not going to be the date/time that the current user saves the file at the end of their session when it is next opened. If you (also) call the routine during the workbook's _Open() event, it would be updated to the true last time saved. "Amy" wrote: I am using Excel 2003. I created a Macro with the following custom function: Function Last_Saved_Date() Last_Saved_Date = Application.Caller.Parent.Parent. _ BuiltinDocumentProperties("Last Save Time").Value End Function Then in a cell in the worksheet, I have it as "=Last_Saved_Date()" and I make sure to format the cell in a date format. Doing this, I am able to see the last saved date inside the worksheet. My questions are the following: 1. How do I have the header print at the top of each page, the last saved date of the file? May I use the contents of my Last Saved Date cell? 2. Is there a more efficient way to do this in the header without using this macro? Thank you for your help, Amy |
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Insert last saved date into header
Let me see if I understand this correctly. What you mention below of what I
won't get is actually what I do want. I'm sorry if I did not ask my question more clearly. let me try again using an example. I have a file named Peppers.xls. I open the file, make some changes, save it, and close it. The file properties show 6/16/09 xxtime after I close the file. Now, I edit the Peppers.xls file the next day, save my changes, and exit Excel again. Now, the file properties show 6/17/09 xxtime after closing the file. How do I get the "6/17/09" information in to the header? If I use Paul's version (yup, much more eloquent than my bullied attempt), will I get this as the last time the file was saved? Each time I edit the file, I would like that new date to show up in the header. Hope that makes sense and thanks for your help! - Amy "JLatham" wrote: Amy, to add to what Paul C gave you (a good way to do it, by the way). While you could use the contents of the cell where you put the user defined function, it would just require more code to get it from the cell to the header. So it turns out to be not really needed, and the way Paul C gave you is more efficient. Also, unless my thinking is muddled this morning, remember that the entry you get is going to be exactly that - the last saved date/time. Which means it's not going to be the date/time that the current user saves the file at the end of their session when it is next opened. If you (also) call the routine during the workbook's _Open() event, it would be updated to the true last time saved. "Amy" wrote: I am using Excel 2003. I created a Macro with the following custom function: Function Last_Saved_Date() Last_Saved_Date = Application.Caller.Parent.Parent. _ BuiltinDocumentProperties("Last Save Time").Value End Function Then in a cell in the worksheet, I have it as "=Last_Saved_Date()" and I make sure to format the cell in a date format. Doing this, I am able to see the last saved date inside the worksheet. My questions are the following: 1. How do I have the header print at the top of each page, the last saved date of the file? May I use the contents of my Last Saved Date cell? 2. Is there a more efficient way to do this in the header without using this macro? Thank you for your help, Amy |
#6
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Insert last saved date into header
Amy
You don't really need a UDF to return the date/time to a header/footer. Sub Last_Saved_Footer() 'Private Sub Workbook_BeforePrint(Cancel As Boolean) Dim wkSht As Worksheet For Each wkSht In ThisWorkbook.Worksheets wkSht.PageSetup.RightFooter = "&8Last Saved : " & _ Format(ThisWorkbook.BuiltinDocumentProperties("Las t Save Time"), _ "yyyy-mmm-dd hh:mm:ss") Next wkSht End Sub Gord Dibben MS Excel MVP On Thu, 18 Jun 2009 08:44:01 -0700, Amy wrote: Let me see if I understand this correctly. What you mention below of what I won't get is actually what I do want. I'm sorry if I did not ask my question more clearly. let me try again using an example. I have a file named Peppers.xls. I open the file, make some changes, save it, and close it. The file properties show 6/16/09 xxtime after I close the file. Now, I edit the Peppers.xls file the next day, save my changes, and exit Excel again. Now, the file properties show 6/17/09 xxtime after closing the file. How do I get the "6/17/09" information in to the header? If I use Paul's version (yup, much more eloquent than my bullied attempt), will I get this as the last time the file was saved? Each time I edit the file, I would like that new date to show up in the header. Hope that makes sense and thanks for your help! - Amy "JLatham" wrote: Amy, to add to what Paul C gave you (a good way to do it, by the way). While you could use the contents of the cell where you put the user defined function, it would just require more code to get it from the cell to the header. So it turns out to be not really needed, and the way Paul C gave you is more efficient. Also, unless my thinking is muddled this morning, remember that the entry you get is going to be exactly that - the last saved date/time. Which means it's not going to be the date/time that the current user saves the file at the end of their session when it is next opened. If you (also) call the routine during the workbook's _Open() event, it would be updated to the true last time saved. "Amy" wrote: I am using Excel 2003. I created a Macro with the following custom function: Function Last_Saved_Date() Last_Saved_Date = Application.Caller.Parent.Parent. _ BuiltinDocumentProperties("Last Save Time").Value End Function Then in a cell in the worksheet, I have it as "=Last_Saved_Date()" and I make sure to format the cell in a date format. Doing this, I am able to see the last saved date inside the worksheet. My questions are the following: 1. How do I have the header print at the top of each page, the last saved date of the file? May I use the contents of my Last Saved Date cell? 2. Is there a more efficient way to do this in the header without using this macro? Thank you for your help, Amy |
#7
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Insert last saved date into header
Actually, since Paul's method uses the _Open() event, I believe it'll show
you what you want. Best way to make sure is to put the code in the workbook and save, close and open a few times to be sure is shows what you want. "Amy" wrote: Let me see if I understand this correctly. What you mention below of what I won't get is actually what I do want. I'm sorry if I did not ask my question more clearly. let me try again using an example. I have a file named Peppers.xls. I open the file, make some changes, save it, and close it. The file properties show 6/16/09 xxtime after I close the file. Now, I edit the Peppers.xls file the next day, save my changes, and exit Excel again. Now, the file properties show 6/17/09 xxtime after closing the file. How do I get the "6/17/09" information in to the header? If I use Paul's version (yup, much more eloquent than my bullied attempt), will I get this as the last time the file was saved? Each time I edit the file, I would like that new date to show up in the header. Hope that makes sense and thanks for your help! - Amy "JLatham" wrote: Amy, to add to what Paul C gave you (a good way to do it, by the way). While you could use the contents of the cell where you put the user defined function, it would just require more code to get it from the cell to the header. So it turns out to be not really needed, and the way Paul C gave you is more efficient. Also, unless my thinking is muddled this morning, remember that the entry you get is going to be exactly that - the last saved date/time. Which means it's not going to be the date/time that the current user saves the file at the end of their session when it is next opened. If you (also) call the routine during the workbook's _Open() event, it would be updated to the true last time saved. "Amy" wrote: I am using Excel 2003. I created a Macro with the following custom function: Function Last_Saved_Date() Last_Saved_Date = Application.Caller.Parent.Parent. _ BuiltinDocumentProperties("Last Save Time").Value End Function Then in a cell in the worksheet, I have it as "=Last_Saved_Date()" and I make sure to format the cell in a date format. Doing this, I am able to see the last saved date inside the worksheet. My questions are the following: 1. How do I have the header print at the top of each page, the last saved date of the file? May I use the contents of my Last Saved Date cell? 2. Is there a more efficient way to do this in the header without using this macro? Thank you for your help, Amy |
#8
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Insert last saved date into header
Thank you. I just tried to execute that but I get a "Run-time error '424':
Object required" message. Do you know what I might have done wrong? Thanks. "JLatham" wrote: Actually, since Paul's method uses the _Open() event, I believe it'll show you what you want. Best way to make sure is to put the code in the workbook and save, close and open a few times to be sure is shows what you want. "Amy" wrote: Let me see if I understand this correctly. What you mention below of what I won't get is actually what I do want. I'm sorry if I did not ask my question more clearly. let me try again using an example. I have a file named Peppers.xls. I open the file, make some changes, save it, and close it. The file properties show 6/16/09 xxtime after I close the file. Now, I edit the Peppers.xls file the next day, save my changes, and exit Excel again. Now, the file properties show 6/17/09 xxtime after closing the file. How do I get the "6/17/09" information in to the header? If I use Paul's version (yup, much more eloquent than my bullied attempt), will I get this as the last time the file was saved? Each time I edit the file, I would like that new date to show up in the header. Hope that makes sense and thanks for your help! - Amy "JLatham" wrote: Amy, to add to what Paul C gave you (a good way to do it, by the way). While you could use the contents of the cell where you put the user defined function, it would just require more code to get it from the cell to the header. So it turns out to be not really needed, and the way Paul C gave you is more efficient. Also, unless my thinking is muddled this morning, remember that the entry you get is going to be exactly that - the last saved date/time. Which means it's not going to be the date/time that the current user saves the file at the end of their session when it is next opened. If you (also) call the routine during the workbook's _Open() event, it would be updated to the true last time saved. "Amy" wrote: I am using Excel 2003. I created a Macro with the following custom function: Function Last_Saved_Date() Last_Saved_Date = Application.Caller.Parent.Parent. _ BuiltinDocumentProperties("Last Save Time").Value End Function Then in a cell in the worksheet, I have it as "=Last_Saved_Date()" and I make sure to format the cell in a date format. Doing this, I am able to see the last saved date inside the worksheet. My questions are the following: 1. How do I have the header print at the top of each page, the last saved date of the file? May I use the contents of my Last Saved Date cell? 2. Is there a more efficient way to do this in the header without using this macro? Thank you for your help, Amy |
#9
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Insert last saved date into header
Probably not doing anything wrong. A couple of small hiccups in the original
code. I hadn't tested it as the general process looked valid. Try this code in its place. Note that it will return an error until the workbook has been saved to HDD at least once (until then there is no such thing as "last saved time"). Sub Auto_Open() Dim Last_Saved As String Last_Saved = "Last save time" For A = 1 To ThisWorkbook.Worksheets.Count With Sheets(A).PageSetup .LeftHeader = _ ThisWorkbook.BuiltinDocumentProperties(Last_Saved) End With Next A End Sub "Amy" wrote: Thank you. I just tried to execute that but I get a "Run-time error '424': Object required" message. Do you know what I might have done wrong? Thanks. "JLatham" wrote: Actually, since Paul's method uses the _Open() event, I believe it'll show you what you want. Best way to make sure is to put the code in the workbook and save, close and open a few times to be sure is shows what you want. "Amy" wrote: Let me see if I understand this correctly. What you mention below of what I won't get is actually what I do want. I'm sorry if I did not ask my question more clearly. let me try again using an example. I have a file named Peppers.xls. I open the file, make some changes, save it, and close it. The file properties show 6/16/09 xxtime after I close the file. Now, I edit the Peppers.xls file the next day, save my changes, and exit Excel again. Now, the file properties show 6/17/09 xxtime after closing the file. How do I get the "6/17/09" information in to the header? If I use Paul's version (yup, much more eloquent than my bullied attempt), will I get this as the last time the file was saved? Each time I edit the file, I would like that new date to show up in the header. Hope that makes sense and thanks for your help! - Amy "JLatham" wrote: Amy, to add to what Paul C gave you (a good way to do it, by the way). While you could use the contents of the cell where you put the user defined function, it would just require more code to get it from the cell to the header. So it turns out to be not really needed, and the way Paul C gave you is more efficient. Also, unless my thinking is muddled this morning, remember that the entry you get is going to be exactly that - the last saved date/time. Which means it's not going to be the date/time that the current user saves the file at the end of their session when it is next opened. If you (also) call the routine during the workbook's _Open() event, it would be updated to the true last time saved. "Amy" wrote: I am using Excel 2003. I created a Macro with the following custom function: Function Last_Saved_Date() Last_Saved_Date = Application.Caller.Parent.Parent. _ BuiltinDocumentProperties("Last Save Time").Value End Function Then in a cell in the worksheet, I have it as "=Last_Saved_Date()" and I make sure to format the cell in a date format. Doing this, I am able to see the last saved date inside the worksheet. My questions are the following: 1. How do I have the header print at the top of each page, the last saved date of the file? May I use the contents of my Last Saved Date cell? 2. Is there a more efficient way to do this in the header without using this macro? Thank you for your help, Amy |
#10
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Insert last saved date into header
Or see Gord Dibben's posting above.
"Amy" wrote: Thank you. I just tried to execute that but I get a "Run-time error '424': Object required" message. Do you know what I might have done wrong? Thanks. "JLatham" wrote: Actually, since Paul's method uses the _Open() event, I believe it'll show you what you want. Best way to make sure is to put the code in the workbook and save, close and open a few times to be sure is shows what you want. "Amy" wrote: Let me see if I understand this correctly. What you mention below of what I won't get is actually what I do want. I'm sorry if I did not ask my question more clearly. let me try again using an example. I have a file named Peppers.xls. I open the file, make some changes, save it, and close it. The file properties show 6/16/09 xxtime after I close the file. Now, I edit the Peppers.xls file the next day, save my changes, and exit Excel again. Now, the file properties show 6/17/09 xxtime after closing the file. How do I get the "6/17/09" information in to the header? If I use Paul's version (yup, much more eloquent than my bullied attempt), will I get this as the last time the file was saved? Each time I edit the file, I would like that new date to show up in the header. Hope that makes sense and thanks for your help! - Amy "JLatham" wrote: Amy, to add to what Paul C gave you (a good way to do it, by the way). While you could use the contents of the cell where you put the user defined function, it would just require more code to get it from the cell to the header. So it turns out to be not really needed, and the way Paul C gave you is more efficient. Also, unless my thinking is muddled this morning, remember that the entry you get is going to be exactly that - the last saved date/time. Which means it's not going to be the date/time that the current user saves the file at the end of their session when it is next opened. If you (also) call the routine during the workbook's _Open() event, it would be updated to the true last time saved. "Amy" wrote: I am using Excel 2003. I created a Macro with the following custom function: Function Last_Saved_Date() Last_Saved_Date = Application.Caller.Parent.Parent. _ BuiltinDocumentProperties("Last Save Time").Value End Function Then in a cell in the worksheet, I have it as "=Last_Saved_Date()" and I make sure to format the cell in a date format. Doing this, I am able to see the last saved date inside the worksheet. My questions are the following: 1. How do I have the header print at the top of each page, the last saved date of the file? May I use the contents of my Last Saved Date cell? 2. Is there a more efficient way to do this in the header without using this macro? Thank you for your help, Amy |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Any formula to insert last document saved date ? | Excel Worksheet Functions | |||
Adding "last saved date" to header | Excel Discussion (Misc queries) | |||
Populating Last Saved Date in Cell AND also update that same cell in Header | Excel Discussion (Misc queries) | |||
How do I insert the date the file was saved in the MS Excel foote. | Excel Discussion (Misc queries) | |||
How to insert date last saved into the sheet (not the footer) | Excel Worksheet Functions |