Excel vba - extracting data from webpage using msxml2.xmlhttp

[nkaggarwal1]

Hi , The below link helped me to workout a old problem i was facing of copying picture from website to excel.

https://www.mrexcel.com/forum/excel-...namic+url+cell

Need help again.

The Code and modules which was given was very fast , it processed 500 entries in 20-25 seconds . i had another requirement of extracting data from webpage and i tried the below code and it takes around 3-4 seconds for one record , how can i change this to module format USING MSXML2.XMLHTTP

First column of Asin9 array contains my 100 asin's for which i am trying to nextract a particular data from amazon.in site.

Code-
====
Sub Button1_Click()
Dim StartTime As Double
Dim SecondsElapsed As Double
StartTime = Timer
For i = 1 to 100
On Error Resume Next
Dim IE As New InternetExplorer
IE.Visible = False
IE.navigate "https://www.amazon.in/dp/" & Range("Asin9")(i, 1).Value
Do
DoEvents
Loop Until IE.readyState = READYSTATE_COMPLETE
Dim Doc As HTMLDocument
Set Doc = IE.document
Range("Asin9")(i, 13).Value = Doc.getElementById("imgTagWrapperId").innerHTML
Next
SecondsElapsed = Round(Timer - StartTime, 2)
MsgBox "Refresh Completed in" & SecondsElapsed & " seconds", vbInformation
End Sub


Kindly advise!!

Thanks,

Nishant.

 

[nkaggarwal1]

Hi Domenic , Thanks , will use both as per requirement (accuracy/speed). many Thanks.

Just wanted to know , Can we extract data from a login/password protected webpage using xmlhttp? If yes , i will open a new thread for that.

 

[Domenic]

It should be possible, so yes start a new thread and ask your question there.

Cheers!

 

[nkaggarwal1]

Sub Button1_Click()
Dim StartTime As Double
Dim SecondsElapsed As Double
StartTime = Timer
'Application.ScreenUpdating = False
For i = 1 To 14
On Error Resume Next
Dim IE As New InternetExplorer
IE.Visible = True
'IE.navigate "https://sellercentral.amazon.in/orders-v3/search?page=1&q=" & Range("Asin9")(i, 1).Value & "&qt=asin&date-range=last-1"
'IE.navigate "https://sellercentral.amazon.in/orders-v3/search?page=1&date-range=1517077800000-1518632999000&q=" & Range("Asin9")(i, 1).Value & "&qt=asin"
'Do
'DoEvents
'Loop Until IE.readyState = READYSTATE_COMPLETE
'Dim Doc5 As HTMLDocument
'Set Doc5 = IE.document
'Application.Wait Now() + TimeValue("00:00:004")
'Range("Asin9")(i, 2).Value = Doc5.getElementsByClassName("total-orders-heading")(0).innerText
'Application.ScreenUpdating = True
'IE.navigate "https://sellercentral.amazon.in/orders-v3/search?page=1&q=" & Range("Asin9")(i, 1).Value & "&qt=asin&date-range=last-1"
'Do
'DoEvents
'Loop Until IE.readyState = READYSTATE_COMPLETE
'Dim Doc6 As HTMLDocument
'Set Doc6 = IE.document
'Application.Wait Now() + TimeValue("00:00:004")
'Range("Asin9")(i, 4).Value = Doc6.getElementsByClassName("total-orders-heading")(0).innerText
'Application.ScreenUpdating = True
IE.navigate "https://sellercentral.amazon.in/orders-v3/search?page=1&date-range=1548613800000-1550168999000&q=" & Range("Asin9")(i, 1).Value & "&qt=asin"
Do
DoEvents
Loop Until IE.readyState = READYSTATE_COMPLETE
Dim Doc7 As HTMLDocument
Set Doc7 = IE.document
Application.Wait Now() + TimeValue("00:00:004")
Range("Asin9")(i, 6).Value = Doc7.getElementsByClassName("total-orders-heading")(0).innerText
Application.ScreenUpdating = True
Next
'Application.ScreenUpdating = True
SecondsElapsed = Round(Timer - StartTime, 2)
MsgBox "Refresh Completed in" & SecondsElapsed & " seconds", vbInformation
End Sub

 

[B___P]

Hi nkaggarwal1,
I use this line of code to wait for webpage content fully loaded

Do While IE.Busy Or IE.ReadyState <> 4: DoEvents: Loop

Give it a try and let us know.
Have a nice weekend