Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.links
external usenet poster
 
Posts: 7
Default 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.
  #2   Report Post  
Posted to microsoft.public.excel.links
external usenet poster
 
Posts: 7
Default Hyperlink File Path Changes. Hair loss imminent.

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.
  #3   Report Post  
Posted to microsoft.public.excel.links
external usenet poster
 
Posts: 473
Default Hyperlink File Path Changes. Hair loss imminent.

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

  #4   Report Post  
Posted to microsoft.public.excel.links
external usenet poster
 
Posts: 7
Default Hyperlink File Path Changes. Hair loss imminent.

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.
  #5   Report Post  
Posted to microsoft.public.excel.links
external usenet poster
 
Posts: 473
Default Hyperlink File Path Changes. Hair loss imminent.

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



  #6   Report Post  
Posted to microsoft.public.excel.links
external usenet poster
 
Posts: 7
Default Hyperlink File Path Changes. Hair loss imminent.

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.


  #7   Report Post  
Posted to microsoft.public.excel.links
external usenet poster
 
Posts: 473
Default Hyperlink File Path Changes. Hair loss imminent.

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

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

  #8   Report Post  
Posted to microsoft.public.excel.links
external usenet poster
 
Posts: 7
Default Hyperlink File Path Changes. Hair loss imminent.

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.
  #9   Report Post  
Posted to microsoft.public.excel.links
external usenet poster
 
Posts: 473
Default Hyperlink File Path Changes. Hair loss imminent.

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

  #10   Report Post  
Posted to microsoft.public.excel.links
external usenet poster
 
Posts: 7
Default Hyperlink File Path Changes. Hair loss imminent.

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



  #11   Report Post  
Posted to microsoft.public.excel.links
external usenet poster
 
Posts: 7
Default Hyperlink File Path Changes. Hair loss imminent.

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.
  #12   Report Post  
Posted to microsoft.public.excel.links
external usenet poster
 
Posts: 473
Default Hyperlink File Path Changes. Hair loss imminent.

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

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

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Hyperlink to File name and path VBA Excel jlclyde Excel Discussion (Misc queries) 3 February 4th 09 08:24 PM
Export file path from sequel to excel as hyperlink KMiles Excel Discussion (Misc queries) 0 August 3rd 07 02:22 AM
Formula too long - new file path is shorter than old file path - Excel 2003 Greg J Excel Worksheet Functions 1 November 22nd 06 06:16 PM
How do I display the full file path for a hyperlink in Office 2003 Worthy Excel Discussion (Misc queries) 6 June 25th 06 04:19 PM
hyperlink navigation path path wrong in Excel 2003 CE Admin Excel Discussion (Misc queries) 5 January 7th 06 08:47 PM


All times are GMT +1. The time now is 11:47 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"