How to pull specific image URL from webpage HTML and insert into excel

Question

How to pull specific image URL from webpage HTML and insert into excel

PorterSLC 0

I have a spreadsheet with over 1000 rows containing product information. Each row is different product. Column D is the CONCATENATED URL to the product webpage. I need to return a specific image URL to each cell in column E that is found in the HTML of the product URL from column D. The image URL that I need to return to column E is always located immediately after the following HTML code on the product page: <img class="zoom-image" data-bind="click: ShowGalleryModal, attr: { src: CurrentImage.src }" src=" and immediately before the fallowing HTML code: "> <small>Roll over image to zoom in</small> However, the file names are not always the same number of characters, so I don't believe I'm able to use a string that will count characters. Assuming I had the correct string to search for these codes and extract the URL from between them, I don't quite know how to implement it into excel. Any help is greatly appreciated. Thank you!

Jiajing Hua 18,060 Reputation points Moderator

2024-01-15T08:43:18.7333333+00:00

Hi,

Please refer to Azar's suggestion.

Any further issues, welcome to discus with us.

1 answer

Your answer

Jiajing Hua 18,060 Reputation points Moderator

2024-01-15T08:43:18.7333333+00:00

Hi,

Please refer to Azar's suggestion.

Any further issues, welcome to discus with us.

Answer 1

Azar 29,520 MVP Volunteer Moderator

Hey PorterSLC,

I guess you can use text function here to find the starting and ending positions of the image URL in the HTML.

For example, you might use FIND to find the starting position and MID to extract the substring.

Search for the HTML Tags:

Use FIND to locate the position of the HTML tags <img class="zoom-image" data-bind="click: ShowGalleryModal, attr: { src: CurrentImage.src }" src=" and "> <small>Roll over image to zoom in</small>.

Extract the URL:

Once you have the positions, use MID to extract the substring between these positions, which would be the image URL.

=MID(D2, FIND("<img class=""zoom-image"" data-bind=""click: ShowGalleryModal, attr: { src: CurrentImage.src }"" src=""", D2) + LEN("<img class=""zoom-image"" data-bind=""click: ShowGalleryModal, attr: { src: CurrentImage.src }"" src=""") + 1, FIND("""> <small>Roll over image to zoom in</small>", D2) - (FIND("<img class=""zoom-image"" data-bind=""click: ShowGalleryModal, attr: { src: CurrentImage.src }"" src=""", D2) + LEN("<img class=""zoom-image"" data-bind=""click: ShowGalleryModal, attr: { src: CurrentImage.src }"" src=""") + 1))

you might need to use VBA. a custom macro to handle the HTML parsing

Function ExtractImageURL(html As String) As String     Dim startTag As String     Dim endTag As String     Dim startPos As Long     Dim endPos As Long      startTag = "<img class=""zoom-image"" data-bind=""click: ShowGalleryModal, attr: { src: CurrentImage.src }"" src="""     endTag = """> <small>Roll over image to zoom in</small>"      startPos = InStr(html, startTag)     endPos = InStr(startPos, html, endTag)      If startPos > 0 And endPos > 0 Then         ExtractImageURL = Mid(html, startPos + Len(startTag), endPos - startPos - Len(startTag))     Else         ExtractImageURL = "URL Not Found"     End If End Function

You can call the func in excel like this - =ExtractImageURL(D2)

If this helps kindly accept the answer thanks much.

PorterSLC 0 Reputation points

2024-01-15T10:28:01.1233333+00:00

Thank you for your suggestion. I have created a macro using the code you provided, but it's not working quite yet because the HTML code still needs to be downloaded from the URL in D2. How do I get the HTML code to download automatically and store somewhere where the macro can then parse it?

Azar 29,520 MVP Volunteer Moderator

Hey PorterSLC Lemme sugest you a way whis involves making an HTTP request to the URL and fetching the HTML response. You can then parse this HTML to extract the needed info. Here's a VBA code snippet -

Function GetHTMLContent(url As String) As String     Dim xml As Object     Set xml = CreateObject("MSXML2.ServerXMLHTTP")          xml.Open "GET", url, False     xml.send          If xml.Status = 200 Then         GetHTMLContent = xml.responseText     Else         GetHTMLContent = "Error: Unable to fetch HTML content"     End If End Function

In your main macro like this you can call like this,

Sub ExtractImageURLFromWebpage()     Dim urlColumn As Range     Dim cell As Range     Dim htmlContent As String          ' Set the range of cells containing the URLs in column D     Set urlColumn = Range("D2:D1000") ' Adjust the range as per your actual data          ' Loop through each cell in the specified range     For Each cell In urlColumn         ' Fetch HTML content from the URL         htmlContent = GetHTMLContent(cell.Value)                  ' Extract the image URL using your previous function or any desired method         Dim imageUrl As String         imageUrl = ExtractImageURL(htmlContent)                  ' Display or store the extracted image URL (you can modify this part as needed)         cell.Offset(0, 1).Value = imageUrl     Next cell End Sub

If this helps kindly accept the answer thanks much.

PorterSLC 0 Reputation points

2024-01-15T14:52:55.4866667+00:00

Here's what I have. As I'm sure you'll see, I have no idea what I'm doing. How do I set this all up? Screenshot 2024-01-15 074914.png

Share via

How to pull specific image URL from webpage HTML and insert into excel

1 answer

Your answer