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.

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.

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

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?

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.

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?

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