follow hyperlink change file location

[bobbuilder]

I have an excel spread sheet that contains hyperlinks to word files once the word file is no longer needed I need the hyperlink to follow to that word file rename it to put it in another folder I am really struggling to find a solution to this these files are on a local drive.

[SamT]

how can we tell that the word file "is no longer needed?"

[bobbuilder]

If the cell changes from a date to comp I really just need the way to follow the hyperlink to the file then change the location to another folder

[SamT]

Worksheet_Change Event
Get Hypelink Address
Use msSripting to move file

[bobbuilder]

If I sent you the excel file and the word documents could I ask you for some more help

[SamT]

Don't really even need that.

Just need to know what column is the data/comp in, and what column is the hyperlink in

and the file Rename Pattern.

Here's the VBA keywords you need to try it yourself. Type one of them into the VBA editor, leave the cursor in or next to the word and press F.



Worksheet. look at "Events; Change"

Target is a Range with all its properties and methods

Offset
Hyperlink
Column

[If Not Target.Column is Columns(2) Then Exit Sub]


Count

[If Target.Count > 1 Then Exit Sub]




Hyperlink. Follow the "Excel" choice first. The "Excel(Object) choice next

Under Hyperlink Object see Properties:

Address. Compare to Name below.

[WordDoc = Target.Offset(0 ,1).Hyperlink.Address]




Name. In VBA help

[WordDoc.Name = "NewDir\NewName"]


Dir. Directory or folder
Kill. Deletes a file

[WordDoc.Kill]

[bobbuilder]

Ill try that thanks so much