I am trying to build a macro for getting data from web. And having struggle of defining the URL parts.
I would like to make the URL like this and the xxxxx should be the value in G1 cell in the same worksheet:
https://sample.com/?ID=xxxxxx
Now I have the following codes, and will be happy if could edit for that. How should I edit for URL3 to make it to get the ID number from the G1 cell and how should I build the URL string to make it "URL2+URL3"
Thanks for your helpings.
Sub GetData()
Dim XMLPage As New MSXML2.XMLHTTP60
Dim HTMLDoc As New MSHTML.HTMLDocument
Dim URL As String
Dim URL2 As String
Dim URL3 As String
URL2 = "https://sample.com/?ID="
URL3 = "the ID in G1 cell"
XMLPage.Open "GET", URL, False
XMLPage.send
HTMLDoc.body.innerHTML = XMLPage.responseText
ProcessHTMLPage HTMLDoc
End Sub
Sub ProcessHTMLPage(HTMLPage As MSHTML.HTMLDocument)
Dim HTMLTable As MSHTML.IHTMLElement
Dim HTMLTables As MSHTML.IHTMLElementCollection
Dim HTMLRow As MSHTML.IHTMLElement
Dim HTMLCell As MSHTML.IHTMLElement
Dim RowNum As Long, ColNum As Integer
Set HTMLTables = HTMLPage.getElementsByTagName("table")
For Each HTMLTable In HTMLTables
If HTMLTable.Rows(0).Cells(0).innerText = "Firma Bilgileri" Then
Range("A29").Value = HTMLTable.className
Range("B1").Value = Now
RowNum = 30
For Each HTMLRow In HTMLTable.getElementsByTagName("tr")
'Debug.Print vbTab & HTMLRow.innerText
ColNum = 1
For Each HTMLCell In HTMLRow.Children
Cells(RowNum, ColNum) = HTMLCell.innerText
ColNum = ColNum + 1
Next HTMLCell
RowNum = RowNum + 1
Next HTMLRow
End If
Next HTMLTable
End Sub