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.

Moved from LINKS

 

[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.

 

[Klemen25]

Use replace funciton (ctrl F).
Find: '\2003\MARCH\item_serial_mar_03_001'
Replace: 'item_serial_mar_03_001'

 

[Davius]

Use replace funciton (ctrl F).
Find: '\2003\MARCH\item_serial_mar_03_001'
Replace: 'item_serial_mar_03_001'

The path that needs to change is within the 'Edit Hyperlink' dialog
box, I cant implement the replace function in that way. That I know
of. Also due to the nature of the filenames (each one unique) I'd have
to do this for every file, which is what i'm trying to avoid.

I really am at a loss.

 

[Jim Cone]

Try running this VBA code on a copy of your sheet...
'--
Sub PutNewBrandOnThem()
'Jim Cone - Portland, Oregon USA - August 2009
Dim HRng As Range
Dim hyp As Hyperlink
Dim HAddress As String
Dim sNewPath As String

sNewPath = "c:\DOCUMENTS\SHARED\IMAGES\"
For Each hyp In ActiveSheet.Hyperlinks
Set HRng = hyp.Parent
HAddress = hyp.Address
HAddress = sNewPath & Dir(HAddress)
hyp.Delete
ActiveSheet.Hyperlinks.Add HRng, HAddress, TextToDisplay:=Dir(HAddress)
Next 'hyp

Set hyp = Nothing
Set HRng = Nothing
End Sub
'--

Note: "TextToDisplay" can be any text.

While I can't be completely objective...
you may need something like my commercial Excel add-in "List Files".
It lists files from specified folder(s) with a hyperlink to each file.
Free 3 week tryout - just email and ask for it.
--
Jim Cone
Portland, Oregon USA
([email protected])



"Davius" <[email protected]>
wrote in message
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.

Moved from LINKS