Hyperlink File Path Changes. Hair loss imminent.

[Davius]

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.

[Davius]

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.

[Bill Manville]

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

[Davius]

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.

[Bill Manville]

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.

[Davius]

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.

[Bill Manville]

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

[Davius]

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

[Bill Manville]

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

[Davius]

On Sep 9, 10:04 pm, Bill Manville <Bill-Manvi...@msn.com> 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'.

[Davius]

So it looks like its trying to establish a hyperlink to the first
file, rather than fixing the hyperlink of the old one. I can change
the cell contents to match the filenames on en mass if this helps.

[Bill Manville]

OK, so that was a hyperlink you previously fixed.
I assumed you hadn't fixed any of them.
Try this:

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 \

If InStr(H.Address,"\")>0 Then
Debug.Print H.Address
H.Address = Mid(H.Address,InStrRev(H.Address,"\"))
End If
Next
Next
End Sub