VBA网页爬虫.send方法单元格引用语法错误求解

Let's break down the issue you're facing and fix it step by step:

The Root Problem #

Right now, when you write "last=Range(G:1)" in your .send parameters, VBA is treating Range(G:1) as a literal string instead of evaluating it to pull the actual value from your worksheet. On top of that, Range(G:1) isn't valid syntax for targeting a single cell—you need to use quoted identifiers like Range("G1") or the numeric-indexed Cells(row, column) syntax to specify the exact cell you want.

The Correct Approach #

To pass your worksheet cell values into the POST request, you need to concatenate the cell's .Value property into your parameter string using the & operator. This tells VBA to fetch the value from the cell and insert it into your request data.

Modified Working Code #

Option Explicit
Sub Test()
    Dim sContent As String
    Dim i As Long
    Dim j As Long
    Dim aHeader() As String
    Dim aRows() As String
    ' 通过XHR获取HTML内容
    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
End Sub

Key Changes Explained #

• Replaced "last=Range(G:1)" with "last=" & Range("G1").Value: This syntax pulls the value from cell G1 and appends it to the "last=" parameter string, instead of sending the text "Range(G:1)" directly.
• Swapped Range(G:1) for Range("G1"): The quoted string "G1" correctly targets the single cell at column G, row 1. If you prefer using numeric indices, you could also write Cells(1, 7).Value (1 = row number, 7 = column number for column G).

Bonus: Handling Multiple Rows (If Needed) #

If you plan to loop through multiple rows of names, you can extend the code with a loop to process each row sequentially:

For i = 1 To 10 ' Adjust the row range to match your data
    With CreateObject("MSXML2.XMLHTTP")
        .Open "POST", "http://npinumberlookup.org/getResults.php", False
        .setRequestHeader "content-type", "application/x-www-form-urlencoded"
        .send _
            "last=" & Range("G" & i).Value & _
            "&first=" & Range("F" & i).Value & _
            "&pracstate=TX" & _
            "&npi=" & _
            "&submit=Search"
        ' Add code here to process the response (e.g., sContent = .responseText)
    End With
Next i

内容的提问来源于stack exchange，提问作者josephkane