Preventing hyperlinks and vlookups from breaking

[jane]

We are planning a re-organize our primary Drive and will be moving
most of our folders and subfolders. We commonly use hyperlinks between
workbooks. We are an Excel dependent department and have hundreds of files
within our primary folders and subfolders.

During testing, I find that when the source workbook is moved, the hyperlink
in the dependent workbook no longer works and I get: "Cannot open specified
file."

I need to move the folders and subfolders soon and need to know how to
prevent this error from occurring when the users try to use the hyperlinks.

Also, re: vlookups - I know my vlookups to sheets within a workbook will be
fine. Will the vlookups to other workbooks break also?

THANK YOU IN ADVANCE!! jane

 

[Jim Thomlinson]

All of your links will break if you move your files or folders and there is
nothing you can do about it other than to fix all of the files after the
move. When I say all I mean ALL. Formulas, hyperlinks and anything else that
refers to external workbooks. That is the main pitfall of linking workbooks
and the reason that I personally (almost) never do it.

 

[מיכ×ל (מיקי) ×בידן]

As far as I recall you will need to run a Macro that loops on all your
WorkBooks > opens(!) each and every one > run an inside loop [part of is
shown hereunder] > save the WB and close it.
I would suggest to get help from a professional VBA programmer.

 

[Jim Thomlinson]

I must be missing something. How do you make that work? Lets say that I have
2 files both sitting in C:\Test. I move 1 file to C:\Here and the other file
to C:\There. Now if I had links in other files to my files in test what is
the appropriate replacement. It is either C:\Here or C:\There. No way to
know.

The only way I can see that working is if I know the full path and file name
of each and every file for both the old and the new location. I could store
that in an XL file and then use that file to create the replacements but that
is going to be a huge task and it may take a very substantial amount of time
to run as it has to open every single XL file on the entire network to go
trolling for each and every possible external link.
--
HTH...

Jim Thomlinson

As far as I recall you will need to run a Macro that loops on all your
WorkBooks > opens(!) each and every one > run an inside loop [part of is
shown hereunder] > save the WB and close it.
I would suggest to get help from a professional VBA programmer.
--------------------------------------
For Each HL In ActiveSheet.Hyperlinks
HL.Address = AWF.Substitute(HL.Address, "C:\Micky", "D:\Micky")
HL.TextToDisplay = AWF.Substitute(HL.Name, "C:\Micky", "D:\Micky")
Next
-------------------
Micky

We are planning a re-organize our primary Drive and will be moving
most of our folders and subfolders. We commonly use hyperlinks between
workbooks. We are an Excel dependent department and have hundreds of files
within our primary folders and subfolders.

During testing, I find that when the source workbook is moved, the hyperlink
in the dependent workbook no longer works and I get: "Cannot open specified
file."

I need to move the folders and subfolders soon and need to know how to
prevent this error from occurring when the users try to use the hyperlinks.

Also, re: vlookups - I know my vlookups to sheets within a workbook will be
fine. Will the vlookups to other workbooks break also?

THANK YOU IN ADVANCE!! jane

 

[מיכ×ל (מיקי) ×בידן]

If you don't care to count the week - as a week - where the start & end date
are "lying", - then you have got an answer.
If you do care - please response
Micky

 

[מיכ×ל (מיקי) ×בידן]

It is difficult for me to express myself in English.
I don't know what they know - or don't know.
What I suggested worked for many organizations to which I used to consult as
per "Office" problems/issues - but those organiztions, usually, knew what and
where they are doing.
Believe me I do not reply just for the fun...
If I didn't fully understand the question - I apologize to Jeane.
Micky

I must be missing something. How do you make that work? Lets say that I have
2 files both sitting in C:\Test. I move 1 file to C:\Here and the other file
to C:\There. Now if I had links in other files to my files in test what is
the appropriate replacement. It is either C:\Here or C:\There. No way to
know.

The only way I can see that working is if I know the full path and file name
of each and every file for both the old and the new location. I could store
that in an XL file and then use that file to create the replacements but that
is going to be a huge task and it may take a very substantial amount of time
to run as it has to open every single XL file on the entire network to go
trolling for each and every possible external link.
--
HTH...

Jim Thomlinson

As far as I recall you will need to run a Macro that loops on all your
WorkBooks > opens(!) each and every one > run an inside loop [part of is
shown hereunder] > save the WB and close it.
I would suggest to get help from a professional VBA programmer.
--------------------------------------
For Each HL In ActiveSheet.Hyperlinks
HL.Address = AWF.Substitute(HL.Address, "C:\Micky", "D:\Micky")
HL.TextToDisplay = AWF.Substitute(HL.Name, "C:\Micky", "D:\Micky")
Next
-------------------
Micky

We are planning a re-organize our primary Drive and will be moving
most of our folders and subfolders. We commonly use hyperlinks between
workbooks. We are an Excel dependent department and have hundreds of files
within our primary folders and subfolders.

During testing, I find that when the source workbook is moved, the hyperlink
in the dependent workbook no longer works and I get: "Cannot open specified
file."

I need to move the folders and subfolders soon and need to know how to
prevent this error from occurring when the users try to use the hyperlinks.

Also, re: vlookups - I know my vlookups to sheets within a workbook will be
fine. Will the vlookups to other workbooks break also?

THANK YOU IN ADVANCE!! jane