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.