Sharid
Well-known Member
- Joined
- Apr 22, 2007
- Messages
- 1,064
- Office Version
- 2016
- Platform
- Windows
I have traditionally used IE for pulling data of the web, this is my code. I now want to change it to XMLHTTP as it tends to be faster, can some please help me make a few changes as I have made a few attempts but nothing works as i'm not 100% sure what I am doing. The code below works but is slow as it uses IE
As always thanks in advance
VBA Code:
Private Sub CommandButton1_Click()
Dim wb As Workbook
Dim wsSheet As Worksheet, links As Variant, IE As Object, link As Variant
Dim rw As Long
Dim dd As Variant
''''SHEET1 as sheet with URL
Set wb = ThisWorkbook
Set wsSheet = wb.Sheets("Sheet1")
''''Set IE = InternetExplorer
Set IE = CreateObject("InternetExplorer.Application")
'''' Source sheet, URLS are in Sheet1 column A row2
rw = wsSheet.Cells(wsSheet.Rows.Count, "A").End(xlUp).Row
links = wsSheet.Range("A2:A" & rw)
With IE
.Visible = False
For Each link In links
.navigate (link)
While .Busy Or .readyState <> 4: DoEvents: Wend
On Error Resume Next
Set doc = IE.document
''''IF Statement, change class to suite needs ' Place DATA IN SHEET1 COLUMN B
If doc.getElementsByClassName("mbg")(0) Is Nothing Then
Sheet1.Cells(Sheet1.Cells(Sheet1.Rows.Count, "B").End(xlUp).Row + 1, "B").Value = "-"
Else
dd = doc.getElementsByClassName("mbg")(0).Children(0).href
Sheet1.Cells(Sheet1.Cells(Sheet1.Rows.Count, "B").End(xlUp).Row + 1, "B").Value = dd
End If
''''navigate links
Next link
''''Close IE Browser
.Quit
End With
Set IE = Nothing
End Sub
As always thanks in advance