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.

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.

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.

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,
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

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.

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.