Hi everyone,
I have a spreadsheet with hundreds of the following link addresses:
madeupname.com/download/logpdfs/002.pdf/
etc.
I need to change all of them to:
madeupname.com/wp-content/uploads/logs/002.pdf
etc.
So I basically need to change this part of the path
madeupname.com/download/logpdfs/
to
madeupname.com/wp-content/uploads/logs
without changing the actual file name, ie 001.pdf
Is this possible using VBA?
I've searched the web for VBA examples but I can't get them to work
Cheers, Martin
have you tried a simple find and replace?
Find what >> download/logpdfs
Replace with >> wp-content/uploads
it worked on my sample.
Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
Sam Capricci
Hi Sambo kid,
Unfortunately Find and Replace doesn't work with hyperlinked content.
opinions.co.il/ogdan/find-and-replace-hyperlinks-url-links-in-excel
But I'm not sure if any code like the above will work on replacing only the main part of the path, and leave the file name alone?
Cheers, Martin
Hi,
Just select the link cell or entire column where the links was placed and hit find and replace (see attached).
If they are actual hyperlinks, rather than HYPERLINK formula then try this in the immediate window.
ALT+F11 (open VBE)
CTRL+G (Open immediate window)
Copy+Paste
Press Enter
Please Login or Register to view this content.
Thanks Andy,
The full replacement should be as screenshot below, the forum wouldn't let me post links and I missed a folder
However it only replaces 5 of the first of 200+ entries.
Do I need to select all the hyperlinked cells?
Where do I press Enter?replace hyperlinks.png
You do not need to select any cells.
You just need to have the cursor at the end of the line of code and press enter.
But how did you manage to replace 5 of the 200+ if you did not know how to execute the code?
Are all the hyperlinked cells on the same sheet?
Hi Andy,
I just pressed enter at the end of the line of code after following your instructions
Yes, all the hyperlinks are on the same spreadsheet.
If I could post a link I could upload the file...
Why do you need to post a link. Use the upload facility on the site
There is a big yellow banner at the top of the page telling you what to do, as well as these instructions
Welcome to the forum
Please attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.
Remember to desensitize the data.
Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.
In the attached sheet, it is the Column called "Name of Log" that I'm trying to change hyperlinks for.
The cells that were correctly highlighted have a yellow highlight on that row.
There's also the links replacement addresses described.
Thanks, Martin
Last edited by lovelyjubbly; 06-08-2020 at 04:56 AM. Reason: Remove spreadsheet
I think I've worked out why, some of the links have http.www instead of just http....
That was it!
Thank you ever so much Ian
Best wishes, Martin
Works like a charm. Tks a lot Andy.Originally Posted by Andy Pope
If they are actual hyperlinks, rather than HYPERLINK formula then try this in the immediate window.
ALT+F11 (open VBE)
CTRL+G (Open immediate window)
Copy+Paste
Press Enter
Please Login or Register to view this content.
Merry Xmas and a happy New Year
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks