Verversen in volgorde (query's -> powerquery's -> draaitabellen)

[robinde]

Hallo,

Ik heb een groot bestand in Excel met verschillende query's. De gegevens die uit deze query's komen heb ik samengevoegd door middel van meerdere powerquery's. Uit de gegevens van deze powerquery's heb ik draaitabellen gemaakt.

Nu wil ik door middel van 1 handeling dat eerst alle query's worden vernieuwd, vervolgens de powerquery's en vervolgens de draaitabellen.

Ik weet dat je hier VBA kan gebruiken, maar ik heb daar beperkte kennis van. Wat zou een oplossing zijn voor dit probleem? En zou het eventueel ook zonder VBA kunnen? Ik kan het bestand helaas niet delen wegens privacy-redenen.

Alvast veel dank!

 

[JEC.]

Hi Robinde,

Probeer deze eens

*knip* zie post 4

 

[robinde]

Hi Robinde,

Probeer deze eens

Sub j()
For Each sh In ThisWorkbook.Worksheets
    For Each it In sh.ListObjects
        it.QueryTable.Refresh
    Next it
    For Each Pivot In sh.PivotTables
        Pivot.PivotCache.Refresh
    Next Pivot
Next sh
End Sub

hoi JVeer,

Als ik deze VBA uitvoer dan worden alleen de query's vernieuwd. De powerquery's en draaitabellen worden niet geupdate.
Ik heb de VBA alleen in "ThisWorkbook" geplaatst. Dat klopt toch?

 

[JEC.]

In een module plaats je deze code

Iets anders, voor de volgorde. In het bestand staan 2 voorbeeld draaitabellen. Je kunt daar ook zelf even een testQuery inzetten om te uit te proberen.

Sub j()
With ThisWorkbook
  For Each sh In .Worksheets
      For Each it In sh.ListObjects
        it.QueryTable.Refresh
      Next it
  Next sh
.RefreshAll
End With
End Sub

 

[pixcel]

Drie opties:
Rechts-klik in je draaitabel > Vernieuwen
Gegevens > Alles vernieuwen
Ctrl-Alt-F5
Resultaat zou steeds hetzelfde moet zijn.

(Bij een "groot" bestand kan het zijn dat je samenvoeg queries erg hard moeten werken. Dan kan het handiger zijn om door te laden naar het datamodel en je pivots daar op te baseren.)

 

[robinde]

In een module plaats je deze code

Iets anders, voor de volgorde. In het bestand staan 2 voorbeeld draaitabellen. Je kunt daar ook zelf even een testQuery inzetten om te uit te proberen.

Sub j()
With ThisWorkbook
  For Each sh In .Worksheets
      For Each it In sh.ListObjects
        it.QueryTable.Refresh
      Next it
  Next sh
.RefreshAll
End With
End Sub

Hoi JVeer,

Als ik deze VBA uitvoer krijg ik een foutmelding bij "it.QueryTable.Refresh"
Ook worden alleen maar de query's ververst en niet de power-query's en draaitabellen

 

[JEC.]

Dat is apart, bij mij werkte het prima. Ik heb zonder bestand geen idee waar het dan aan kan liggen

 

[pixcel]

Als ik deze VBA uitvoer krijg ik een foutmelding bij "it.QueryTable.Refresh"

Komt waarschijnlijk doordat je dan een listobject it hebt die niet is verbonden met externe gegevens en dus geen querytable heeft.
Wat is bij jou een query en hoe verbind je die met power query? Een voorbeeldbestand waarmee de structuur van je werkmap duidelijk wordt zou wel handig zijn. Dat kan dan ook verklaren waarom de refreshall niet werkt.

 

[JEC.]

Precies, met de refresh all had ik verwacht dat alles hoe dan ook mee wordt genomen...

 

[robinde]

Komt waarschijnlijk doordat je dan een listobject it hebt die niet is verbonden met externe gegevens en dus geen querytable heeft.
Wat is bij jou een query en hoe verbind je die met power query? Een voorbeeldbestand waarmee de structuur van je werkmap duidelijk wordt zou wel handig zijn. Dat kan dan ook verklaren waarom de refreshall niet werkt.

Hoi Pixcel en JVeer,

Hierbij een voorbeelddocument. Let niet op de inhoud, want deze manier van verwerking van query-->draaitabel is heel omslachtig. Het gaat erom dat ik eigenlijk 3 stappen doe voordat de draaitabel gemaakt wordt.
In mijn originele bestand zitten meerdere query's die ik met powerquery combineer waaruit draaitabellen zijn gemaakt.

 

[pixcel]

Je refresh probleem ontstaat doordat je pivot verbonden is met Excel gegevens en slechts indirect met power query.
Je zou het volgende kunnen proberen:
- verwijder de 2 werkbladen met draaitabel en power query tabel
- ga naar Gegevens > Query's en verbindingen > Query's > rechts-klik op je query > laden naar > en kies voor draaitabel.

Beter zou zijn als je je Interbase via odbc direct probeert te verbinden met power query. Dan hou je alleen je draaitabel over. Tussenstappen waarbij gegevens op sheets gedumpt worden zijn vaak onnodig en werken alleen maar vertragend, zeker met grote aantallen gegevens.