Excel VBA - copy text from html and paste into excell

Question

I am looking to copy data from a specific website and paste the information into excel. I have created a search box were you can enter the item code eg "CPINT00038" and it will open the specific page. but for the life of me i cant figure out how to scrape the image and the text and paste them into excel.

here is my code so far please help

Sub SearchCC()

'what to search for

Dim SearchString As String

SearchString = InputBox("What do you want to search for?")

'create and a new instance of IE

Dim IE As New InternetExplorer

IE.Visible = True

'to find what you're looking for

IE.Navigate "http://www.canadacomputers.com/search_results.php? 
search_in=&keywords=" & SearchString

While IE.Busy Or IE.ReadyState <> 4: DoEvents: Wend

End Sub

What have you tried? There are myriad posts here on SO and the internet generally, on how to get images/information from websites. — BruceWayne, May 24 '18 at 17:00
this is what i have now that i can pull the discription but i can't figure out how to pull the image. — txxghostxxt, May 24 '18 at 17:58
Sub SearchCC() 'what to search for Dim SearchString As String SearchString = InputBox("What do you want to search for?") 'create and a new instance of IE Dim IE As New InternetExplorer IE.Visible = True 'to find what you're looking for IE.Navigate "http://www.canadacomputers.com/search_results.php?search_in=&keywords=" & SearchString While IE.Busy Or IE.ReadyState <> 4: DoEvents: Wend 'Grab Description From Site Description = IE.Document.getElementsByClassName("prod-descrip")(0).innerText With ActiveCell .Value = Description End With End Sub — txxghostxxt, May 24 '18 at 17:58

score 0 · Accepted Answer · answered May 24 '18 at 20:28

For the description it's very easy, you just get it in one shot into a variable:

description = IE.Document.getElementById("desc1").getElementsByTagName("p")(0).innerText

... and you print it where you want as Range("A1").Value = description.

For the image, it's a bit more tricky. To download images from the web, you can:

1) Add on top of your module the declaration to the URLDownloadToFile function provided by the library urlmon.dll (note: if your Excel is in 32bit instead of 64bit, you will need to remove the keyword PtrSafe):

Private Declare PtrSafe Function URLDownloadToFile Lib "urlmon" Alias "URLDownloadToFileA" (ByVal pCaller As Long, ByVal szURL As String, ByVal szFileName As String, ByVal dwReserved As Long, ByVal lpfnCB As Long) As Long

2) Use the function like this to save the image into a given path with a given name:

samplePath = "C:\Users\Yourself\YourFolder\yourImage.jpg"
imgSource = IE.Document.getElementsByClassName("prodimg")(0).getAttribute("src")
Response = URLDownloadToFile(0, imgSource, samplePath, 0, 0)

If Response is equal to 0, the image was successfully download from the web and saved into "C:\Users\Yourself\YourFolder\yourImage.jpg".

I'll let you record a macro to insert an image in Excel from a given path (let's call this macro Sub InsertImage(ByVal filePath As String), and then just:

If Response = 0 Then
    InsertImage samplePath
End If

You can then remove the downloaded image from your computer as explained in this answer

Excel VBA - copy text from html and paste into excell

1 Answers1