excel - 根据excel单元格中的链接获取网络数据

Question

我想创建一个 Excel 工作表，其中一列中有一个网站链接(例如 https://grieferwert.com/?product=beacon-3)，在下一列中，Excel 应该自动导入价格历史图表数据。对于来自具有相同结构的网站(例如 https://grieferwert.com/?product=sand-dk)的不同行中的多个链接，这应该是可能的。如何使 Web 查询基于相邻单元格中的链接？

The table on this screenshot is what the "add data from web" does, on the left the link to the website.

我尝试手动执行此操作，因此对于每个链接，我手动插入了一个数据查询。这是可能的，但我想包含的所有条目都需要很长时间。

这里没有手动编写代码，最新的 Excel 发行版的所有板子功能。不过，我可以从高级电源查询编辑器中提取以下内容:

let
Quelle = Web.Page(Web.Contents("https://grieferwert.com/?product=sand-dk")),
Data0 = Quelle{0}[Data],
#"Geänderter Typ" = Table.TransformColumnTypes(Data0,{{"Type", type text}, {"Price", type text}, {"When", type date}})
in
#"Geänderter Typ"

我希望数据表会根据链接单元格的变化而变化；所以当我更改链接时(交换上面提到的两者)，刷新数据后数据应该会发生相应的变化。最后，我想用别处表格中的数字做一些简单的计算。

Answer 1

将您的 url 从 A1 开始放在 A 列，然后每隔 7 行，然后运行下面的代码(您可以 attach this code to a form control button 以便通过按钮运行)

使用 Alt+F11 打开 VBE。右击project explorer pane并添加 standard module然后将下面的代码添加到标准模块中。

当 VBE 打开时:您需要转到 VBE > 工具 > 引用 > add a reference至Microsoft HTML Object Library
代码使用 XHR检索网页 HTML，然后使用其类属性匹配表

Set hTable = html.querySelector(".product_pane")

querySelector应用 css class selector到 HTMLDocument , 保存在变量 html , 检索匹配。

然后我使用剪贴板将表格复制粘贴到工作表中。

VBA:

Option Explicit
Public Sub GetTables()
    Dim urls(), i As Long, html As HTMLDocument, hTable As Object
    Dim ws As Worksheet, clipboard As Object, lastRow As Long

    Set clipboard = GetObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")  
    Set ws = ThisWorkbook.Worksheets("Sheet1")
    ws.Columns("C:E").ClearContents
    lastRow = ws.Cells(ws.rows.Count, "A").End(xlUp).Row
    urls = Application.Transpose(ws.Range("A1:A" & lastRow).Value)
    Set html = New HTMLDocument

    With CreateObject("MSXML2.XMLHTTP")
        For i = LBound(urls) To UBound(urls) Step 6
            .Open "GET", urls(i), False
            .send
            html.body.innerHTML = .responseText
            Set hTable = html.querySelector(".product_pane")
            clipboard.SetText hTable.outerHTML '5 rows per table
            clipboard.PutInClipboard
            ws.Range("C" & i).PasteSpecial
        Next
    End With
End Sub

表 1 中的示例布局:

*运行代码后返回 C:E 列中的值