Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

VBA code to search URL HTML and return specific image URL

0

I have a different URL in each cell of column D below the header cell. I need a Macro that will search the HTML code from each URL and return a specific image URL from the HTML code to the cell in column E of the same row.

Here's the specific URL found in D2: "https://orderherbalsonline.goherbalife.com/Catalog/Product/Details/en-CA/3106"

Here's the specific image URL from the webpage of the URL in D2 that I need automatically returned to E2: "https://assets.herbalifenutrition.com/content/dam/regional/nam/en_ca/consumable_content/product-catalog-assets/images/2021/06-Jun/3106_F1_FrenchVanilla_GoHL_CA.png/_jcr_content/renditions/cq5dam.web.800.800.png"

I'm very inexperienced with VBA, so once I have this macro saved, how do I go about running it for each blank cell in Column E where a value exists in column D?

Any help is greatly appreciated.

excel
posted by: eporter801 on Mon Jan 8, 2024 at 1:05 pm
Answer
Discuss

Answers

0

Hello eporter801 and welcome to the forum.

I pasted the URL you quoted for the image into Chrome and got "404 - No Resource Found" error. So I then went to the URL you quoted (as in D97) to go to the product page. Once their, I right clicked on the top thumbnail image (the product) and then clicked "Copy image address" which produced the same URL. When I pasted this copied link into the address bar it brought up the image - no error. Why one way works and the other doesn't is a mystery to me.

I am not knowledgable with HTML code so I can't offer anything with regards to searching for the image URL/address.

So I have modified your file in the following way:

1) went to the product page as listed in Col D

2) right clicked the image of the product and then "Copy image address"

3) in your product file I right clicked the adjacent cell (ex: E97) and selected "Link" at the bottom of the menu

4) created a hyperlink to the image

Clicking the hyperlink opens the image in your default browser.

I have attached the modified file with 4 examples of the hyperlink (products 0004, 0006, 001K and 3106)

If this answers your question please mark my answer as Selected.

Cheers   :-)

posted by: WillieD24 (rep: 557) on Mon Jan 8, 2024 at 5:27 pm
Discuss

Discussion

I appreciate your response, but I'm not sure that provides what I'm looking for. I know how to find the image URL manually, but I'm looking for a VBA code that finds it automatically in each cell of Column E
eporter801 Jan 8, '24 at 7:29 pm
Sorry, but as I mentioned, I am not familiar with HTML code so I won't be able to develop VBA code to get the image URL from the webpage. (From what I have seen in the past, that type of code can get quite complex.)
It's possible that John_Ru, a regular contributor, may be able to provide a solution.
Cheers   :-)
WillieD24 (rep: 557) Jan 8, '24 at 7:55 pm
If you go to the product page and copy the image address, and the paste it into the address bar of a new tab, before pressing enter, you will see that there is extra text in front of "https" which is "cq5adm.web.800.800.png (800x800) - " in bold. This text is not captured when the address is pasted into Excel which is probably why the address without this doesn't work.
Hopefully someone with HTML code knowledge can help you out.

Cheers   :-)
WillieD24 (rep: 557) Jan 9, '24 at 1:49 pm
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login