Como eu adiciono os dados da web do Excel, usando a atualização, em uma nova coluna ou planilha sem substituir os dados antigos

Question

Estou usando os dados da web do Excel para mover uma tabela de valores de um website para o Excel. Meu período de atualização é a cada 10 minutos. O problema com a atualização para meus propósitos é que substitui os valores antigos pelo novo. Preciso preservar os dados da tabela a cada 10 minutos (vou plotar isso) para que a progressão dos valores seja importante (não apenas o valor atual). Eu vou executar isso enquanto estou no trabalho, por isso deve ser automatizado.

De preferência, os dados serão adicionados a uma única planilha, mas se precisar de uma nova planilha com todos os dados despejados, tudo bem.

Alguém sabe se o Excel Web Data pode fazer isso? Ou algo parecido? VBA está fora porque o HTML é muito difícil de navegar (para mim) e a tabela tem muitos campos exclusivos que eu precisaria chamar e percorrer.

Obrigado,

TMME

    

Answer 1

A ferramenta de consulta da Web do Excel não é capaz de preservar os dados buscados anteriormente.

No entanto, usando o VBA, é muito fácil copiar automaticamente os dados da tabela de consulta no Excel toda vez que a Web Query a atualiza.

Siga estas etapas para configurar uma pasta de trabalho para demonstrar a técnica:

1) Crie uma nova pasta de trabalho com duas planilhas, WebQuery e USD .

2) Selecione a célula A1 da planilha WebQuery e inicie uma nova Consulta na Web usando o endereço https://www.xe.com/currencyconverter/ .

3) Role para baixo até a tabela XE Live Exchange Rates e importe-a.

4)NomóduloThisWorkbook,adicioneestecódigo:

'============================================================================================ ' 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) Crie um novo módulo de classe chamado clsQueryTable e coloque este código nele:

'============================================================================================
' 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) Defina o Web Query para atualizar automaticamente a cada minuto.

7) Salve e feche a pasta de trabalho

Quando você reabrir a pasta de trabalho, a Web Query começará a ser atualizada a cada minuto e a primeira coluna de dados da tabela XE Live Exchange Rates (as taxas de câmbio atuais do USD) será armazenada na folha USD .

Esta demonstração apenas extrai uma coluna de dados, mas qualquer / todos os dados da tabela podem ser copiados da mesma maneira.

Observe que a demonstração funcionará corretamente como está em qualquer tabela <<>> de qualquer URL, já que o código se ajusta automaticamente para o tamanho da tabela.