Referencing a Cell .send Syntax Error // Web Scraping

Background/objective: Web Scrape: Problem with the Syntax using the .send

I am attempting to send the last name and first name from a list of names in two columns of cells, I am coming across Syntax Errors as it does not recognize the cell and assumes the range is the "name"

code: The syntax error begins on the "last" and "first" line under the .send, as I am attempting to send a cell value rather than type in the name. What is the correct formatting when referencing a range of cells?

Option Explicit
Sub Test()
    Dim sContent As String
    Dim i As Long
    Dim j As Long
    Dim aHeader() As String
    Dim aRows() As String

    ' Retrieve HTML content via XHR
    With CreateObject("MSXML2.XMLHTTP")
        .Open "POST", "http://npinumberlookup.org/getResults.php", False
        .setRequestHeader "content-type", "application/x-www-form-urlencoded"
        .send _
            "last=Range(G:1)" & _
            "&first=Range(F:1)" & _
            "&pracstate=TX" & _
            "&npi=" & _
            "&submit=Search"

1 answer

  • answered 2018-01-14 11:27 Foxfire And Burns And Burns

    When you want to reference the value of a Range, is exactly as @Qharr said before. I tried doing:

    ' Retrieve HTML content via XHR
    With CreateObject("MSXML2.XMLHTTP")
        .Open "POST", "http://npinumberlookup.org/getResults.php", False
        .setRequestHeader "content-type", "application/x-www-form-urlencoded"
        .send _
            "last=" & Range("G1").Value & _
            "&first=" & Range("F1").Value & _
            "&pracstate=TX" & _
            "&npi=" & _
            "&submit=Search"
    End With
    

    And it worked. No errors when running the code.