Hyperlink File Path Changes. Hair loss imminent.

I have an excel workbook (Office 2000) which contains a column of
hyperlinks on several sheets. To word documents that were organised in
a file tree like the following example:

c:\DOCUMENTS\SHARED\IMAGES\2003\MARCH\item_serial_ mar_03_001

There are many files linked to this path or similar, the only
differance being the 'Year or Month' folder. As per the date that the
image was taken on. There are around 300 files all varying in months/
years, all hyperlinked succesefully.

The spreadsheet displays the hyperlinks as 'item/serial/mar/03/001' in
each cell.

The file path has changed slightly to:

d:\DOCUMENTS\SHARED\IMAGES\

Now the Spreadsheet and ALL of the files regardless of year/month are
stored in this folder, so we've effectivly removed steps 4 and 5 of
the file tree. (The year and month) This has, as expected broken all
of the hyperlinks. Upon moving the files they now default link to d:\
but still retain their now non-exsistant year and month sub folder
steps.

I was hoping there would be someone able to shed light on what is
required in order to 'fix' all of the hyperlinks on a sheet by way of
VBA code. Be it one that removes steps of a file path or just
replacing the current ones with d:\DOCUMENTS\SHARED\IMAGES\ but
retaining the filename as they haven't changed. A further point to
note with this is that the text displayed in the cells is in the
forwardslash format whereas the files are named with underscores.

I've tried David McRitchies site with no luck and trawled here and
found similar threads but none that have worked.

I eagerly await some guidance. Cheers.

I dont know if this will help with a solution, but to fix one link all
that I have to do is:

Right click the cell
-Hyperlink
-Edit Hyperlink...

Then delete the year and month from the filepath box.

so: '\2003\MARCH\item_serial_mar_03_001'
becomes: 'item_serial_mar_03_001'

Interestingly the dialog box only shows the folders steps above, not
the complete filepath, which isn't a problem.

Idividually the problem is an easy fix. I was hoping for some useful
code that allowed me to do the whole worksheet in a click as doing
this 300+ times is highly inconvieniant. Ta again.

I guess the spreadsheet containing the hyperlinks is located in the
IMAGES folder. The hyperlinks are relative to the location of the
spreadsheet.

Something like this might do it:

Sub ChangeHype()
Dim WS As Worksheet
Dim H As Hyperlink
For Each WS In ActiveWorkbook.Worksheets
For Each H In WS.Hyperlinks
' change it to start after the last \
H.Address = Mid(H.Address,InStrRev(H.Address,"\"))
Next
Next
End Sub

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup

On Aug 21, 5:10*pm, Bill Manville wrote:
I guess the spreadsheet containing the hyperlinks is located in the
IMAGES folder. *The hyperlinks are relative to the location of the
spreadsheet.

Something like this might do it:

Sub ChangeHype()
* Dim WS As Worksheet
* Dim H As Hyperlink
* For Each WS In ActiveWorkbook.Worksheets
* * For Each H In WS.Hyperlinks
* * * ' change it to start after the last \
* * * H.Address = Mid(H.Address,InStrRev(H.Address,"\"))
* * Next
* Next
End Sub

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup


Thats correct the Spreadsheet is now in the folder along with all of
the images. I tried the above and got a Runtime error '5' "invalid
procedure call or argument.

I also tried different filepaths in this line:

H.Address = Mid(H.Address,InStrRev(H.Address,"\"))

such as H.Address = Mid(H.Address,InStrRev(H.Address,"\images"))

"d:\documents\shared\images"

with and without the final backslash, am I missing something or is my
problem greater than this?

Really appreciate your input.

I guess we need to see what H.Address is.
Put a
Debug.Print H.Address
before the H.Address =... line and see what it shows in the debug
window.

If that doesn't give you any clues, post what it gives you in your
reply here and I will see if it gives me any.

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup

On Sep 3, 2:27*pm, Bill Manville wrote:
I guess we need to see what H.Address is.
Put a
* Debug.Print H.Address
before the H.Address =... line and see what it shows in the debug
window.

If that doesn't give you any clues, post what it gives you in your
reply here and I will see if it gives me any.

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup

Still the same error, no further info really....

Sub ChangeHype()
Dim WS As Worksheet
Dim H As Hyperlink
For Each WS In ActiveWorkbook.Worksheets
For Each H In WS.Hyperlinks
Debug.Print H.Address
H.Address = Mid(H.Address, InStrRev(H.Address,
"\Images")) ------ is the line the debug shows as the error.
Next
Next
End Sub


I was thinking of an alternate route of doing this when i thought
crossed my mind. Is there a way of mass CREATING hyperlinks, as now
all the files are in the same folder surely we could create a VBA that
links whatever filename is in the cell with the filename that
correspondes to in the folder.

I know i had the problem that all the image filenames were in the
***_***_***_*** format and all the cells are in the ***/***/***/***
but I can change this en mass with a simple find and replace '/' with
'_' thus matching the cells to the filenames. Then only needing to
create a crap load of hyperlinks again. There anyway of doing that via
VBA?

Thanks again for your time.

So what does Debug.Print put in the immediate window?

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup

On Sep 9, 1:56*pm, Bill Manville wrote:
So what does Debug.Print put in the immediate window?

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup

Nothing. There is no change other than the pop up dialog box.

Davius wrote:
Nothing. There is no change other than the pop up dialog box.


That is very surprising.
A hyperlink with no Address.
Are you sure you looked in the Immediate Window of the VB editor to see
what Debug.Print had printed.
If you don't understand that question, change Debug.Print to MsgBox

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup

On Sep 9, 10:04*pm, Bill Manville wrote:
Davius wrote:
Nothing. There is no change other than the pop up dialog box.

That is very surprising.
A hyperlink with no Address.
Are you sure you looked in the Immediate Window of the VB editor to see
what Debug.Print had printed.
If you don't understand that question, change Debug.Print to MsgBox

Sorry my knowledge is poor, i found the immediate window via Ctrl-G.
Upon running the macro: 'item_serial_mar_03_001.jpeg' is displayed.
Which is the first file in the folder '\images'.