Last modified date of a linked file

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

tjc - 21 Aug 2007 16:34 GMT

I've seen several posts on how to display last saved date for the active
workbook, but I'm searching for the last saved date of a linked file. Can
anyone suggest a way to use BuiltInDocumentProperties with a reference to the
another file?

Reply to this Message

Joel - 21 Aug 2007 17:06 GMT

Sub getfiles()

Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.GetFile("c:\temp\book1.xls")
MsgBox ("last Modified = " & f.datelastmodified)
End Sub

> I've seen several posts on how to display last saved date for the active
> workbook, but I'm searching for the last saved date of a linked file. Can
> anyone suggest a way to use BuiltInDocumentProperties with a reference to the
> another file?

Reply to this Message

tjc - 21 Aug 2007 17:32 GMT

Thanks Joel.
But, I'm looking for a user defined function. Maybe something along the
lines of
=lastsaved(c:/otherfile!A1)

> Sub getfiles()
>
[quoted text clipped - 7 lines]
> > anyone suggest a way to use BuiltInDocumentProperties with a reference to the
> > another file?

Reply to this Message

Joel - 21 Aug 2007 17:52 GMT

Simple. We change the macro to a function

call function with a string name of a file in double quotes
=lastsaved("c:\temp\book1.xls")

Function lastsaved(FileName As String)
Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.GetFile(FileName)
lastsaved = f.datelastmodified
End Function

> Thanks Joel.
> But, I'm looking for a user defined function. Maybe something along the
[quoted text clipped - 12 lines]
> > > anyone suggest a way to use BuiltInDocumentProperties with a reference to the
> > > another file?

Reply to this Message

tjc - 21 Aug 2007 18:56 GMT

Thanks Joel. This code works great.
For my purposes, is there a way to use a cell reference that would be
updated when along with other links in the worksheet instead of the string
reference?

> Simple. We change the macro to a function
>
[quoted text clipped - 23 lines]
> > > > anyone suggest a way to use BuiltInDocumentProperties with a reference to the
> > > > another file?

Reply to this Message

Joel - 21 Aug 2007 20:12 GMT

A String is a String is a String. Doesn't matter where it comes from. If
you have a cell that is text then
=lastsaved(A1)

where A1 = "c:\temp\book1.xls"

> Thanks Joel. This code works great.
> For my purposes, is there a way to use a cell reference that would be
[quoted text clipped - 28 lines]
> > > > > anyone suggest a way to use BuiltInDocumentProperties with a reference to the
> > > > > another file?

Reply to this Message

tjc - 21 Aug 2007 20:26 GMT

Thanks Joel. I haven't figured out how to convert an active cell reference
to text. In this case, I'm trying to use the code you provided to get the
last saved date of a file referenced in another cell. For example,

cell A1 contains =c:\temp\book1.xls!A1 which returns the value in A1

In cell B1, I'm trying to get the last saved date of book1.xls in such a way
that it will update as the file referenced in A1 is changed through update
links. I've tried a version of =lastsaved(mid(A1,2,18)) which doesn't work
since A1 returns a value instead of the cell referenced.

> A String is a String is a String. Doesn't matter where it comes from. If
> you have a cell that is text then
[quoted text clipped - 34 lines]
> > > > > > anyone suggest a way to use BuiltInDocumentProperties with a reference to the
> > > > > > another file?

Reply to this Message

Joel - 21 Aug 2007 21:16 GMT

You don't have to convert. Any cell in text format will be passed in to the
function correctly.

> Thanks Joel. I haven't figured out how to convert an active cell reference
> to text. In this case, I'm trying to use the code you provided to get the
[quoted text clipped - 45 lines]
> > > > > > > anyone suggest a way to use BuiltInDocumentProperties with a reference to the
> > > > > > > another file?

Reply to this Message

tjc - 31 Aug 2007 16:30 GMT

Thanks Joel. I got the answer to converting file name referenced in a
formula to text in another post ("Show cell reference as text"). Combined
with your LastSaved UDF, my problem is completely solved.

> You don't have to convert. Any cell in text format will be passed in to the
> function correctly.
[quoted text clipped - 48 lines]
> > > > > > > > anyone suggest a way to use BuiltInDocumentProperties with a reference to the
> > > > > > > > another file?

Reply to this Message