< návrat zpět

MS Excel

Téma: Makro refreh dotazů z Power Query rss

Zaslal/a radek.jirkov 7.11.2019 18:21

potřeboval bych pomoci s jedním problémem ohledně makra v excelu.
Mám vytvořené makro, které mimo jiné zahrnuje refresh tabulek excelu ze dvou různých souborů, které jsou propojeny dotazy do jedné tabulky v Power Query a makrem provádím jejich aktualizaci, ovšem i když tento příkaz není posledním příkazem v makru, tak vždycky se aktualizace tabulek (refresh dotazů) provede až po skončení makra a tím se přemažou v tabulce jiné operace, které by měly následovat až po refresh tabulek s dotazy, Nyní to řeším rozdělením na 2 makra :
1. makro - provádí úpravy v tabulkách a refresh dotazů v Power Query ( je spuštěno automaticky při otevření souboru excel)
2. makro - provede další úpravy tabulky (přiřazeno tlačítko) ovšem problémem je když někdo tlačítko zapomene stisknout

Potřebuji tyto 2 makra spojit a dodržet, aby příkazy v makru byly spuštěny a dokončeny v pořadí, jak jsou v makru uvedeny, když výše uvedené 2 makra spojím, tak mi refresh dotazů přemaže příkazy, které v makru následují až po refresh dotazů. Předem děkuji za pomoc..

Zaslat odpověď >
MePExG(7.11.2019 18:38)#044940
MePExG
Bod 1. nie je potrebné makro, ale stačí nastaviť vlastnosť výstupného=výsledného PQ dotazu, aby sa spustil pri otvorení zošitu.
Tu je makro na aktualizáciu všetkých PQ dotazov v zošite
Public Sub UpdatePowerQueriesOnly()
Dim lTest As Long, cn As WorkbookConnection
On Error Resume Next
For Each cn In Workbook.Connections
lTest = InStr(1, LCase(cn.OLEDBConnection.Connection), "provider=microsoft.mashup.oledb.1")
If Err.Number <> 0 Then
Err.Clear
Exit For
End If
If lTest > 0 Then cn.Refresh
Next cn
End Subcitovat
radek.jirkov(7.11.2019 19:10)#044941
avatar
Děkuji za příspěvek a pomoc:

Pokud tomu dobře rozumím:
- nahradím můj příkaz na refresh dotazů v PQ výše uvedeným
- a nakopíruji příkazy z makra 2 do makra 1 a budu mít jedno výsledné makro, které postupně provede všechny kroky v makrucitovat
MePExG(7.11.2019 20:03)#044942
MePExG
Nemyslel som to tak.
- prvé makro nie je potrebné, lebo stačí nastaviť vlastnosť PQ (koncového) dotazu [pravé tlačidlo myši na dotaz], podľa priloženého obrázku
- druhé makro vyneste (bez aktualizácie) na spustenie napr. na tlačidlo
* priložné makro je len pre prípad, ak chcete mať možnosť aktualizovať PQ pomocou programu (žiaľ doposiaľ som nenašiel postup ako (aktualizáciu PQ) programovo otestovať aby následne mohla bežať ďalšia časť kódu, lebo VBA nečaká na PQ.
Příloha: png44942_refreshpq.png (15kB, staženo 59x)
44942_refreshpq.png
citovat
elninoslov(8.11.2019 7:08)#044943
elninoslov
Toto by malo počkať. Či ?
Sub RefreshPQ()
Dim bBackQ As Boolean
Cells(1, 5).Value = "Refreshing"
With ThisWorkbook.Connections("Dotaz – Pokus").OLEDBConnection
bBackQ = .BackgroundQuery
.BackgroundQuery = False
.Refresh
.BackgroundQuery = bBackQ
End With
Cells(1, 5).Value = "Hotovo"
MsgBox "Refresh hotový"
End Subcitovat
radek.jirkov(8.11.2019 15:06)#044944
avatar
Děkuji za návrh,zkusil jsem a změnil jsem název dotazu, ale makro mi skončí a ukáže chybu, když načítá With ThisWorkbook.Cennections("Dotaz - Synthese").OLEDBConestion
Níže je uvedeno, jak jsem to vložil...

' akualizacesyntheseII Makro
'

'
ActiveWorkbook.Connections("Dotaz – Synthese").Refresh
ActiveWorkbook.Connections("Dotaz – Summary").Refresh
Sheets("Synthese II").Select
Range("M5").Select

' Sub RefreshPQ()
Dim bBackQ As Boolean
Cells(1, 5).Value = "Refreshing"
With ThisWorkbook.Connections("Dotaz - Synthese").OLEDBConnection
bBackQ = .BackgroundQuery
.BackgroundQuery = False
.Refresh
.BackgroundQuery = bBackQ
End With
Cells(1, 5).Value = "Hotovo"
MsgBox "Refresh hotový"citovat
elninoslov(9.11.2019 11:34)#044946
elninoslov

mepexg napsal/a:

...žiaľ doposiaľ som nenašiel postup ako (aktualizáciu PQ) programovo otestovať aby následne mohla bežať ďalšia časť kódu, lebo VBA nečaká na PQ...

No dalo by sa to takouto triedou.
Toto riešenie počíta:
- s tým, že je "Obnovovať na pozadí" zapnuté - čo je výhoda, lebo dotazy bežia naraz.
- s tým, že dané dotazy sú vizualizované v Tabuľke.
- s tým, že v tomto prípade ide o všetky dotazy v zošite, ale dá sa to určiť iba na tie ktoré chcete - podľa ich výsledných Tabuliek.
- nespúšťate to makrom, ale normálne cez Obnoviť Tabuľku alebo Obnoviť všetko v Ribbone.

Ak to je treba s "Obnovovať na pozadí" vypnutým, tak to treba preprogramovať úplne inak - ešte som neskúšal.

POZOR - akákoľvek zmena v makre spôsobí nefunkčnosť, a treba znovu spustiť Initialization.

Posielam to aj s pokusnými súbormi, tak je to väčšie (4,7 MB) na GoogleDrive

Vyskúšajte, ale zmente si v dotazoch umiestnenie súborov, bo ja to mám "Z:\PQ čakaj\"citovat
radek.jirkov(10.11.2019 14:54)#044948
avatar
Moc děkuji, ještě to potřebuji aplikovat do mého makra, abych to nezkazil, můj email je radek.muller@parker.com můžu poprosit jestli by jste mi napsal na tento email, také bych vás rád odměnil za Váš čas při řešení mého problému.citovat
lubo(11.11.2019 10:24)#044952
avatar
Inspirativní je odkaz:

https://github.com/IvanBond/Power-Refresh

Řešil jsem aktualizaci a následné uložení tabulky.
Při běhu proqramu záleželo na aktuální verzi pq.

Jako nevyhovující se ukázaly verze s krátkým cyklem aktualizace. Po přechodu na půlroční kanál jsou problémy nevýznamné.citovat
radek.jirkov(11.11.2019 10:36)#044953
avatar
V příloze je uloženo moje současné makro, na konci proběhne aktualizace dotazů Power query, následně potřebuji aby se spustilo makro8(které mi vloží do tabulky další data) můžete mi prosím ty příkazy vložit přímo do přílohy,aby Power query na konci makra zaktualizovalo dotazy a následně spustilo makro8. Děkuji
Sub Makro1()
''
' ulozeni Makro
'
'
ActiveWorkbook.Save
Range("E4").Select
' Makro1 Makro
'
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
' Extraction des commandes client
Application.Run "CDES_CLIENTS"
' Extraction des Ordres d'achats
' Application.Run "OP"
' Extract Work orders
Application.Run "WO_list"
' Extraction des manquants
Application.Run "Shortage"
' Tri des cdes clients
Application.Run "Sort1"
' Copie des WOs by customers orders
Application.Run "WO"
' Mise en forme
Application.Run "Mise_en_forme"
' Manquants sur WO
Application.Run "manquants"
' Récupération des données précédemment saisies
Application.Run "Reprise"
Sheets("Synthese").Activate
Calculate
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
'
' uprava1 Makro
'
'
Range("Y5").Select
Sheets("Synthese II").Select
Range("M3").Select
'
' Aktualizace Makro
'
'
'zalohacont1a Makro
'
Range("H6:H1000").Select
Selection.Copy
Sheets("zalohacont1").Select
Range("A1").Select
ActiveSheet.Paste
Sheets("Synthese II").Select
Range("M6:M1000").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("zalohacont1").Select
Range("B1").Select
ActiveSheet.Paste
Sheets("Synthese II").Select
Range("AA6:AA1000").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("zalohacont1").Select
Range("C1").Select
ActiveSheet.Paste
Sheets("Synthese II").Select
Range("AH6:AH1000").Select
Application.CutCopyMode = False

Module9 - 2
Selection.Copy
Sheets("zalohacont1").Select
ActiveWindow.SmallScroll Down:=-12
Range("E1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveWindow.SmallScroll Down:=-9
Range("I24").Select
Sheets("Synthese II").Select
Range("U6:U1000").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("zalohacont1").Select
ActiveWindow.SmallScroll Down:=-15
Range("F1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("I12").Select
Sheets("Synthese II").Select
Range("O3").Select
' akualizacesyntheseII Makro
'
'
ActiveWorkbook.Connections("Dotaz – Synthese").Refresh
ActiveWorkbook.Connections("Dotaz – Summary").Refresh
Sheets("Synthese II").Select
Range("M5").Select
Po aktualizaci P Query potřebuji spustit makro8, které doplní tabulku o další údaje, bohužel vždy Power query aktualizuje soubory až po dokončení maker a tím vložená data makrem 8 vymaže. Potřebuji zachovat toto pořadí v Makro1.citovat
radek.jirkov(11.11.2019 11:04)#044955
avatar
Neumím programovat ve VBA makra v podstatě nahrávám, tak budu vděčný, když mi domého makra ty úpravy někdo nakopíruje..děkuji za pochopenícitovat

Uživatelské menu

Nejste přihlášen(a)
avatar\n

Menu

Formulář Faktura

Formulář Faktura IV

Oblíbený formulář Faktura byl vylepšen a rozšířen.
Více se dočtete zde.

Helios iNuvio

Používáte podnikový systém Helios iNuvio? Potřebujete pomoci se správou nebo vyvinout SQL proceduru? Více informací naleznete na stránce Helios iNuvio.

On-line nástroje