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