VBA: getHTML как пользовательская функция - избегайте нескольких запросов HTML

Я использую это для извлечения HTML с веб-страницы

Function GetHTML(url As String) As String    
    With CreateObject("MSXML2.XMLHTTP")
        .Open "GET", url, False
        .Send
        GetHTML = .ResponseText
    End With

End Function

Я создал еще одну пользовательскую функцию (с веб-страницы) для извлечения информации из HTML и помещения ее в двумерный массив.

Function fromthewebpage(month,user)
    testtext=GetHTML("http://example.com")
    ....
    ....
    fromthewebpage= udarray(month,user)
End function

Когда мне нужна информация на листе, я вызываю ее с помощью =fromthewebpage(x,y) в ячейке.

Но размер массива 13 * 4, и мне нужен весь массив, и каждое использование функции fromthewebpage (x, y) вызывает GetHTML независимо, что приводит к 52 извлечениям страницы. Существуют ли способы вызвать GetHTML только один раз и повторно использовать эту информацию для каждого использования fromthewebpage.

Я предполагаю либо 1) вызов GetHTML при открытии книги и сохранение информации в постоянном массиве, на который я могу ссылаться при использовании fromthewebpage, либо 2) создание fromthewebpage функции массива. Но я не знаю, возможно ли это и как это сделать.


html web-scraping excel custom-function vba
person user2523167    schedule 26.09.2018    source источник
comment
Вам понадобится URL-адрес по умолчанию, иначе как вы собираетесь передать URL-адрес GetHTML при открытии книги (подсказка?)? Как вы обновляете этот URL-адрес, когда хотите его изменить? Можете ли вы предоставить фактический URL-адрес и полную функцию fromthewebpage с примером вызова и ожидаемыми результатами? Не похоже, что UDF — идеальный путь. Почему вы не можете использовать подпрограмму для доставки результатов массива на лист, а затем получить доступ к этой области листа? Или еще лучше просто использовать формулы?   -  person QHarr    schedule 26.09.2018
comment
Вы можете просто объявить переменную udarray статической в ​​функции fromthewebpage и сохранить массив после анализа всех данных. При вызове функции сначала проверьте, заполнен ли массив, и если нет, проанализируйте данные.   -  person omegastripes    schedule 27.09.2018

Ответы (1)


arrow_upward
1
arrow_downward

Итак, я упомянул в комментариях идею использования подпрограммы для получения информации HTML и возврата вашего массива на лист. Затем вы можете использовать функцию или формулы для индексации этой информации.

В приведенном ниже случае подгруппа выбирает URL-адрес из диапазона рабочего листа A1. Вы можете привязать подпрограмму к кнопке или событию изменения рабочего листа с целью A1, чтобы она обновлялась при изменении URL-адреса.

Приведенное ниже является иллюстративным. Я использую ссылки на вопросы StackOverflow для заполнения массива, который, как я считаю, является вашими указанными размерами (хотя их легко изменить, если нет).

GetHTML возвращает массив на лист. Функция GetInfo позволяет индексировать этот массив и возвращать элемент, указав строку и столбец в массиве. Это отражение вашего указания двух аргументов в сигнатуре функции для извлечения строки.

Option Explicit
Public Sub GetHTML()
    Dim html As htmldocument, ws As Worksheet
    Set html = New htmldocument: Set ws = ThisWorkbook.Worksheets("Sheet1")
    On Error GoTo errhand
    With CreateObject("MSXML2.XMLHTTP")
        .Open "GET", ws.[A1], False
        .Send
        html.body.innerhtml = .ResponseText
    End With

    Dim arr(0 To 13, 0 To 4)
    Dim i As Long, r As Long, c As Long
    Dim aNodeList As Object
    Set aNodeList = html.querySelectorAll("#question-mini-list h3 > a[href]")
    For i = 0 To (14 * 5) - 1
       If i = 0 Then
           arr(r, c) = aNodeList.Item(i)
       ElseIf i Mod 5 = 0 And i <> 0 Then
           r = r + 1: c = 0
           arr(r, c) = aNodeList.Item(i)
       Else
          c = c + 1
          arr(r, c) = aNodeList.Item(i)
       End If
    Next

    ws.[B1].Resize(UBound(arr, 1) + 1, UBound(arr, 2) + 1) = arr
errhand:
    If Err.Number <> 0 Then Err.Clear
End Sub

Public Function GetInfo(ByVal r As Long, ByVal c As Long) As String
    GetInfo = ThisWorkbook.Worksheets("Sheet1").Range("B1:F14").Cells(r + 1, c + 1)
End Function

Вызов UDF в листе:

введите здесь описание изображения

Событие Worksheet_Change

Если привязать выполнение подпрограммы GetHTML к событию Worksheet_Change с URL-адресом в A1, то в области кода листа 1:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False
    If Target.Address = [A1].Address Then
        GetHTML
    End If
    Application.EnableEvents = True
End Sub
person QHarr    schedule 26.09.2018