I have a work log that I use to track my daily projects that is an excel
sheet
and in the fifth column I have the customers name which I have hyper linked
to a WORD document that is in a specific directory I would like to have
eighth
cell on that same line show the saved date of the WORD file with the name of
the
hyperlinked file this is one line of my log.

-A- -B- -C- -D- -E-
-F- -G-
-H-
(status) (item) (entry date) (invoice) (customer) (project)
(comments) (updated)
Closed 10 05/08/07 2398057 Franklin Band Saw Post
Waiting For response "the date of the word file"

If I could do this I can look at a glance and see the date of the last
activity based on the file date of the hyperlinked WORD document

All the word documents are in the same folder, I add items to this everyday,
so the rows across just keep accumulating
I don't know if its possible to have it do this automatically update cell
"H" every time the word file gets changed so I would
always have up to date information

I'm open to suggestions, I just don't know how to get this macro in a cell,
I mean if this is even the way this should be done

Thanks,

Jeff W.

Well, you're not easily going to get this updated each time the Word file
gets changed - Excel has no direct way of knowing when Word did something.

But the information you need is all the the hyperlink to the file will
contain the path to that file and that gives Excel something to work with -

Given that we can now determine where the Word file is, you have a couple of
options on how to get its last updated date into column G. You could set up
some VBA code in the workbook's _Open() event handler that would go down
through which ever column you have the hyperlink in (that's not clear from
your example), and get the dates and put them in the appropriate row, column
G. A second option is much the same - but the code would be associated with
some worksheet action - such as activating it (but if it's the only sheet in
the workbook, that's not a good choice - the _Activate event doesn't fire
until that sheet is selected, and being the active sheet when the workbook
opens doesn't count). Third option is to have the same code working from an
"on demand" button on the sheet that you'd click when you wanted the
information updated.

To get this going, it would be very helpful to know the name of this
worksheet and the column with the hyperlinks in it. Meanwhile, I've got to
go dig around in my "file info" information and remember how to code up
digging out the file date from a disk file...

"Jeff W." wrote:

I have a work log that I use to track my daily projects that is an excel
sheet
and in the fifth column I have the customers name which I have hyper linked
to a WORD document that is in a specific directory I would like to have
eighth
cell on that same line show the saved date of the WORD file with the name of
the
hyperlinked file this is one line of my log.

-A- -B- -C- -D- -E-
-F- -G-
-H-
(status) (item) (entry date) (invoice) (customer) (project)
(comments) (updated)
Closed 10 05/08/07 2398057 Franklin Band Saw Post
Waiting For response "the date of the word file"

If I could do this I can look at a glance and see the date of the last
activity based on the file date of the hyperlinked WORD document

All the word documents are in the same folder, I add items to this everyday,
so the rows across just keep accumulating
I don't know if its possible to have it do this automatically update cell
"H" every time the word file gets changed so I would
always have up to date information

I'm open to suggestions, I just don't know how to get this macro in a cell,
I mean if this is even the way this should be done

Thanks,

Jeff W.

Jeff W.,

Enter "=When(C5)" on the worksheet, with C5 representing
the cell with the hyperlink.

Place the following code in a standard module...
'---
Function When(ByRef rng As Excel.Range) As String
Dim P As String
Dim T As Date
Application.Volatile
If rng.Hyperlinks.Count < 1 Then
When = "No Hyperlink"
Else
P = rng.Hyperlinks(1).Address
T = FileDateTime(P)
When = Format$(T, "General Date")
End If
End Function
'---
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)
(Check out "List Files")


"Jeff W."
wrote in message
I have a work log that I use to track my daily projects that is an excel
sheet
and in the fifth column I have the customers name which I have hyper linked
to a WORD document that is in a specific directory I would like to have
eighth
cell on that same line show the saved date of the WORD file with the name of
the
hyperlinked file this is one line of my log.

-A- -B- -C- -D- -E-
-F- -G-
-H-
(status) (item) (entry date) (invoice) (customer) (project)
(comments) (updated)
Closed 10 05/08/07 2398057 Franklin Band Saw Post
Waiting For response "the date of the word file"

If I could do this I can look at a glance and see the date of the last
activity based on the file date of the hyperlinked WORD document

All the word documents are in the same folder, I add items to this everyday,
so the rows across just keep accumulating
I don't know if its possible to have it do this automatically update cell
"H" every time the word file gets changed so I would
always have up to date information

I'm open to suggestions, I just don't know how to get this macro in a cell,
I mean if this is even the way this should be done
Thanks,
Jeff W.

OK, the name of the work book is "work log 2007.xls"
The name of the file/hyperlink in always in column "E"
The date of the hyperlinked file will always be in column "H"

I guess maybe having update the cell every time file date
changes may be too much to expect, maybe I could have it run
through and update these fields on the opening of the work book only

I suppose this would be fine, and I think probably easier.

Anything you could do to help would be appreciated greatly...

Thanks,

Jeff W.


"JLatham" <HelpFrom @ Jlathamsite.com.(removethis) wrote in message
...
Well, you're not easily going to get this updated each time the Word file
gets changed - Excel has no direct way of knowing when Word did something.

But the information you need is all the the hyperlink to the file will
contain the path to that file and that gives Excel something to work
with -

Given that we can now determine where the Word file is, you have a couple
of
options on how to get its last updated date into column G. You could set
up
some VBA code in the workbook's _Open() event handler that would go down
through which ever column you have the hyperlink in (that's not clear from
your example), and get the dates and put them in the appropriate row,
column
G. A second option is much the same - but the code would be associated
with
some worksheet action - such as activating it (but if it's the only sheet
in
the workbook, that's not a good choice - the _Activate event doesn't fire
until that sheet is selected, and being the active sheet when the workbook
opens doesn't count). Third option is to have the same code working from
an
"on demand" button on the sheet that you'd click when you wanted the
information updated.

To get this going, it would be very helpful to know the name of this
worksheet and the column with the hyperlinks in it. Meanwhile, I've got
to
go dig around in my "file info" information and remember how to code up
digging out the file date from a disk file...

"Jeff W." wrote:

I have a work log that I use to track my daily projects that is an excel
sheet
and in the fifth column I have the customers name which I have hyper
linked
to a WORD document that is in a specific directory I would like to have
eighth
cell on that same line show the saved date of the WORD file with the name
of
the
hyperlinked file this is one line of my log.

-A- -B- -C- -D- -E-
-F- -G-
-H-
(status) (item) (entry date) (invoice) (customer) (project)
(comments) (updated)
Closed 10 05/08/07 2398057 Franklin Band Saw
Post
Waiting For response "the date of the word file"

If I could do this I can look at a glance and see the date of the last
activity based on the file date of the hyperlinked WORD document

All the word documents are in the same folder, I add items to this
everyday,
so the rows across just keep accumulating
I don't know if its possible to have it do this automatically update cell
"H" every time the word file gets changed so I would
always have up to date information

I'm open to suggestions, I just don't know how to get this macro in a
cell,
I mean if this is even the way this should be done

Thanks,

Jeff W.

Look at Jim Cone's solution below - spot on answer for you! I worked out a
way using Scripting and the FileSystemObject, but his solution is much
cleaner and probably the way to go. To adapt it for your setup, you'd put
=When(E2)
into cell H2, and you can drag/fill the formula on down the sheet as far as
you need to go.

"Jeff W." wrote:

OK, the name of the work book is "work log 2007.xls"
The name of the file/hyperlink in always in column "E"
The date of the hyperlinked file will always be in column "H"

I guess maybe having update the cell every time file date
changes may be too much to expect, maybe I could have it run
through and update these fields on the opening of the work book only

I suppose this would be fine, and I think probably easier.

Anything you could do to help would be appreciated greatly...

Thanks,

Jeff W.


"JLatham" <HelpFrom @ Jlathamsite.com.(removethis) wrote in message
...
Well, you're not easily going to get this updated each time the Word file
gets changed - Excel has no direct way of knowing when Word did something.

But the information you need is all the the hyperlink to the file will
contain the path to that file and that gives Excel something to work
with -

Given that we can now determine where the Word file is, you have a couple
of
options on how to get its last updated date into column G. You could set
up
some VBA code in the workbook's _Open() event handler that would go down
through which ever column you have the hyperlink in (that's not clear from
your example), and get the dates and put them in the appropriate row,
column
G. A second option is much the same - but the code would be associated
with
some worksheet action - such as activating it (but if it's the only sheet
in
the workbook, that's not a good choice - the _Activate event doesn't fire
until that sheet is selected, and being the active sheet when the workbook
opens doesn't count). Third option is to have the same code working from
an
"on demand" button on the sheet that you'd click when you wanted the
information updated.

To get this going, it would be very helpful to know the name of this
worksheet and the column with the hyperlinks in it. Meanwhile, I've got
to
go dig around in my "file info" information and remember how to code up
digging out the file date from a disk file...

"Jeff W." wrote:

I have a work log that I use to track my daily projects that is an excel
sheet
and in the fifth column I have the customers name which I have hyper
linked
to a WORD document that is in a specific directory I would like to have
eighth
cell on that same line show the saved date of the WORD file with the name
of
the
hyperlinked file this is one line of my log.

-A- -B- -C- -D- -E-
-F- -G-
-H-
(status) (item) (entry date) (invoice) (customer) (project)
(comments) (updated)
Closed 10 05/08/07 2398057 Franklin Band Saw
Post
Waiting For response "the date of the word file"

If I could do this I can look at a glance and see the date of the last
activity based on the file date of the hyperlinked WORD document

All the word documents are in the same folder, I add items to this
everyday,
so the rows across just keep accumulating
I don't know if its possible to have it do this automatically update cell
"H" every time the word file gets changed so I would
always have up to date information

I'm open to suggestions, I just don't know how to get this macro in a
cell,
I mean if this is even the way this should be done

Thanks,

Jeff W.

Jim I think this may be close but I'm not sure its working right and it
could be me I don't know.

I added the code to a module, and I went to line 2 in the book and entered
the formula "=When(c2)"
into H2, this returns a "No Hyperlink" in that cell and since the actual
hyperlink in the "E" column
I changed it to =When(e2) this returned a "#Value!" I have formatted the
cell for date but no difference

I'm not really that sharp with this stuff, but I don't know how putting
"=When(C5)" in all my cells in column "H"
is going to work, cause it looks like it would work for row 5 only, when
column E is the hyperlink column entry
for each line item on the sheet so I this it needs to increment between
rows.

<Jeff


"Jim Cone" wrote in message
...
Jeff W.,

Enter "=When(C5)" on the worksheet, with C5 representing
the cell with the hyperlink.

Place the following code in a standard module...
'---
Function When(ByRef rng As Excel.Range) As String
Dim P As String
Dim T As Date
Application.Volatile
If rng.Hyperlinks.Count < 1 Then
When = "No Hyperlink"
Else
P = rng.Hyperlinks(1).Address
T = FileDateTime(P)
When = Format$(T, "General Date")
End If
End Function
'---
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)
(Check out "List Files")


"Jeff W."
wrote in message
I have a work log that I use to track my daily projects that is an excel
sheet
and in the fifth column I have the customers name which I have hyper
linked
to a WORD document that is in a specific directory I would like to have
eighth
cell on that same line show the saved date of the WORD file with the name
of
the
hyperlinked file this is one line of my log.

-A- -B- -C- -D- -E-
-F- -G-
-H-
(status) (item) (entry date) (invoice) (customer) (project)
(comments) (updated)
Closed 10 05/08/07 2398057 Franklin Band Saw Post
Waiting For response "the date of the word file"

If I could do this I can look at a glance and see the date of the last
activity based on the file date of the hyperlinked WORD document

All the word documents are in the same folder, I add items to this
everyday,
so the rows across just keep accumulating
I don't know if its possible to have it do this automatically update cell
"H" every time the word file gets changed so I would
always have up to date information

I'm open to suggestions, I just don't know how to get this macro in a
cell,
I mean if this is even the way this should be done
Thanks,
Jeff W.

Jeff ,
The When function will not work if you created the hyperlink using a
Hyperlink formula: "=Hyperlink(...)".
It should work if the hyperlink is created using Insert | Hyperlink (from the menu).
The cell format can remain at "General".
You might try inserting a new hyperlink and seeing if the When function
returns the date/time.

You can "fill" (down or across) by dragging the lower right corner of the cell.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)



"Jeff W."
wrote in message
Jim I think this may be close but I'm not sure its working right and it
could be me I don't know.

I added the code to a module, and I went to line 2 in the book and entered
the formula "=When(c2)"
into H2, this returns a "No Hyperlink" in that cell and since the actual
hyperlink in the "E" column
I changed it to =When(e2) this returned a "#Value!" I have formatted the
cell for date but no difference

I'm not really that sharp with this stuff, but I don't know how putting
"=When(C5)" in all my cells in column "H"
is going to work, cause it looks like it would work for row 5 only, when
column E is the hyperlink column entry
for each line item on the sheet so I this it needs to increment between
rows.
<Jeff

Jim, I think I have the hyper links correct. I did however
remove one, and use the insert from the menu, but its the
same results.

I can see that the three var's from the macro are ;
"when", "T" and "P"

If I use the formula "=T(E2)" it will return the name of the hyperlink
the way you see it in "E2".

I dont see how or where it is trying to get the date of the actual file,

I mean it looks like this will try to get the date of the hyperlink rather
than the file date.

The directory where this is C:\mcam\Work Log and the WORD doc's
that are hyperlinked are in C:\mcam\Work Log\Activity

My first though would have been to find the hyperlink name in this
directory then, get the date of the file.

Is this another way of doing this?


<Jeff






"Jim Cone" wrote in message
...
Jeff ,
The When function will not work if you created the hyperlink using a
Hyperlink formula: "=Hyperlink(...)".
It should work if the hyperlink is created using Insert | Hyperlink (from
the menu).
The cell format can remain at "General".
You might try inserting a new hyperlink and seeing if the When function
returns the date/time.

You can "fill" (down or across) by dragging the lower right corner of the
cell.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)



"Jeff W."
wrote in message
Jim I think this may be close but I'm not sure its working right and it
could be me I don't know.

I added the code to a module, and I went to line 2 in the book and entered
the formula "=When(c2)"
into H2, this returns a "No Hyperlink" in that cell and since the actual
hyperlink in the "E" column
I changed it to =When(e2) this returned a "#Value!" I have formatted the
cell for date but no difference

I'm not really that sharp with this stuff, but I don't know how putting
"=When(C5)" in all my cells in column "H"
is going to work, cause it looks like it would work for row 5 only, when
column E is the hyperlink column entry
for each line item on the sheet so I this it needs to increment between
rows.
<Jeff

Jeff,
Just so you'll know, I tried Jim's function and it works just fine if, as he
has said, the hyperlink was put there using the Insert Hyperlink method. If
you have used the =HYPERLINK() worksheet function to get the hyperlinks, his
function could be modified (in the "No Hyperlink" section) to test if the
cell's formula starts with "=HYPERLINK" and then parse out the hyperlink
portion of it and continue on in much the same fashion as it does in the
'does have hyperlink' portion of the IF block.

You're right about =When(E5) not working on rows other than 5, but he's also
given you instruction on how to 'Fill' the formula on down the sheet with the
row number changing automatically. If you need more help with that, check
out Excel's Help and search for the topic Fill Data.

"Jeff W." wrote:

Jim I think this may be close but I'm not sure its working right and it
could be me I don't know.

I added the code to a module, and I went to line 2 in the book and entered
the formula "=When(c2)"
into H2, this returns a "No Hyperlink" in that cell and since the actual
hyperlink in the "E" column
I changed it to =When(e2) this returned a "#Value!" I have formatted the
cell for date but no difference

I'm not really that sharp with this stuff, but I don't know how putting
"=When(C5)" in all my cells in column "H"
is going to work, cause it looks like it would work for row 5 only, when
column E is the hyperlink column entry
for each line item on the sheet so I this it needs to increment between
rows.

<Jeff


"Jim Cone" wrote in message
...
Jeff W.,

Enter "=When(C5)" on the worksheet, with C5 representing
the cell with the hyperlink.

Place the following code in a standard module...
'---
Function When(ByRef rng As Excel.Range) As String
Dim P As String
Dim T As Date
Application.Volatile
If rng.Hyperlinks.Count < 1 Then
When = "No Hyperlink"
Else
P = rng.Hyperlinks(1).Address
T = FileDateTime(P)
When = Format$(T, "General Date")
End If
End Function
'---
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)
(Check out "List Files")


"Jeff W."
wrote in message
I have a work log that I use to track my daily projects that is an excel
sheet
and in the fifth column I have the customers name which I have hyper
linked
to a WORD document that is in a specific directory I would like to have
eighth
cell on that same line show the saved date of the WORD file with the name
of
the
hyperlinked file this is one line of my log.

-A- -B- -C- -D- -E-
-F- -G-
-H-
(status) (item) (entry date) (invoice) (customer) (project)
(comments) (updated)
Closed 10 05/08/07 2398057 Franklin Band Saw Post
Waiting For response "the date of the word file"

If I could do this I can look at a glance and see the date of the last
activity based on the file date of the hyperlinked WORD document

All the word documents are in the same folder, I add items to this
everyday,
so the rows across just keep accumulating
I don't know if its possible to have it do this automatically update cell
"H" every time the word file gets changed so I would
always have up to date information

I'm open to suggestions, I just don't know how to get this macro in a
cell,
I mean if this is even the way this should be done
Thanks,
Jeff W.

Jeff,
T is a worksheet function that returns text from a cell.
It has nothing to do with the T in the When function.

P is the path to the file extracted from the Hyperlink.
FileDateTime returns the file date extracted from P

Unless you are using something "different" like a Mac computer or
XL4 or XL2007, I am out of ideas.

Another way of constructing the function would be to use the
FileSystemObject (as JLatham mentioned). However, you would
still have to extract the file path from the Hyperlink.
--
Jim Cone



"Jeff W."
wrote in message
Jim, I think I have the hyper links correct. I did however
remove one, and use the insert from the menu, but its the
same results.

I can see that the three var's from the macro are ;
"when", "T" and "P"

If I use the formula "=T(E2)" it will return the name of the hyperlink
the way you see it in "E2".

I dont see how or where it is trying to get the date of the actual file,

I mean it looks like this will try to get the date of the hyperlink rather
than the file date.

The directory where this is C:\mcam\Work Log and the WORD doc's
that are hyperlinked are in C:\mcam\Work Log\Activity

My first though would have been to find the hyperlink name in this
directory then, get the date of the file.

Is this another way of doing this?
<Jeff

Jeff,
Try this code for the When() function:
Function When(ByRef rng As Excel.Range) As String
Dim P As String
Dim T As Date

Application.Volatile
If rng.Hyperlinks.Count < 1 Then
'test for =HYPERLINK formula
If rng.HasFormula Then
If Left(rng.Formula, 10) = "=HYPERLINK" Then
P = Mid(rng.Formula, 13, _
InStr(13, rng.Formula, Chr$(34)) - 13)
T = FileDateTime(P)
When = Format$(T, "General Date")
Else
When = "No Hyperlink"
End If
Else
When = "No Hyperlink"
End If
Else
P = rng.Hyperlinks(1).Address
T = FileDateTime(P)
When = Format$(T, "General Date")
End If
End Function

It should handle the situation where you've used the =HYPERLINK() formula in
the cells in column E. Let us know if it works for you or not. If it
doesn't I'll go and modify the code I worked up earlier to either work with
both Insert Hyperlink type links or =HYPERLINK() type links, or to even hard
code to the specific path and just dig out the filename to use to return the
date. It's not nearly as "clean" as Jim's solution and currently requires
that you have Scripting support installed on your computer, but I may be able
to eliminate that now that Jim has introduced me to the FileDateTime()
function - brand new to me! Thanks for introducing me to it, Jim.

For you and Jim, I encountered a problem with the original function also.
When I opened the file I'd saved this morning with hyperlinks inserted using
Insert Hyperlink, all of the results of the =WHEN(E#) formula were #VALUE ...
upon investigating, I found that the hyperlinks had been converted to
relative addressing and instead of showing up as "C:\folder\folder\file.doc"
type, they were like "..\..\folder\file.doc" and that apparently causes the
error since the FileDateTime() function is expecting a 'normal'
well-formed-path to a file.

"Jeff W." wrote:

Jim, I think I have the hyper links correct. I did however
remove one, and use the insert from the menu, but its the
same results.

I can see that the three var's from the macro are ;
"when", "T" and "P"

If I use the formula "=T(E2)" it will return the name of the hyperlink
the way you see it in "E2".

I dont see how or where it is trying to get the date of the actual file,

I mean it looks like this will try to get the date of the hyperlink rather
than the file date.

The directory where this is C:\mcam\Work Log and the WORD doc's
that are hyperlinked are in C:\mcam\Work Log\Activity

My first though would have been to find the hyperlink name in this
directory then, get the date of the file.

Is this another way of doing this?


<Jeff






"Jim Cone" wrote in message
...
Jeff ,
The When function will not work if you created the hyperlink using a
Hyperlink formula: "=Hyperlink(...)".
It should work if the hyperlink is created using Insert | Hyperlink (from
the menu).
The cell format can remain at "General".
You might try inserting a new hyperlink and seeing if the When function
returns the date/time.

You can "fill" (down or across) by dragging the lower right corner of the
cell.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)



"Jeff W."
wrote in message
Jim I think this may be close but I'm not sure its working right and it
could be me I don't know.

I added the code to a module, and I went to line 2 in the book and entered
the formula "=When(c2)"
into H2, this returns a "No Hyperlink" in that cell and since the actual
hyperlink in the "E" column
I changed it to =When(e2) this returned a "#Value!" I have formatted the
cell for date but no difference

I'm not really that sharp with this stuff, but I don't know how putting
"=When(C5)" in all my cells in column "H"
is going to work, cause it looks like it would work for row 5 only, when
column E is the hyperlink column entry
for each line item on the sheet so I this it needs to increment between
rows.
<Jeff

Thank you for the response, I have removed the hyperlink then went to the
cell
and clicked on the menu (Insert), (Hyperlink) and the usual dialog box
appears

I press Link to (Existing File or Web Page)

I press Look In (Current Folder) and it shows me the activity folder where
my
docs are stored and I select the word document I want the hyperlink to go to

I think this is the way this should be, correct me if I'm wrong

The only other thing is I am running XP Pro and Excel 2003 I'm not sure if
this
makes any difference or not

I don't have any formula or macros in this work book other that what Jim
gave me
which I cut and pasted into a newly created module then on the first line of
my book
in the "H" column I add the formula and it just returns the "#VALUE!"
message

When you run it does it return the date of some thing? What date do you get?
If you have a hyperlink to a file named test.doc that you created 10/16/07
7:30
I think you would see the 10/16/07 7:30 in the cell where the formula was
entered
is this correct? cause this is what I'm trying to get.

The date time of the file name listed in the hyperlink


-E1- -H1-
Test "should be the time and date of the file
named test.doc"




Regards,

Jeff W.




"JLatham" <HelpFrom @ Jlathamsite.com.(removethis) wrote in message
...
Jeff,
Just so you'll know, I tried Jim's function and it works just fine if, as
he
has said, the hyperlink was put there using the Insert Hyperlink method.
If
you have used the =HYPERLINK() worksheet function to get the hyperlinks,
his
function could be modified (in the "No Hyperlink" section) to test if the
cell's formula starts with "=HYPERLINK" and then parse out the hyperlink
portion of it and continue on in much the same fashion as it does in the
'does have hyperlink' portion of the IF block.

You're right about =When(E5) not working on rows other than 5, but he's
also
given you instruction on how to 'Fill' the formula on down the sheet with
the
row number changing automatically. If you need more help with that, check
out Excel's Help and search for the topic Fill Data.

"Jeff W." wrote:

Jim I think this may be close but I'm not sure its working right and it
could be me I don't know.

I added the code to a module, and I went to line 2 in the book and
entered
the formula "=When(c2)"
into H2, this returns a "No Hyperlink" in that cell and since the actual
hyperlink in the "E" column
I changed it to =When(e2) this returned a "#Value!" I have formatted the
cell for date but no difference

I'm not really that sharp with this stuff, but I don't know how putting
"=When(C5)" in all my cells in column "H"
is going to work, cause it looks like it would work for row 5 only, when
column E is the hyperlink column entry
for each line item on the sheet so I this it needs to increment between
rows.

<Jeff


"Jim Cone" wrote in message
...
Jeff W.,

Enter "=When(C5)" on the worksheet, with C5 representing
the cell with the hyperlink.

Place the following code in a standard module...
'---
Function When(ByRef rng As Excel.Range) As String
Dim P As String
Dim T As Date
Application.Volatile
If rng.Hyperlinks.Count < 1 Then
When = "No Hyperlink"
Else
P = rng.Hyperlinks(1).Address
T = FileDateTime(P)
When = Format$(T, "General Date")
End If
End Function
'---
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)
(Check out "List Files")


"Jeff W."
wrote in message
I have a work log that I use to track my daily projects that is an
excel
sheet
and in the fifth column I have the customers name which I have hyper
linked
to a WORD document that is in a specific directory I would like to have
eighth
cell on that same line show the saved date of the WORD file with the
name
of
the
hyperlinked file this is one line of my log.

-A- -B- -C- -D- -E-
-F- -G-
-H-
(status) (item) (entry date) (invoice) (customer) (project)
(comments) (updated)
Closed 10 05/08/07 2398057 Franklin Band Saw
Post
Waiting For response "the date of the word file"

If I could do this I can look at a glance and see the date of the last
activity based on the file date of the hyperlinked WORD document

All the word documents are in the same folder, I add items to this
everyday,
so the rows across just keep accumulating
I don't know if its possible to have it do this automatically update
cell
"H" every time the word file gets changed so I would
always have up to date information

I'm open to suggestions, I just don't know how to get this macro in a
cell,
I mean if this is even the way this should be done
Thanks,
Jeff W.

Jeff,
I suspect you may be running into the same issue with the Insert Hyperlink
attempt that I did - that it is being converted to a relative link address,
which is confusing the function that Jim provided. His code worked fine for
me until I later opened the workbook at which time I got #VALUE on the sheet
where previously I'd had the proper date/time that the file had been
modified. Too weird!

First thing to try would be the revised Jim Cone code I posted a bit earlier
this morning - and go back to using the =HYPERLINK() formulas, it should work
for you. In the meantime, I'll work up yet another option for you using the
path you've given that should work for both Insert Hyperlink and =HYPERLINK()
setups. It'll be a bit slower than what is being used now, but that's a
relative term.

"Jeff W." wrote:

Thank you for the response, I have removed the hyperlink then went to the
cell
and clicked on the menu (Insert), (Hyperlink) and the usual dialog box
appears

I press Link to (Existing File or Web Page)

I press Look In (Current Folder) and it shows me the activity folder where
my
docs are stored and I select the word document I want the hyperlink to go to

I think this is the way this should be, correct me if I'm wrong

The only other thing is I am running XP Pro and Excel 2003 I'm not sure if
this
makes any difference or not

I don't have any formula or macros in this work book other that what Jim
gave me
which I cut and pasted into a newly created module then on the first line of
my book
in the "H" column I add the formula and it just returns the "#VALUE!"
message

When you run it does it return the date of some thing? What date do you get?
If you have a hyperlink to a file named test.doc that you created 10/16/07
7:30
I think you would see the 10/16/07 7:30 in the cell where the formula was
entered
is this correct? cause this is what I'm trying to get.

The date time of the file name listed in the hyperlink


-E1- -H1-
Test "should be the time and date of the file
named test.doc"




Regards,

Jeff W.




"JLatham" <HelpFrom @ Jlathamsite.com.(removethis) wrote in message
...
Jeff,
Just so you'll know, I tried Jim's function and it works just fine if, as
he
has said, the hyperlink was put there using the Insert Hyperlink method.
If
you have used the =HYPERLINK() worksheet function to get the hyperlinks,
his
function could be modified (in the "No Hyperlink" section) to test if the
cell's formula starts with "=HYPERLINK" and then parse out the hyperlink
portion of it and continue on in much the same fashion as it does in the
'does have hyperlink' portion of the IF block.

You're right about =When(E5) not working on rows other than 5, but he's
also
given you instruction on how to 'Fill' the formula on down the sheet with
the
row number changing automatically. If you need more help with that, check
out Excel's Help and search for the topic Fill Data.

"Jeff W." wrote:

Jim I think this may be close but I'm not sure its working right and it
could be me I don't know.

I added the code to a module, and I went to line 2 in the book and
entered
the formula "=When(c2)"
into H2, this returns a "No Hyperlink" in that cell and since the actual
hyperlink in the "E" column
I changed it to =When(e2) this returned a "#Value!" I have formatted the
cell for date but no difference

I'm not really that sharp with this stuff, but I don't know how putting
"=When(C5)" in all my cells in column "H"
is going to work, cause it looks like it would work for row 5 only, when
column E is the hyperlink column entry
for each line item on the sheet so I this it needs to increment between
rows.

<Jeff


"Jim Cone" wrote in message
...
Jeff W.,

Enter "=When(C5)" on the worksheet, with C5 representing
the cell with the hyperlink.

Place the following code in a standard module...
'---
Function When(ByRef rng As Excel.Range) As String
Dim P As String
Dim T As Date
Application.Volatile
If rng.Hyperlinks.Count < 1 Then
When = "No Hyperlink"
Else
P = rng.Hyperlinks(1).Address
T = FileDateTime(P)
When = Format$(T, "General Date")
End If
End Function
'---
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)
(Check out "List Files")


"Jeff W."
wrote in message
I have a work log that I use to track my daily projects that is an
excel
sheet
and in the fifth column I have the customers name which I have hyper
linked
to a WORD document that is in a specific directory I would like to have
eighth
cell on that same line show the saved date of the WORD file with the
name
of
the
hyperlinked file this is one line of my log.

-A- -B- -C- -D- -E-
-F- -G-
-H-
(status) (item) (entry date) (invoice) (customer) (project)
(comments) (updated)
Closed 10 05/08/07 2398057 Franklin Band Saw
Post
Waiting For response "the date of the word file"

If I could do this I can look at a glance and see the date of the last
activity based on the file date of the hyperlinked WORD document

All the word documents are in the same folder, I add items to this
everyday,
so the rows across just keep accumulating
I don't know if its possible to have it do this automatically update
cell
"H" every time the word file gets changed so I would
always have up to date information

I'm open to suggestions, I just don't know how to get this macro in a
cell,
I mean if this is even the way this should be done
Thanks,
Jeff W.

Jeff,
I've modified Jim's code to (hopefully) handle all cases:
hyperlink was created using Insert | Hyperlink
hyperlink was created with = HYPERLINK() function
hyperlink has been converted to relative address
no hyperlink exists.

Replace your current When() function in code with the one below. Initially
you'll get error indications on the worksheet because part of the change I
made is to give it a cell address as a string rather than as a Range. I did
that only to try to make setting up the formulas to use it easier for you.

Where you previously had something like =When(E2) or =When(E5) in column H,
put this formula:
=When(Address(Row(),5))
It is the same formula for all cells in column H!! What happens is that the
Row() portion always returns the row that it is in, and the 5 says to always
use column 5 (E) as the column and it creates an address from that which the
When() function will then use.

Here's the revised When() code:

Function When(ByRef rngAddress As String) As String
Const pathToFiles = "C:\mcam\Work Log\Activity\"

Dim P As String
Dim T As Date
Dim rng As Range

Set rng = ActiveSheet.Range(rngAddress)
Application.Volatile

If rng.Hyperlinks.Count < 1 Then
'test for =HYPERLINK formula
'assumption is that it EITHER has a
'hyperlink from Insert | Hyperlink, or
'is using =HYPERLINK() formula
'this could be fooled if it had a hyperlink
'inserted by Insert | Hyperlink and also has
'some formula other than the =HYPERLINK() formula
'in the cell.
If rng.HasFormula Then
If Left(rng.Formula, 10) = "=HYPERLINK" Then
P = Mid(rng.Formula, 13, _
InStr(13, rng.Formula, Chr$(34)) - 13)
T = FileDateTime(P)
When = Format$(T, "General Date")
Else
When = "No Hyperlink"
End If
Else
When = "No Hyperlink"
End If
Else
P = rng.Hyperlinks(1).Address
If Left(P, 1) = "." Then
'it is a relative address, must dig out the filename
'and use the path in constant pathToFiles to come up
'with the well formed path to use to get the date
P = pathToFiles & Right(P, Len(P) - InStrRev(P, "\"))
End If
T = FileDateTime(P)
When = Format$(T, "General Date")
End If
End Function


"Jeff W." wrote:

Thank you for the response, I have removed the hyperlink then went to the
cell
and clicked on the menu (Insert), (Hyperlink) and the usual dialog box
appears

I press Link to (Existing File or Web Page)

I press Look In (Current Folder) and it shows me the activity folder where
my
docs are stored and I select the word document I want the hyperlink to go to

I think this is the way this should be, correct me if I'm wrong

The only other thing is I am running XP Pro and Excel 2003 I'm not sure if
this
makes any difference or not

I don't have any formula or macros in this work book other that what Jim
gave me
which I cut and pasted into a newly created module then on the first line of
my book
in the "H" column I add the formula and it just returns the "#VALUE!"
message

When you run it does it return the date of some thing? What date do you get?
If you have a hyperlink to a file named test.doc that you created 10/16/07
7:30
I think you would see the 10/16/07 7:30 in the cell where the formula was
entered
is this correct? cause this is what I'm trying to get.

The date time of the file name listed in the hyperlink


-E1- -H1-
Test "should be the time and date of the file
named test.doc"




Regards,

Jeff W.




"JLatham" <HelpFrom @ Jlathamsite.com.(removethis) wrote in message
...
Jeff,
Just so you'll know, I tried Jim's function and it works just fine if, as
he
has said, the hyperlink was put there using the Insert Hyperlink method.
If
you have used the =HYPERLINK() worksheet function to get the hyperlinks,
his
function could be modified (in the "No Hyperlink" section) to test if the
cell's formula starts with "=HYPERLINK" and then parse out the hyperlink
portion of it and continue on in much the same fashion as it does in the
'does have hyperlink' portion of the IF block.

You're right about =When(E5) not working on rows other than 5, but he's
also
given you instruction on how to 'Fill' the formula on down the sheet with
the
row number changing automatically. If you need more help with that, check
out Excel's Help and search for the topic Fill Data.

"Jeff W." wrote:

Jim I think this may be close but I'm not sure its working right and it
could be me I don't know.

I added the code to a module, and I went to line 2 in the book and
entered
the formula "=When(c2)"
into H2, this returns a "No Hyperlink" in that cell and since the actual
hyperlink in the "E" column
I changed it to =When(e2) this returned a "#Value!" I have formatted the
cell for date but no difference

I'm not really that sharp with this stuff, but I don't know how putting
"=When(C5)" in all my cells in column "H"
is going to work, cause it looks like it would work for row 5 only, when
column E is the hyperlink column entry
for each line item on the sheet so I this it needs to increment between
rows.

<Jeff


"Jim Cone" wrote in message
...
Jeff W.,

Enter "=When(C5)" on the worksheet, with C5 representing
the cell with the hyperlink.

Place the following code in a standard module...
'---
Function When(ByRef rng As Excel.Range) As String
Dim P As String
Dim T As Date
Application.Volatile
If rng.Hyperlinks.Count < 1 Then
When = "No Hyperlink"
Else
P = rng.Hyperlinks(1).Address
T = FileDateTime(P)
When = Format$(T, "General Date")
End If
End Function
'---
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)
(Check out "List Files")


"Jeff W."
wrote in message
I have a work log that I use to track my daily projects that is an
excel
sheet
and in the fifth column I have the customers name which I have hyper
linked
to a WORD document that is in a specific directory I would like to have
eighth
cell on that same line show the saved date of the WORD file with the
name
of
the
hyperlinked file this is one line of my log.

-A- -B- -C- -D- -E-
-F- -G-
-H-
(status) (item) (entry date) (invoice) (customer) (project)
(comments) (updated)
Closed 10 05/08/07 2398057 Franklin Band Saw
Post
Waiting For response "the date of the word file"

If I could do this I can look at a glance and see the date of the last
activity based on the file date of the hyperlinked WORD document

All the word documents are in the same folder, I add items to this
everyday,
so the rows across just keep accumulating
I don't know if its possible to have it do this automatically update
cell
"H" every time the word file gets changed so I would
always have up to date information

I'm open to suggestions, I just don't know how to get this macro in a
cell,
I mean if this is even the way this should be done
Thanks,
Jeff W.

I get the same result, FYI I have a Dell computer with up to date
Windows XP Pro I have office 2003 w/ Excel 2003
and I have created a new excel document by right clicking and
selecting (New), (Excel Worksheet), I open the worksheet

I press [ATL F11] then I right click and select (insert) (Module)
I paste the code you provided, I exit the vba editor

then on a blank sheet In cell "E1", I type "Bray" then right click and
select "Hyperlink" then select the WORD document named Bray.doc

Then in the cell "H1" I type =when(e1) press enter and it returns
"#VALUE!"

You have been very patient with me on this, but if I'm doing this wrong
I don't know what it could be

The date on the file named "Bray.doc" is 10/01/2007 4:24pm and that just
doesnt come up in the "H" cell

Can you see that I'm doing something different or wrong here?

I'm not sure what the next step woulde be...

Jeff. W


"JLatham" <HelpFrom @ Jlathamsite.com.(removethis) wrote in message
...
Jeff,
I've modified Jim's code to (hopefully) handle all cases:
hyperlink was created using Insert | Hyperlink
hyperlink was created with = HYPERLINK() function
hyperlink has been converted to relative address
no hyperlink exists.

Replace your current When() function in code with the one below.
Initially
you'll get error indications on the worksheet because part of the change I
made is to give it a cell address as a string rather than as a Range. I
did
that only to try to make setting up the formulas to use it easier for you.

Where you previously had something like =When(E2) or =When(E5) in column
H,
put this formula:
=When(Address(Row(),5))
It is the same formula for all cells in column H!! What happens is that
the
Row() portion always returns the row that it is in, and the 5 says to
always
use column 5 (E) as the column and it creates an address from that which
the
When() function will then use.

Here's the revised When() code:

Function When(ByRef rngAddress As String) As String
Const pathToFiles = "C:\mcam\Work Log\Activity\"

Dim P As String
Dim T As Date
Dim rng As Range

Set rng = ActiveSheet.Range(rngAddress)
Application.Volatile

If rng.Hyperlinks.Count < 1 Then
'test for =HYPERLINK formula
'assumption is that it EITHER has a
'hyperlink from Insert | Hyperlink, or
'is using =HYPERLINK() formula
'this could be fooled if it had a hyperlink
'inserted by Insert | Hyperlink and also has
'some formula other than the =HYPERLINK() formula
'in the cell.
If rng.HasFormula Then
If Left(rng.Formula, 10) = "=HYPERLINK" Then
P = Mid(rng.Formula, 13, _
InStr(13, rng.Formula, Chr$(34)) - 13)
T = FileDateTime(P)
When = Format$(T, "General Date")
Else
When = "No Hyperlink"
End If
Else
When = "No Hyperlink"
End If
Else
P = rng.Hyperlinks(1).Address
If Left(P, 1) = "." Then
'it is a relative address, must dig out the filename
'and use the path in constant pathToFiles to come up
'with the well formed path to use to get the date
P = pathToFiles & Right(P, Len(P) - InStrRev(P, "\"))
End If
T = FileDateTime(P)
When = Format$(T, "General Date")
End If
End Function


"Jeff W." wrote:

Thank you for the response, I have removed the hyperlink then went to the
cell
and clicked on the menu (Insert), (Hyperlink) and the usual dialog box
appears

I press Link to (Existing File or Web Page)

I press Look In (Current Folder) and it shows me the activity folder
where
my
docs are stored and I select the word document I want the hyperlink to go
to

I think this is the way this should be, correct me if I'm wrong

The only other thing is I am running XP Pro and Excel 2003 I'm not sure
if
this
makes any difference or not

I don't have any formula or macros in this work book other that what Jim
gave me
which I cut and pasted into a newly created module then on the first line
of
my book
in the "H" column I add the formula and it just returns the "#VALUE!"
message

When you run it does it return the date of some thing? What date do you
get?
If you have a hyperlink to a file named test.doc that you created
10/16/07
7:30
I think you would see the 10/16/07 7:30 in the cell where the formula was
entered
is this correct? cause this is what I'm trying to get.

The date time of the file name listed in the hyperlink


-E1- -H1-
Test "should be the time and date of the
file
named test.doc"




Regards,

Jeff W.




"JLatham" <HelpFrom @ Jlathamsite.com.(removethis) wrote in message
...
Jeff,
Just so you'll know, I tried Jim's function and it works just fine if,
as
he
has said, the hyperlink was put there using the Insert Hyperlink
method.
If
you have used the =HYPERLINK() worksheet function to get the
hyperlinks,
his
function could be modified (in the "No Hyperlink" section) to test if
the
cell's formula starts with "=HYPERLINK" and then parse out the
hyperlink
portion of it and continue on in much the same fashion as it does in
the
'does have hyperlink' portion of the IF block.

You're right about =When(E5) not working on rows other than 5, but he's
also
given you instruction on how to 'Fill' the formula on down the sheet
with
the
row number changing automatically. If you need more help with that,
check
out Excel's Help and search for the topic Fill Data.

"Jeff W." wrote:

Jim I think this may be close but I'm not sure its working right and
it
could be me I don't know.

I added the code to a module, and I went to line 2 in the book and
entered
the formula "=When(c2)"
into H2, this returns a "No Hyperlink" in that cell and since the
actual
hyperlink in the "E" column
I changed it to =When(e2) this returned a "#Value!" I have formatted
the
cell for date but no difference

I'm not really that sharp with this stuff, but I don't know how
putting
"=When(C5)" in all my cells in column "H"
is going to work, cause it looks like it would work for row 5 only,
when
column E is the hyperlink column entry
for each line item on the sheet so I this it needs to increment
between
rows.

<Jeff


"Jim Cone" wrote in message
...
Jeff W.,

Enter "=When(C5)" on the worksheet, with C5 representing
the cell with the hyperlink.

Place the following code in a standard module...
'---
Function When(ByRef rng As Excel.Range) As String
Dim P As String
Dim T As Date
Application.Volatile
If rng.Hyperlinks.Count < 1 Then
When = "No Hyperlink"
Else
P = rng.Hyperlinks(1).Address
T = FileDateTime(P)
When = Format$(T, "General Date")
End If
End Function
'---
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)
(Check out "List Files")


"Jeff W."
wrote in message
I have a work log that I use to track my daily projects that is an
excel
sheet
and in the fifth column I have the customers name which I have hyper
linked
to a WORD document that is in a specific directory I would like to
have
eighth
cell on that same line show the saved date of the WORD file with the
name
of
the
hyperlinked file this is one line of my log.

-A- -B- -C- -D- -E-
-F- -G-
-H-
(status) (item) (entry date) (invoice) (customer)
(project)
(comments) (updated)
Closed 10 05/08/07 2398057 Franklin Band
Saw
Post
Waiting For response "the date of the word file"

If I could do this I can look at a glance and see the date of the
last
activity based on the file date of the hyperlinked WORD document

All the word documents are in the same folder, I add items to this
everyday,
so the rows across just keep accumulating
I don't know if its possible to have it do this automatically update
cell
"H" every time the word file gets changed so I would
always have up to date information

I'm open to suggestions, I just don't know how to get this macro in
a
cell,
I mean if this is even the way this should be done
Thanks,
Jeff W.

Jeff,
Not a problem, I added some confusion to the deal when I changed the When()
code.

Instead of the =when(E1)
formula over in cell H1, type this formula into H1:
=when(Address(Row(),5))
The date should show up then.

This formula never changes - it will be the same in H2, H3, H4, H5, etc. So
you can easily fill it down the sheet or even use it in cells that are
anwhere on the sheet (except column E - as that would create a circular
reference error).

"Jeff W." wrote:

I get the same result, FYI I have a Dell computer with up to date
Windows XP Pro I have office 2003 w/ Excel 2003
and I have created a new excel document by right clicking and
selecting (New), (Excel Worksheet), I open the worksheet

I press [ATL F11] then I right click and select (insert) (Module)
I paste the code you provided, I exit the vba editor

then on a blank sheet In cell "E1", I type "Bray" then right click and
select "Hyperlink" then select the WORD document named Bray.doc

Then in the cell "H1" I type =when(e1) press enter and it returns
"#VALUE!"

You have been very patient with me on this, but if I'm doing this wrong
I don't know what it could be

The date on the file named "Bray.doc" is 10/01/2007 4:24pm and that just
doesnt come up in the "H" cell

Can you see that I'm doing something different or wrong here?

I'm not sure what the next step woulde be...

Jeff. W


"JLatham" <HelpFrom @ Jlathamsite.com.(removethis) wrote in message
...
Jeff,
I've modified Jim's code to (hopefully) handle all cases:
hyperlink was created using Insert | Hyperlink
hyperlink was created with = HYPERLINK() function
hyperlink has been converted to relative address
no hyperlink exists.

Replace your current When() function in code with the one below.
Initially
you'll get error indications on the worksheet because part of the change I
made is to give it a cell address as a string rather than as a Range. I
did
that only to try to make setting up the formulas to use it easier for you.

Where you previously had something like =When(E2) or =When(E5) in column
H,
put this formula:
=When(Address(Row(),5))
It is the same formula for all cells in column H!! What happens is that
the
Row() portion always returns the row that it is in, and the 5 says to
always
use column 5 (E) as the column and it creates an address from that which
the
When() function will then use.

Here's the revised When() code:

Function When(ByRef rngAddress As String) As String
Const pathToFiles = "C:\mcam\Work Log\Activity\"

Dim P As String
Dim T As Date
Dim rng As Range

Set rng = ActiveSheet.Range(rngAddress)
Application.Volatile

If rng.Hyperlinks.Count < 1 Then
'test for =HYPERLINK formula
'assumption is that it EITHER has a
'hyperlink from Insert | Hyperlink, or
'is using =HYPERLINK() formula
'this could be fooled if it had a hyperlink
'inserted by Insert | Hyperlink and also has
'some formula other than the =HYPERLINK() formula
'in the cell.
If rng.HasFormula Then
If Left(rng.Formula, 10) = "=HYPERLINK" Then
P = Mid(rng.Formula, 13, _
InStr(13, rng.Formula, Chr$(34)) - 13)
T = FileDateTime(P)
When = Format$(T, "General Date")
Else
When = "No Hyperlink"
End If
Else
When = "No Hyperlink"
End If
Else
P = rng.Hyperlinks(1).Address
If Left(P, 1) = "." Then
'it is a relative address, must dig out the filename
'and use the path in constant pathToFiles to come up
'with the well formed path to use to get the date
P = pathToFiles & Right(P, Len(P) - InStrRev(P, "\"))
End If
T = FileDateTime(P)
When = Format$(T, "General Date")
End If
End Function


"Jeff W." wrote:

Thank you for the response, I have removed the hyperlink then went to the
cell
and clicked on the menu (Insert), (Hyperlink) and the usual dialog box
appears

I press Link to (Existing File or Web Page)

I press Look In (Current Folder) and it shows me the activity folder
where
my
docs are stored and I select the word document I want the hyperlink to go
to

I think this is the way this should be, correct me if I'm wrong

The only other thing is I am running XP Pro and Excel 2003 I'm not sure
if
this
makes any difference or not

I don't have any formula or macros in this work book other that what Jim
gave me
which I cut and pasted into a newly created module then on the first line
of
my book
in the "H" column I add the formula and it just returns the "#VALUE!"
message

When you run it does it return the date of some thing? What date do you
get?
If you have a hyperlink to a file named test.doc that you created
10/16/07
7:30
I think you would see the 10/16/07 7:30 in the cell where the formula was
entered
is this correct? cause this is what I'm trying to get.

The date time of the file name listed in the hyperlink


-E1- -H1-
Test "should be the time and date of the
file
named test.doc"




Regards,

Jeff W.




"JLatham" <HelpFrom @ Jlathamsite.com.(removethis) wrote in message
...
Jeff,
Just so you'll know, I tried Jim's function and it works just fine if,
as
he
has said, the hyperlink was put there using the Insert Hyperlink
method.
If
you have used the =HYPERLINK() worksheet function to get the
hyperlinks,
his
function could be modified (in the "No Hyperlink" section) to test if
the
cell's formula starts with "=HYPERLINK" and then parse out the
hyperlink
portion of it and continue on in much the same fashion as it does in
the
'does have hyperlink' portion of the IF block.

You're right about =When(E5) not working on rows other than 5, but he's
also
given you instruction on how to 'Fill' the formula on down the sheet
with
the
row number changing automatically. If you need more help with that,
check
out Excel's Help and search for the topic Fill Data.

"Jeff W." wrote:

Jim I think this may be close but I'm not sure its working right and
it
could be me I don't know.

I added the code to a module, and I went to line 2 in the book and
entered
the formula "=When(c2)"
into H2, this returns a "No Hyperlink" in that cell and since the
actual
hyperlink in the "E" column
I changed it to =When(e2) this returned a "#Value!" I have formatted
the
cell for date but no difference

I'm not really that sharp with this stuff, but I don't know how
putting
"=When(C5)" in all my cells in column "H"
is going to work, cause it looks like it would work for row 5 only,
when
column E is the hyperlink column entry
for each line item on the sheet so I this it needs to increment
between
rows.

<Jeff


"Jim Cone" wrote in message
...
Jeff W.,

Enter "=When(C5)" on the worksheet, with C5 representing
the cell with the hyperlink.

Place the following code in a standard module...
'---
Function When(ByRef rng As Excel.Range) As String
Dim P As String
Dim T As Date
Application.Volatile
If rng.Hyperlinks.Count < 1 Then
When = "No Hyperlink"
Else
P = rng.Hyperlinks(1).Address
T = FileDateTime(P)
When = Format$(T, "General Date")
End If
End Function
'---
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)
(Check out "List Files")


"Jeff W."
wrote in message
I have a work log that I use to track my daily projects that is an
excel
sheet
and in the fifth column I have the customers name which I have hyper
linked
to a WORD document that is in a specific directory I would like to
have
eighth
cell on that same line show the saved date of the WORD file with the
name
of
the
hyperlinked file this is one line of my log.

-A- -B- -C- -D- -E-
-F- -G-
-H-
(status) (item) (entry date) (invoice) (customer)
(project)
(comments) (updated)
Closed 10 05/08/07 2398057 Franklin Band
Saw
Post
Waiting For response "the date of the word file"

If I could do this I can look at a glance and see the date of the
last
activity based on the file date of the hyperlinked WORD document

All the word documents are in the same folder, I add items to this
everyday,
so the rows across just keep accumulating
I don't know if its possible to have it do this automatically update
cell
"H" every time the word file gets changed so I would
always have up to date information

I'm open to suggestions, I just don't know how to get this macro in
a
cell,
I mean if this is even the way this should be done
Thanks,
Jeff W.

I saw that change in the formula and I get the same result when
I copy and paste " =when(Address(Row(),5))" into the "H" cell

I dont understand why this wont work on my blank workbook.

I wonder if we shouldnt go about this differntly, I would rather that
it worked here the way it seems to be working there, but

How about a sub/macro that looked at the file name of the value of the
hyperlink in the given directory and retuned the time and date that way

I mean if this wont work here the way you have done it for me, I dont
know what else to do. if I had a sub/macro to do this another way I can
make it run and update on open or I could even add a menu selection that
I could update it manually with

I could add the menu selection to run a macro and even make it run on open

I guess this is better than nothing, right. I dont understand it, this is
why
I opened blank workbook in that same directory and enterd only what you
told me too, to make sure that nothing else was keeping this from running


Thanks,

Jeff W.


"JLatham" <HelpFrom @ Jlathamsite.com.(removethis) wrote in message
...
Jeff,
Not a problem, I added some confusion to the deal when I changed the
When()
code.

Instead of the =when(E1)
formula over in cell H1, type this formula into H1:
=when(Address(Row(),5))
The date should show up then.

This formula never changes - it will be the same in H2, H3, H4, H5, etc.
So
you can easily fill it down the sheet or even use it in cells that are
anwhere on the sheet (except column E - as that would create a circular
reference error).

"Jeff W." wrote:

I get the same result, FYI I have a Dell computer with up to date
Windows XP Pro I have office 2003 w/ Excel 2003
and I have created a new excel document by right clicking and
selecting (New), (Excel Worksheet), I open the worksheet

I press [ATL F11] then I right click and select (insert) (Module)
I paste the code you provided, I exit the vba editor

then on a blank sheet In cell "E1", I type "Bray" then right click and
select "Hyperlink" then select the WORD document named Bray.doc

Then in the cell "H1" I type =when(e1) press enter and it returns
"#VALUE!"

You have been very patient with me on this, but if I'm doing this wrong
I don't know what it could be

The date on the file named "Bray.doc" is 10/01/2007 4:24pm and that just
doesnt come up in the "H" cell

Can you see that I'm doing something different or wrong here?

I'm not sure what the next step woulde be...

Jeff. W


"JLatham" <HelpFrom @ Jlathamsite.com.(removethis) wrote in message
...
Jeff,
I've modified Jim's code to (hopefully) handle all cases:
hyperlink was created using Insert | Hyperlink
hyperlink was created with = HYPERLINK() function
hyperlink has been converted to relative address
no hyperlink exists.

Replace your current When() function in code with the one below.
Initially
you'll get error indications on the worksheet because part of the
change I
made is to give it a cell address as a string rather than as a Range.
I
did
that only to try to make setting up the formulas to use it easier for
you.

Where you previously had something like =When(E2) or =When(E5) in
column
H,
put this formula:
=When(Address(Row(),5))
It is the same formula for all cells in column H!! What happens is
that
the
Row() portion always returns the row that it is in, and the 5 says to
always
use column 5 (E) as the column and it creates an address from that
which
the
When() function will then use.

Here's the revised When() code:

Function When(ByRef rngAddress As String) As String
Const pathToFiles = "C:\mcam\Work Log\Activity\"

Dim P As String
Dim T As Date
Dim rng As Range

Set rng = ActiveSheet.Range(rngAddress)
Application.Volatile

If rng.Hyperlinks.Count < 1 Then
'test for =HYPERLINK formula
'assumption is that it EITHER has a
'hyperlink from Insert | Hyperlink, or
'is using =HYPERLINK() formula
'this could be fooled if it had a hyperlink
'inserted by Insert | Hyperlink and also has
'some formula other than the =HYPERLINK() formula
'in the cell.
If rng.HasFormula Then
If Left(rng.Formula, 10) = "=HYPERLINK" Then
P = Mid(rng.Formula, 13, _
InStr(13, rng.Formula, Chr$(34)) - 13)
T = FileDateTime(P)
When = Format$(T, "General Date")
Else
When = "No Hyperlink"
End If
Else
When = "No Hyperlink"
End If
Else
P = rng.Hyperlinks(1).Address
If Left(P, 1) = "." Then
'it is a relative address, must dig out the filename
'and use the path in constant pathToFiles to come up
'with the well formed path to use to get the date
P = pathToFiles & Right(P, Len(P) - InStrRev(P, "\"))
End If
T = FileDateTime(P)
When = Format$(T, "General Date")
End If
End Function


"Jeff W." wrote:

Thank you for the response, I have removed the hyperlink then went to
the
cell
and clicked on the menu (Insert), (Hyperlink) and the usual dialog box
appears

I press Link to (Existing File or Web Page)

I press Look In (Current Folder) and it shows me the activity folder
where
my
docs are stored and I select the word document I want the hyperlink to
go
to

I think this is the way this should be, correct me if I'm wrong

The only other thing is I am running XP Pro and Excel 2003 I'm not
sure
if
this
makes any difference or not

I don't have any formula or macros in this work book other that what
Jim
gave me
which I cut and pasted into a newly created module then on the first
line
of
my book
in the "H" column I add the formula and it just returns the "#VALUE!"
message

When you run it does it return the date of some thing? What date do
you
get?
If you have a hyperlink to a file named test.doc that you created
10/16/07
7:30
I think you would see the 10/16/07 7:30 in the cell where the formula
was
entered
is this correct? cause this is what I'm trying to get.

The date time of the file name listed in the hyperlink



-H1-
Test "should be the time and date of the
file
named test.doc"




Regards,

Jeff W.




"JLatham" <HelpFrom @ Jlathamsite.com.(removethis) wrote in message
...
Jeff,
Just so you'll know, I tried Jim's function and it works just fine
if,
as
he
has said, the hyperlink was put there using the Insert Hyperlink
method.
If
you have used the =HYPERLINK() worksheet function to get the
hyperlinks,
his
function could be modified (in the "No Hyperlink" section) to test
if
the
cell's formula starts with "=HYPERLINK" and then parse out the
hyperlink
portion of it and continue on in much the same fashion as it does in
the
'does have hyperlink' portion of the IF block.

You're right about =When(E5) not working on rows other than 5, but
he's
also
given you instruction on how to 'Fill' the formula on down the sheet
with
the
row number changing automatically. If you need more help with that,
check
out Excel's Help and search for the topic Fill Data.

"Jeff W." wrote:

Jim I think this may be close but I'm not sure its working right
and
it
could be me I don't know.

I added the code to a module, and I went to line 2 in the book and
entered
the formula "=When(c2)"
into H2, this returns a "No Hyperlink" in that cell and since the
actual
hyperlink in the "E" column
I changed it to =When(e2) this returned a "#Value!" I have
formatted
the
cell for date but no difference

I'm not really that sharp with this stuff, but I don't know how
putting
"=When(C5)" in all my cells in column "H"
is going to work, cause it looks like it would work for row 5 only,
when
column E is the hyperlink column entry
for each line item on the sheet so I this it needs to increment
between
rows.

<Jeff


"Jim Cone" wrote in message
...
Jeff W.,

Enter "=When(C5)" on the worksheet, with C5 representing
the cell with the hyperlink.

Place the following code in a standard module...
'---
Function When(ByRef rng As Excel.Range) As String
Dim P As String
Dim T As Date
Application.Volatile
If rng.Hyperlinks.Count < 1 Then
When = "No Hyperlink"
Else
P = rng.Hyperlinks(1).Address
T = FileDateTime(P)
When = Format$(T, "General Date")
End If
End Function
'---
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)
(Check out "List Files")


"Jeff W."
wrote in message
I have a work log that I use to track my daily projects that is
an
excel
sheet
and in the fifth column I have the customers name which I have
hyper
linked
to a WORD document that is in a specific directory I would like
to
have
eighth
cell on that same line show the saved date of the WORD file with
the
name
of
the
hyperlinked file this is one line of my log.

-A- -B- -C- -D- -E-
-F- -G-
-H-
(status) (item) (entry date) (invoice) (customer)
(project)
(comments) (updated)
Closed 10 05/08/07 2398057 Franklin Band
Saw
Post
Waiting For response "the date of the word file"

If I could do this I can look at a glance and see the date of the
last
activity based on the file date of the hyperlinked WORD document

All the word documents are in the same folder, I add items to
this
everyday,
so the rows across just keep accumulating
I don't know if its possible to have it do this automatically
update
cell
"H" every time the word file gets changed so I would
always have up to date information

I'm open to suggestions, I just don't know how to get this macro
in
a
cell,
I mean if this is even the way this should be done
Thanks,
Jeff W.

Jeff, Let's try it this way. Click the link below and save the file to your
hard drive and then set up some entries in column E on Sheet1 and see if you
don't get some results over in column H. I've already tested this at my end
and it works fine. If it gives you troubles, there are instructions on the
sheet on how to get in touch with me.

http://www.jlathamsite.com/uploads/G...Hyperlinks.xls

It's an Excel 2003 workbook and the machine it was running on has Windows
XP, so there shouldn't be any surprises there.

By the way - what is your macro security set to? To check, use this
sequence in the main menu of Excel:
Tools | Macro | Security
Should be set to either Medium (my recommendation) or Low (unsafe) in order
to run the code. If you have to change it, after changing it, close and
reopen Excel - the changes are not put into effect until after it's closed
and reopened.


"Jeff W." wrote:

I saw that change in the formula and I get the same result when
I copy and paste " =when(Address(Row(),5))" into the "H" cell

I dont understand why this wont work on my blank workbook.

I wonder if we shouldnt go about this differntly, I would rather that
it worked here the way it seems to be working there, but

How about a sub/macro that looked at the file name of the value of the
hyperlink in the given directory and retuned the time and date that way

I mean if this wont work here the way you have done it for me, I dont
know what else to do. if I had a sub/macro to do this another way I can
make it run and update on open or I could even add a menu selection that
I could update it manually with

I could add the menu selection to run a macro and even make it run on open

I guess this is better than nothing, right. I dont understand it, this is
why
I opened blank workbook in that same directory and enterd only what you
told me too, to make sure that nothing else was keeping this from running


Thanks,

Jeff W.


"JLatham" <HelpFrom @ Jlathamsite.com.(removethis) wrote in message
...
Jeff,
Not a problem, I added some confusion to the deal when I changed the
When()
code.

Instead of the =when(E1)
formula over in cell H1, type this formula into H1:
=when(Address(Row(),5))
The date should show up then.

This formula never changes - it will be the same in H2, H3, H4, H5, etc.
So
you can easily fill it down the sheet or even use it in cells that are
anwhere on the sheet (except column E - as that would create a circular
reference error).

"Jeff W." wrote:

I get the same result, FYI I have a Dell computer with up to date
Windows XP Pro I have office 2003 w/ Excel 2003
and I have created a new excel document by right clicking and
selecting (New), (Excel Worksheet), I open the worksheet

I press [ATL F11] then I right click and select (insert) (Module)
I paste the code you provided, I exit the vba editor

then on a blank sheet In cell "E1", I type "Bray" then right click and
select "Hyperlink" then select the WORD document named Bray.doc

Then in the cell "H1" I type =when(e1) press enter and it returns
"#VALUE!"

You have been very patient with me on this, but if I'm doing this wrong
I don't know what it could be

The date on the file named "Bray.doc" is 10/01/2007 4:24pm and that just
doesnt come up in the "H" cell

Can you see that I'm doing something different or wrong here?

I'm not sure what the next step woulde be...

Jeff. W


"JLatham" <HelpFrom @ Jlathamsite.com.(removethis) wrote in message
...
Jeff,
I've modified Jim's code to (hopefully) handle all cases:
hyperlink was created using Insert | Hyperlink
hyperlink was created with = HYPERLINK() function
hyperlink has been converted to relative address
no hyperlink exists.

Replace your current When() function in code with the one below.
Initially
you'll get error indications on the worksheet because part of the
change I
made is to give it a cell address as a string rather than as a Range.
I
did
that only to try to make setting up the formulas to use it easier for
you.

Where you previously had something like =When(E2) or =When(E5) in
column
H,
put this formula:
=When(Address(Row(),5))
It is the same formula for all cells in column H!! What happens is
that
the
Row() portion always returns the row that it is in, and the 5 says to
always
use column 5 (E) as the column and it creates an address from that
which
the
When() function will then use.

Here's the revised When() code:

Function When(ByRef rngAddress As String) As String
Const pathToFiles = "C:\mcam\Work Log\Activity\"

Dim P As String
Dim T As Date
Dim rng As Range

Set rng = ActiveSheet.Range(rngAddress)
Application.Volatile

If rng.Hyperlinks.Count < 1 Then
'test for =HYPERLINK formula
'assumption is that it EITHER has a
'hyperlink from Insert | Hyperlink, or
'is using =HYPERLINK() formula
'this could be fooled if it had a hyperlink
'inserted by Insert | Hyperlink and also has
'some formula other than the =HYPERLINK() formula
'in the cell.
If rng.HasFormula Then
If Left(rng.Formula, 10) = "=HYPERLINK" Then
P = Mid(rng.Formula, 13, _
InStr(13, rng.Formula, Chr$(34)) - 13)
T = FileDateTime(P)
When = Format$(T, "General Date")
Else
When = "No Hyperlink"
End If
Else
When = "No Hyperlink"
End If
Else
P = rng.Hyperlinks(1).Address
If Left(P, 1) = "." Then
'it is a relative address, must dig out the filename
'and use the path in constant pathToFiles to come up
'with the well formed path to use to get the date
P = pathToFiles & Right(P, Len(P) - InStrRev(P, "\"))
End If
T = FileDateTime(P)
When = Format$(T, "General Date")
End If
End Function


"Jeff W." wrote:

Thank you for the response, I have removed the hyperlink then went to
the
cell
and clicked on the menu (Insert), (Hyperlink) and the usual dialog box
appears

I press Link to (Existing File or Web Page)

I press Look In (Current Folder) and it shows me the activity folder
where
my
docs are stored and I select the word document I want the hyperlink to
go
to

I think this is the way this should be, correct me if I'm wrong

The only other thing is I am running XP Pro and Excel 2003 I'm not
sure
if
this
makes any difference or not

I don't have any formula or macros in this work book other that what
Jim
gave me
which I cut and pasted into a newly created module then on the first
line
of
my book
in the "H" column I add the formula and it just returns the "#VALUE!"
message

When you run it does it return the date of some thing? What date do
you
get?
If you have a hyperlink to a file named test.doc that you created
10/16/07
7:30
I think you would see the 10/16/07 7:30 in the cell where the formula
was
entered
is this correct? cause this is what I'm trying to get.

The date time of the file name listed in the hyperlink



-H1-
Test "should be the time and date of the
file
named test.doc"




Regards,

Jeff W.




"JLatham" <HelpFrom @ Jlathamsite.com.(removethis) wrote in message
...
Jeff,
Just so you'll know, I tried Jim's function and it works just fine
if,
as
he
has said, the hyperlink was put there using the Insert Hyperlink
method.
If
you have used the =HYPERLINK() worksheet function to get the
hyperlinks,
his
function could be modified (in the "No Hyperlink" section) to test
if
the
cell's formula starts with "=HYPERLINK" and then parse out the
hyperlink
portion of it and continue on in much the same fashion as it does in
the
'does have hyperlink' portion of the IF block.

You're right about =When(E5) not working on rows other than 5, but
he's
also
given you instruction on how to 'Fill' the formula on down the sheet
with
the
row number changing automatically. If you need more help with that,
check
out Excel's Help and search for the topic Fill Data.

"Jeff W." wrote:

Jim I think this may be close but I'm not sure its working right
and
it
could be me I don't know.

I added the code to a module, and I went to line 2 in the book and
entered
the formula "=When(c2)"
into H2, this returns a "No Hyperlink" in that cell and since the
actual
hyperlink in the "E" column
I changed it to =When(e2) this returned a "#Value!" I have
formatted
the
cell for date but no difference

I'm not really that sharp with this stuff, but I don't know how
putting
"=When(C5)" in all my cells in column "H"
is going to work, cause it looks like it would work for row 5 only,
when
column E is the hyperlink column entry
for each line item on the sheet so I this it needs to increment
between
rows.

<Jeff


"Jim Cone" wrote in message
...
Jeff W.,

Enter "=When(C5)" on the worksheet, with C5 representing
the cell with the hyperlink.

Place the following code in a standard module...
'---
Function When(ByRef rng As Excel.Range) As String
Dim P As String
Dim T As Date
Application.Volatile
If rng.Hyperlinks.Count < 1 Then
When = "No Hyperlink"

Jim,
Just to let you know how things went - it's a done deal now and code is
working for him, but not as a UDF - as an "on demand" Sub. The basic problem
of all of our earlier efforts is that Excel seems to play games with
hyperlinks inserted using Insert | Hyperlink. It will sometimes change them
to a relative link as "..\..\folder\folder\file.xxx" and will sometimes put
them in as something like
"file:///\folder\file.xxx" and sometimes even reverse the \ to /. This was
confusing the FileDateTime() command and causing the #VALUE error returns.

I took the code I'd written earlier using the FSO and what you initially
provided and got rid of the use of FSO, since FileDateTime() gave me what we
needed without using scripting and FSO, and wrote code that dealt with both
types of hyperlinks: those contained within a =HYPERLINK() worksheet
function, and those inserted with Insert | Hyperlink. BUT - for the second
type, since he has all of the files in a known location on the system, I
first replaced any / characters with \ then stripping off just the filename
and prefacing it with the known folder path. I then used that path as the
argument for FileDateTime() and he says it's working just fine for him now.
The final code:

Sub GetFileDates()
'
'change these constants to match your setup
Const WSheet = "Activity" ' be sure to change this in your workbook if
needed
Const pathToFiles = "C:\mcam\Work Log\Activity\" ' my test path:
"C:\Documents and Settings\All Users\Documents\Proposals\AcademyInstr_Rebid\"
'"C:\mcam\Work Log\Activity\"
Const linkColumn = "E"
Const dateColumn = "H"
Const firstDataRow = 2 ' first row to examine for hyperlinks.

Dim dateColOffset As Integer
Dim anyAddress As String
Dim allLinkCells As Range
Dim anyCell As Range
Dim lastRow As Long
Dim LC As Integer ' loop counter
Dim anyLink As String ' this was P in the When() function
Dim filesDate As Date ' this was T in the When() function

'some preparation setup
lastRow = Range(linkColumn & Rows.Count).End(xlUp).Row
If lastRow <= firstDataRow Then
MsgBox "No possible hyperlinks to examine. Quitting.", vbOKOnly, "No
Data Entries"
Exit Sub
End If
dateColOffset = Range(dateColumn & firstDataRow).Column - _
Range(linkColumn & firstDataRow).Column
anyAddress = linkColumn & firstDataRow & ":" & linkColumn & lastRow
'reference all used cells in column E
Set allLinkCells = Worksheets(WSheet).Range(anyAddress)

'work through all possible links on the sheet/column E
For Each anyCell In allLinkCells
If anyCell.Hyperlinks.Count < 1 Then
'test for =HYPERLINK formula
If anyCell.HasFormula Then
If Left(anyCell.Formula, 10) = "=HYPERLINK" Then
anyLink = Mid(anyCell.Formula, 13, _
InStr(13, anyCell.Formula, Chr$(34)) - 13)
On Error Resume Next
filesDate = FileDateTime(anyLink)
If Err = 0 Then
anyCell.Offset(0, dateColOffset) = Format$(filesDate, "General
Date")
Else
anyCell.Offset(0, dateColOffset) = "Invalid Link Path"
Err.Clear
End If
On Error GoTo 0
Else
anyCell.Offset(0, dateColOffset) = ""
End If
Else
anyCell.Offset(0, dateColOffset) = ""
End If
Else
anyLink = anyCell.Hyperlinks(1).Address
'we are going to take ALL hyperlinks and reduce them
'to just the filename and add the contents of pathToFiles back to them!
'make sure that / gets changed to \ in it first
anyLink = Replace(anyLink, "/", Application.PathSeparator)
anyLink = pathToFiles & Right(anyLink, Len(anyLink) - _
InStrRev(anyLink, Application.PathSeparator))

On Error Resume Next
filesDate = FileDateTime(anyLink)
If Err = 0 Then
anyCell.Offset(0, dateColOffset) = Format$(filesDate, "General Date")
Else
anyCell.Offset(0, dateColOffset) = "Invalid Link Path"
Err.Clear
End If
On Error GoTo 0
End If
Next ' end of anyCell in allLinkCells loop
End Sub


"Jim Cone" wrote:

Jeff,
T is a worksheet function that returns text from a cell.
It has nothing to do with the T in the When function.

P is the path to the file extracted from the Hyperlink.
FileDateTime returns the file date extracted from P

Unless you are using something "different" like a Mac computer or
XL4 or XL2007, I am out of ideas.

Another way of constructing the function would be to use the
FileSystemObject (as JLatham mentioned). However, you would
still have to extract the file path from the Hyperlink.
--
Jim Cone



"Jeff W."
wrote in message
Jim, I think I have the hyper links correct. I did however
remove one, and use the insert from the menu, but its the
same results.

I can see that the three var's from the macro are ;
"when", "T" and "P"

If I use the formula "=T(E2)" it will return the name of the hyperlink
the way you see it in "E2".

I dont see how or where it is trying to get the date of the actual file,

I mean it looks like this will try to get the date of the hyperlink rather
than the file date.

The directory where this is C:\mcam\Work Log and the WORD doc's
that are hyperlinked are in C:\mcam\Work Log\Activity

My first though would have been to find the hyperlink name in this
directory then, get the date of the file.

Is this another way of doing this?
<Jeff

Thanks for the update.
I was going to ask for the results as the problem did puzzle me.
Much appreciated.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)



"JLatham"
<HelpFrom @ Jlathamsite.com.(removethis)
wrote in message
Jim,
Just to let you know how things went - it's a done deal now and code is
working for him, but not as a UDF - as an "on demand" Sub. The basic problem
of all of our earlier efforts is that Excel seems to play games with
hyperlinks inserted using Insert | Hyperlink. It will sometimes change them
to a relative link as "..\..\folder\folder\file.xxx" and will sometimes put
them in as something like
"file:///\folder\file.xxx" and sometimes even reverse the \ to /. This was
confusing the FileDateTime() command and causing the #VALUE error returns.

I took the code I'd written earlier using the FSO and what you initially
provided and got rid of the use of FSO, since FileDateTime() gave me what we
needed without using scripting and FSO, and wrote code that dealt with both
types of hyperlinks: those contained within a =HYPERLINK() worksheet
function, and those inserted with Insert | Hyperlink. BUT - for the second
type, since he has all of the files in a known location on the system, I
first replaced any / characters with \ then stripping off just the filename
and prefacing it with the known folder path. I then used that path as the
argument for FileDateTime() and he says it's working just fine for him now.
The final code:

Sub GetFileDates()
'
'change these constants to match your setup
Const WSheet = "Activity" ' be sure to change this in your workbook if
needed
Const pathToFiles = "C:\mcam\Work Log\Activity\" ' my test path:
"C:\Documents and Settings\All Users\Documents\Proposals\AcademyInstr_Rebid\"
'"C:\mcam\Work Log\Activity\"
Const linkColumn = "E"
Const dateColumn = "H"
Const firstDataRow = 2 ' first row to examine for hyperlinks.

Dim dateColOffset As Integer
Dim anyAddress As String
Dim allLinkCells As Range
Dim anyCell As Range
Dim lastRow As Long
Dim LC As Integer ' loop counter
Dim anyLink As String ' this was P in the When() function
Dim filesDate As Date ' this was T in the When() function

'some preparation setup
lastRow = Range(linkColumn & Rows.Count).End(xlUp).Row
If lastRow <= firstDataRow Then
MsgBox "No possible hyperlinks to examine. Quitting.", vbOKOnly, "No
Data Entries"
Exit Sub
End If
dateColOffset = Range(dateColumn & firstDataRow).Column - _
Range(linkColumn & firstDataRow).Column
anyAddress = linkColumn & firstDataRow & ":" & linkColumn & lastRow
'reference all used cells in column E
Set allLinkCells = Worksheets(WSheet).Range(anyAddress)

'work through all possible links on the sheet/column E
For Each anyCell In allLinkCells
If anyCell.Hyperlinks.Count < 1 Then
'test for =HYPERLINK formula
If anyCell.HasFormula Then
If Left(anyCell.Formula, 10) = "=HYPERLINK" Then
anyLink = Mid(anyCell.Formula, 13, _
InStr(13, anyCell.Formula, Chr$(34)) - 13)
On Error Resume Next
filesDate = FileDateTime(anyLink)
If Err = 0 Then
anyCell.Offset(0, dateColOffset) = Format$(filesDate, "General
Date")
Else
anyCell.Offset(0, dateColOffset) = "Invalid Link Path"
Err.Clear
End If
On Error GoTo 0
Else
anyCell.Offset(0, dateColOffset) = ""
End If
Else
anyCell.Offset(0, dateColOffset) = ""
End If
Else
anyLink = anyCell.Hyperlinks(1).Address
'we are going to take ALL hyperlinks and reduce them
'to just the filename and add the contents of pathToFiles back to them!
'make sure that / gets changed to \ in it first
anyLink = Replace(anyLink, "/", Application.PathSeparator)
anyLink = pathToFiles & Right(anyLink, Len(anyLink) - _
InStrRev(anyLink, Application.PathSeparator))

On Error Resume Next
filesDate = FileDateTime(anyLink)
If Err = 0 Then
anyCell.Offset(0, dateColOffset) = Format$(filesDate, "General Date")
Else
anyCell.Offset(0, dateColOffset) = "Invalid Link Path"
Err.Clear
End If
On Error GoTo 0
End If
Next ' end of anyCell in allLinkCells loop
End Sub