Date Last Modified

[Guest]

Hi All,

I need to add the 'date last modified' of one spreadsheet into a cell of
another spreadsheet and have it update whenever the spreadsheet is opened.

i.e. in cell A1 of spreadsheet B, i would like to show the date last
modified of spreadsheet A and whenever spreadsheet B is opened it updates so
that if spreadsheet A has been modified, the date updates.

I cannot change spreadsheet A in any way.

If possible i'd like to do this with formulae but if the only solution is in
VBA, please can you be as specific as possible as my vba is weak.

Thanks
Emma

 

[All In]

Hi try this bit of code I got from another user. I think we are tryin
to do the same thing. I setit up as a macro. Every time my boss open
the spreed sheet and changes something it puts the date it was update
on the sheet.


Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Cells(1, 1) = Now

End Sub




I hope this helps you

All I

 

[Guest]

Thanks for trying but as i said i cannot change spreadsheet A in any way.

Does anyone else have any ideas?

Thanks

 

[Guest]

Emma,

My VBA is almost certainly weaker than yours - it was non-existent until
about an hour ago! But I realise I'm going to have to bite the bullet some
time and learn to use it, so I've just done a bit of research and I think I
may have solved your problem.

I think this ought to work:

1. Open your spreadsheet B
2. Open the visual basic editor (Alt+F11)
3. In the left-hand pane, click on where it says "VBAProject
(SpreadsheetB.xls)"
3. Insert a new module (Insert > Module)
4. Copy the following, and paste it into the upper-right-hand pane:

Function Date_last_modified(File_path As String)
Application.Volatile
Date_last_modified= FileDateTime(File_path)
End Function

5. Close the visual basic editor (Alt+Q)
6. Type the following into cell A1 of spreadsheet B:

=Date_last_modified("C:\Folder\SpreadsheetA.xls")

(... where C:\Folder\SpreadsheetA.xls is the full path of your spreadsheet A)

7. Reformat the cell as a date (Format > Cells... > Number > Date).

The cell should now show the date that Spreadsheet A was last modified.

I hope this works and is what you were after.

Best wishes,

Graham