I have a question regarding Microsoft Excel 97.  I have a database of
about 3000 entries that are listed in Excel.  They are all
hyperlinked to a folder on my hard drive, for example:
"C:\folder1\folder2\file2234.txt".  However, I have recently moved all
the actual files to the D: (CDRom) drive while the excel file is still
on C:\.  How can can I change all of them
to the different drive letter w/o having to do it individually?  Can I
use a macro that will ONLY change the drive letter?  If so, please
provide details as to how to accomplish this task, because the macro
didn't work correctly last time I tried.

Good Day botanybill-ga,


I am currently using Excel 2000, but I am almost certain that the 97
version also has the "Replace" feature.

The Replace feature can substitute a string, or any part of a string
with another. To use it, open your excel file and select all the
records in which you wish to have the drive letter modified. In the
Edit menu you will find the Replace option. Click on it to get the
Replace dialog box.

Since I am using the 2000 version, I am not sure if some of the
Replace option's features are in the same place, but once you know
what options to look for, you might be able to find them easily.  If
not, you can of course ask for a clarification, and I will downgrade
my excel version, and find the exact location of the required option.

The option we are looking for (in Excel 2000) is in the form of a
check box in the Replace dialog that comes up when you select it in
the Edit Menu. The option is called "Find Entire Cells Only". You want
to make sure that this option is not selected, as Excel will only look
for a whole string in a cell for a match rather than just a part of
the string.

Now, in the "Find What" entry box, type "c:" (no quotations of
course), and in the "Replace With" box type the new drive letter such
us "d:" (again, no quotes).
Click on the "Replace All" button to avoid confirmation on each
modification, and all the changes will be made for you quickly and
easily.

I hope this helps. If not, please ask for a clarification before
rating my answer.  Thank you for your question.

Regards,
slawek-ga

---

Request for Answer Clarification by botanybill-ga on 19 Oct 2002 16:41 PDT

Thanks for the answer, but that's actually not quite the problem.  I
suppose I need to clarify just a bit.  Each of those files are titles
that are *Hyperlinked* to the C: drive.  So, you can't use the find
and replace tool because the hyperlink is "inside" of the file.  For
example, lets say one of the 3000 files is named on Excel: help_123,
but the hyperlink goes to the link C:\file1\file2\help_123.txt.  The
Hyperlink can't be changed unless I click on the file and go to "Edit
Hyperlink" and then change it to something like
D:\file1\file2\help_123.txt.  So unfortunately the Find & Replace will
not work since the Hyperlink isn't listed in the spreadsheet part of
Excel but internally and can only be changed when I right click on the
file and edit the hyperlink.  I need to find some way to edit all of
that manually.  Any ideas?  Perhaps some kind of macro?  (I tried
recording a macro, but it didn't work.  I can explain why if you'd
like, but basically it thought that when I changed the hyperlink and
pressed "ok" that I wanted every file to read the entire drive letter
and folder extention and file name for every single file i change w/
the macro, which of course I do not, just the drive letter.  But it
didn't recognize that.)

Thanks!

---

Clarification of Answer by slawek-ga on 19 Oct 2002 18:13 PDT

Good Day,

What about exporting the file to txt, opening it in a word editor
which has the replace option, and importing it back to excel? I am not
at my computer right now to try this for you (the PC I am on does not
have Excel), but wanted to reply as soon as possible. Please let me
know if this worked, if not I will do more research at home. I
appologise for the delay.

Regards,
slawek-ga

---

Clarification of Answer by slawek-ga on 20 Oct 2002 00:34 PDT

Good Day botanybill-ga,

I have arrived at home, and tested my second solution (saving as
text), which did not work.  The file addresses do not get saved. 
However, I have found another similar solution: save the file as HTML.

Saving it as HTML will put in the actual address into the text of the
file (since all HTML is text).  Open it in an editor without word
wrapping, and run the replace feature. Than open the HTML file in
excel. I hope that 97 is like 2000, and can open an html file it
created.

Please let me know if saving it as HTML, making the changes by
"Replace", and re-opening it in Excel 97 worked. If not, do you have
access to a 2000 version anywhere? If none of the above work, I will
find another solution for you!

Thank you.
slawek-ga

Thank you so much!  That worked!  Your quick and effective answers
have definately made a customer out of me!!!!!!!!

Hi
Please try this macro. It will rename all hyperlink names from "c:" to
"D:"
I assumed that your data entry is in worksheeet name "sheet1". If it
is different please change that name in code for "Sheet1".

Sub ChangePath()
    For Each h In Worksheets("Sheet1").Hyperlinks
      'MsgBox h.Address
      intPosition = InStr(1, h.Address, "C:", vbTextCompare)
      If intPosition > 0 Then
        strBalanceText = Mid(h.Name, 3)
        strNewAddress = ("D:" & strBalanceText)
        h.Address = strNewAddress
        intPositon = 0
      End If
    Next
End Sub


I assumed you know how to edit and run macro
Please let me know if you face any problem.

Thanks
RAC