☰

如何使用刷新将 Excel Web 数据添加到新列或工作表中而不覆盖旧数据

tag-icon • tag-icon microsoft-excel
如何使用刷新将 Excel Web 数据添加到新列或工作表中而不覆盖旧数据

我正在使用 Excel 的 Web 数据将一个数值表从网站移到 Excel 中。我的刷新周期是每 10 分钟一次。刷新的问题是它会用新值覆盖旧值。我需要保留每 10 分钟获取一次的表格数据（我将绘制此图），因此数值的进展很重要（而不仅仅是当前值是什么）。我会在上班时运行它，因此它必须是自动化的。

最好将数据添加到单个工作表中，但如果每次数据转储都需要一个新的工作表，那也可以。

有人知道 Excel Web Data 是否可以做到这一点吗？或者类似的东西？VBA 不行，因为 HTML 对我来说太难导航，而且表格中有太多我需要调用和迭代的唯一字段。

谢谢，

三甲氧基硅烷

答案1

Excel 的 Web 查询工具无法保存之前获取的数据。

但是，使用 VBA，可以很容易地在 Web 查询每次刷新时自动从 Excel 中的查询表中复制数据。


请按照以下步骤设置工作簿来演示该技术：

1) 创建一个包含两个工作表的新工作簿，WebQuery和USD。

2) 选择工作A1表的单元格WebQuery并使用地址开始新的 Web 查询https://www.xe.com/currencyconverter/。

3) 向下滚动到 XE 实时汇率表并导入。

Web 查询创建的屏幕截图

4）在ThisWorkbook模块中，添加以下代码：

'============================================================================================
' Module     : ThisWorkbook
' Version    : 0.1.0
' Part       : 1 of 2
' References : N/A
' Source     : https://superuser.com/a/1331097/763880
'============================================================================================
Option Explicit

Private qtExchangeRates As New clsQueryTable

Private Sub Workbook_Open()

  qtExchangeRates.InitEvents Worksheets("WebQuery").QueryTables(1)

End Sub

5）创建一个名为的新类模块clsQueryTable并将以下代码放入其中：

'============================================================================================
' Module     : Class Module clsQueryTable
' Version    : 0.1.0
' Part       : 2 of 2
' References : N/A
' Source     : https://superuser.com/a/1331097/763880
'============================================================================================
Option Explicit

Public WithEvents QueryTable As QueryTable

Private Sub QueryTable_AfterRefresh(ByVal Success As Boolean)

  If Success Then
    Dim varUSDExchangeRates As Variant
    varUSDExchangeRates = Me.QueryTable.WorkbookConnection.Ranges(1).Columns(2).Value2
    varUSDExchangeRates(LBound(varUSDExchangeRates), 1) = Now
    Worksheets("USD").Range("A1").Offset(Rows.Count - 1).End(xlUp).Offset(1) _
      .Resize(ColumnSize:=1 + UBound(varUSDExchangeRates) - LBound(varUSDExchangeRates)) _
      = Excel.WorksheetFunction.Transpose(varUSDExchangeRates)
  Else
    ' Query failed or was cancelled
  End If

End Sub

Sub InitEvents(QueryTable As Object)

  Set Me.QueryTable = QueryTable

End Sub

6) 将 Web 查询设置为每分钟自动刷新。

7）保存并关闭工作簿

当您重新打开工作簿时，Web 查询将每分钟开始刷新一次，并且 XE 实时汇率表（当前美元汇率）的第一个数据列将存储在工作USD表中。

此演示仅提取一列数据，但可以以相同的方式复制表中的任何/所有数据。

请注意，演示将正常工作任何表来自任何URL，因为代码会自动根据表格的大小进行调整。

相关内容