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