I've just got back to my office after working away for a couple of weeks and now my large spreadsheet has lost all it's hyperlinks.
I think a co-worker saved the sheet incorrectly for it to do this but I'm looking to fix it back to the way it was before I went away without changing every single link one at a time.
They used to point to the server I work from but now they are directing to AppData\Roaming\Microsoft\etc....
Thanks
Beanie
Save a copy off before you run this to make sure you don't destroy your file.
Please Login or Register to view this content.
Then change the sOld and sNew to whatever it needs to be.
Please ensure you mark your thread as Solved once it is. Click here to see how.
If a post helps, please don't forget to add to our reputation by clicking the star icon in the bottom left-hand corner of a post.
Thanks Mike, but I'm going to need you to spell this out for me. How do I apply this to the workbook? I haven't the first clue about code and macro's etc.
How to install your new code
- Copy the Excel VBA code
- Select the workbook in which you want to store the Excel VBA code
- Press Alt+F11 to open the Visual Basic Editor
- Choose Insert > Module
- Edit > Paste the macro into the module that appeared
- Close the VBEditor
- Save your workbook (Excel 2007+ select a macro-enabled file format, like *.xlsm)
To run the Excel VBA code:
- Press Alt-F8 to open the macro list
- Select a macro in the list
- Click the Run button
But again, you need to change the sOld and sNew first.
Thanks Mike. I'm very confused about the old and new part.
Old link is C:\Users\localcrew\AppData\Roaming\Microsoft\2016\6 June 2016\Crew Calls\Outs\Crew Call Sheet 29th June 2016.pdf
New link is G:\Administration\2016\7 July 2016\Crew Calls\Ins\Crew Call Sheet 29th June 2016.xlsx
Now I am confused. You are changing the link from a PDF to a XLSX?
I thought you were just redirecting to another folder for a group of hyperlinks
Last edited by mikeTRON; 07-27-2016 at 03:03 PM.
Sorry, my bad. I'll try again:-
Old link is C:\Users\localcrew\AppData\Roaming\Microsoft\2016\6 June 2016\Crew Calls\Outs\Crew Call Sheet 29th June 2016.pdf
New link is G:\Administration\2016\6 June 2016\Crew Calls\Outs\Crew Call Sheet 29th June 2016.pdf
Basically I have a ton of links directed to different directories. Some are .xls files and some are .pdf's
Right so what is the INCORRECT portion of the OLD link?
"C:\Users\localcrew\AppData\Roaming\Microsoft\"
Is it that?
If so the following should work for you.
Please Login or Register to view this content.
Hi Mike
That code isn't helping. After running the macro, when I hover over the hyperlink, the highlighted text appears
to be pointing in the right direction. When I click on the hyperlink I get the cannot find the specified file pop up.
The part I'm not understanding here is by running this code how I can change multiple links.
My workbook is basically an hours log. Each date has a hyperlink to estimated hours for that day which points to a .xls file,
then I have an actual hours which points to a .pdf file. So as it is date specific I don't understand how it's possible to
change, an entire calendar then if you like, by running this code which appears to be specific to one file.
I also have other hyperlinks which point to shows we have worked and invoices for costings per show. These are also .pdf and .xls
files respectively.
Regards
Beanie
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks