Date Macro

[Mikeyb7]

Hi,

How do i set up a macro so that when a spreadsheet is updated the date will update to represent this?

Thanks

Mike

[Mikeyb7]

Can any1 please help with this?

Thanks

[bhofsetz]

What about the spreadsheet do you want to reflect the update date? Windows should automatically keep track of information such as date created, date modified, and date accessed. Just look in the File menu under Properties.

If you want the date to appear on the spreadsheet with the current date you could simply put a function into a cell which will return today's date such as
=Today()

HTH

[Mikeyb7]

Hi

I have the macro set up so it should show when a spreadsheet was last modified by using the following;

Sub DateLastModified()
Dim fs, f
Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.GetFile("T:\Jobs\Job Loading\Manual Job Loading\Job Board Brands\CareerStructure.com\Weekly Task Spreadsheet.xls")
Cells(2, 1) = f.DateLastModified
End Sub


and i have the auto run feature of;

Private Sub Workbook_Open()
Run "DateLastModified"
End Sub


However, when the spreadsheet is opened it gives me the date of when i'm opening the document and not when it was last saved by someone else who has just used it?

Can any1 please help!

Thanks

Mike

[EdMac]

If the macro was to run on workbook close, that would tell you when it was last closed.

Ed

[Mikeyb7]

is it possible to have tell me when it was last saved or edited by someone else and not the time when i open the spreadsheet?

Thanks

Mike

[EdMac]

I'm no VB wizard but have you tried this?

Private Sub Workbook_close()
Run "DateLastModified"
End Sub

When the book is closed the code runs and enters the date. When you open it, you can see when it was last closed. If you added a bit more code you could also have the user name of the last who closed it.

Ed