0

Iam working with excel 2016. I need to extract the link of an image from a website using VBA in excel.

Example, i have a website that shows a product with the link : https://www.hikvision.com/en/products/Turbo-HD-Products/Turbo-HD-Cameras/Value-Series/ds-2ce56d0t-vpir3f/

My image is into a div , like that :

<div class="slide-image" style="background-image: url('/content/dam/hikvision/products/HIKVISION/Turbo_HD_Products/Turbo_HD_Cameras/Value_Series/D0T_Series/DS-2CE56D0T-VPIR3F/images/2CE56D0T-半球11-正视图.png.thumb.1280.1280.png');"></div>

I tried this : 

Private Sub btnExtractURL_Click()
Dim sourceString As String
Dim rowIdx As Integer, rowMax As Integer
Dim posFirst As Integer, posLast As Integer, chrLength As Integer
rowMax = Range("A3").End(xlDown).Row
' ---
For rowIdx = 3 To rowMax
    If Cells(rowIdx, 1).Value <> "" Then
        Cells(rowIdx, 2).Value = ""
        sourceString = Cells(rowIdx, 1).Value
        posFirst = InStr(sourceString, "http")
        posLast = InStr(posFirst, sourceString, """")
        chrLength = (posLast - 1) - (posFirst - 1)
        Cells(rowIdx, 2).Value = Mid(sourceString, posFirst, chrLength)
    End If
Next
' ---
MsgBox "finished"
End Sub

But i have an error with this solution... I tried to extract the text to see another method, and it work's but when i insert the class of that image, it doesn't work ! 

Sub Get_Web_Data()

Dim request As Object
Dim response As String
Dim html As New HTMLDocument
Dim website As String
Dim price As Variant

' Website to go to.
website = "https://www.hikvision.com/en/products/Turbo-HD-Products/Turbo-HD-Cameras/Value-Series/ds-2ce56d0t-vpir3f/"

' Create the object that will make the webpage request.
Set request = CreateObject("MSXML2.XMLHTTP")

' Where to go and how to go there - probably don't need to change this.
request.Open "GET", website, False

' Get fresh data.
request.setRequestHeader "If-Modified-Since", "Sat, 1 Jan 2000 00:00:00 GMT"

' Send the request for the webpage.
request.send

' Get the webpage response data into a variable.
response = StrConv(request.responseBody, vbUnicode)

' Put the webpage into an html object to make data references easier.
html.body.innerHTML = response

' Get the price from the specified element on the page.
Name = html.getElementsByClassName("prod_name").Item(0).innerText

' Output the price into a message box.
MsgBox Name

End Sub

Can you give an idea to extract this image and copy the link into my excel ?

Dalila Hannouche
  • 29
  • 11
  • 1
    Yes, it is possible. • If that is not the answer you expected please read [ask] and include what you have tried so far explaining where you got stuck or errors. Note that this is not a free code writing service. – Pᴇʜ Dec 20 '19 at 08:34
  • Iam sorry if you don't understand what i mean. My english is not good :) I will try again. – Dalila Hannouche Dec 23 '19 at 17:32
  • *"doesn't work"* is no error description that helps us to help you. Please include in which line you get an error and what the exact error message is. – Pᴇʜ Jan 07 '20 at 08:13

0 Answers0