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.