时间戳

首页 > 解决方案 > Excel VBA code to scrape URLs, two different error codes

excel - Excel VBA code to scrape URLs, two different error codes

问题描述

Again, very new to coding, any insight appreciated. See code below, am happy to provide more if that helps solve the issue. I've marked [issue] next to the lines that are generating the error messages.


Set xRng = Application.InputBox("Please select the keyword range", "Google Search Macro", Selection.Address, , , , , 8)
If xRng Is Nothing Then Exit Sub

Application.ScreenUpdating = False
xLastRow = xRng.Rows.Count

Set xRng = xRng(1)

For i = 0 [issue] To xLastRow - 1 
tempStr = xRng.Offset(i).Value
tempStr = Replace(tempStr, " ", "+")
url = "https://www.google.com/search?q=" & tempStr

Set nameCell = xRng.Offset(i, 1)
Set linkCell = xRng.Offset(i, 2)

Set request = CreateObject("MSXML2.XMLHTTP")
request.Open "GET", url, False
request.setRequestHeader "Content-Type", "text/xml"
request.send

returnStr = StrConv(request.responseBody, vbUnicode)
returnPage.body.innerHTML = returnStr [issue]

The first error message that comes up is "Compile error: For control variable already in use" and it highlights "For i = 0."

The second error message that comes up is "Compile error: Expected End Sub" and it highlights "returnStr."

标签： excelvbaurl

解决方案

在这里，您的代码已排序。

您缺少声明，为避免这种情况，我建议Option Explicit您在模块顶部使用它，这将迫使您声明所有变量。

你错过了Next i关闭循环和End Sub关闭过程：

Option Explicit
Sub URLPull()

    Dim searchRange As Range
'    Dim nameCell As Range 'not used
'    Dim linkCell As Range 'not used
    Dim url As String
    Dim returnStr As String
    Dim tempStr As String
    'Dim i As Long, xLastRow As Long 'no need for them using For Each loop
    Dim request As Object
    Dim returnPage As New HTMLDocument
    Dim returnSiteName As Variant
    Dim returnLink As Variant
    Dim xRng As Range 'this was missing
    Dim C As Range 'to loop through the xRng

    'On Error Resume Next delete this, this is for error handle

    Set xRng = Application.InputBox("Please select the keyword range", "Google Search Macro", Selection.Address, , , , , 8)
    If xRng Is Nothing Then Exit Sub

    Application.ScreenUpdating = False
'    xLastRow = xRng.Rows.Count
'
'    Set xRng = xRng(1)

    Set request = CreateObject("MSXML2.XMLHTTP") 'QHarr comment

    For Each C In xRng 'this way you loop through all the cells in your range
        tempStr = C 'this is the cell value
        tempStr = Replace(tempStr, " ", "+")
        url = "https://www.google.com/search?q=" & tempStr 'if you get no results, check here the tempStr value
        'You are not using these:
'        Set nameCell = xRng.Offset(i, 1)
'        Set linkCell = xRng.Offset(i, 2)
        request.Open "GET", url, False
        request.setRequestHeader "Content-Type", "text/xml"
        request.send
        returnStr = StrConv(request.responseBody, vbUnicode)
        returnPage.body.innerHTML = returnStr
    Next C 'this was missing

End Sub 'this was missing

推荐阅读