0

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
braX
  • 11,506
  • 5
  • 20
  • 33

0 Answers0