Update Data Power Query and Copy Values by VBA

Asked

Viewed 50 times

0

I need to update some cells in my spreadsheet as soon as an update in the Power Query table is done, but cells only update the second time I run the code.

Sub AtualizarDadosPowerQuery()
    ActiveWorkbook.Connections("Consulta - Tabela3").Refresh
    Sheets("Relatorio").Range("B12").Value = Sheets("CNPJ").Range("D2").Value
    Sheets("Relatorio").Range("T12").Value = Sheets("CNPJ").Range("P2").Value
    Sheets("Relatorio").Range("B15").Value = Sheets("CNPJ").Range("S2").Value
End Sub

The tab "Relatorio" is the tab where values should go and not update first.
The tab "CNPJ" is the tab where values need to be searched, it does update at first.

excel vba powerquery

1 answer

1

It is necessary to define that the .Refresh does not occur in background mode (background update).

but cells only update the second time I run the code

Whereas the background update is enabled, the code triggers the .Refresh and copies the predefined cells before you have completed the update.
That is why, when executing the code for the second time, the data is being copied correctly, because now it has completed the first .Refresh and has updated data to make the copy. But it is likely that it is still running the second request for .Refresh, but you don’t understand the "mistake" this time.

Option 01:
Disable background update (background update), in Query/Properties/Use/Update Control. Screens and details in the links below, of existing answers.

Option 02:
Upgrade time is quite relative, depends on each machine, file, connection etc. Carefully evaluate if it is applicable in your case. It is possible to set a delay for VBA between the .Refresh and the next lines, which copy the values. The code below leaves an interval of 5 seconds:

Sub AtualizarDadosPowerQuery()
    ActiveWorkbook.Connections("Consulta - Tabela3").Refresh

    Application.Wait (Now + TimeValue("0:00:05"))

    Sheets("Relatorio").Range("B12").Value = Sheets("CNPJ").Range("D2").Value
    Sheets("Relatorio").Range("T12").Value = Sheets("CNPJ").Range("P2").Value
    Sheets("Relatorio").Range("B15").Value = Sheets("CNPJ").Range("S2").Value
End Sub

See also:

  • Thank you very much, it worked!

Browser other questions tagged excel vba powerquery

You are not signed in. Login or sign up in order to post.