VBA pauze tijd inbouwen of commando voor wachten op refresh

[Boudewijn30]

Beste,

Ik heb praktisch geen verstand van macro's maar heb er hard 1 nodig. Mijn macro werkt perfect, alleen wil ik voor de zekerheid een "wacht commando" of een "wacht of refresh" regel inbouwen.

' Sneltoets: Ctrl+w
'
ActiveWorkbook.RefreshAll
Sheets(Array("Productie Monitor", "Specifieke Zorgsoorten")).Select
Sheets("Specifieke Zorgsoorten").Activate
Sheets(Array("Productie Monitor", "Specifieke Zorgsoorten")).Copy
Range("E1016").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End Sub

Dit is de macro die ik heb opgenomen en die werkt. Maar zou graag voor de "copy" een regel willen hebben dat de .Select wacht op de ActiveWorkbook.RefreshAll is gestopt met verversen.

Ik heb al meerdere fora afgestruind en hier en daar wat codes weten te vinden maar alle codes zorgden voor een compile fout. Dus mocht er een VBA expert zijn die mij kan helpen, graag! Alvast hartelijk dank!

 

[wampier]

wat wordt er gerefreshed met de refreshall? databasequeries kun je instellen dat ze "blocking" zijn en dus eerst helemaal uitgevoerd worden voordat de macro verdergaat. Echter werkt dat niet met alle types informatie

 

[Boudewijn30]

Wampier,

Het wordt nu wel heel technisch voor mij dus ik ga mijn best doen om het uit te leggen.

De sheet maakt gebruikt van een tabel met data afkomstig uit een SQL database. Benaderbaar via "Gegevens -> Bestand verbinden". Wat ik begrijp is dat het een SQLOLEDB is. Dus het trekt data uit de database en gooit deze in de voorgedefinieerde cellen en kolommen.

Mijn kennis over de techniek achter de database export is niet groot. Ik hoop dat het iets aan duidelijkheid heeft verschaft.

Groet,

 

[wampier]

In dat geval kun je de verbinding met de database instellen op: backgroundquery (verversen op achtergrond) = false

Je kunt dat "vast" doen in de instelling van de verbinding (via de grafische menu's voor de database verbinding). Je kunt het ook tijdelijk doen in je macro als je normaal wel achtergrond verversing wil hebben.

Op het moment dat het verversen op de achtergrond wordt uitgezet zal een refreshall eerst alles netjes afhandelen voordat de macro verder gaat.

 

[Boudewijn30]

Hoi Wampier,

Thanks, je uitleg is helder. Alleen van idee/concept naar uitvoering is erg lastig. Dus 1) welk commando moet ik gebruiken om de refresh eerst te laten eindigen en 2) hoe en waar in de macro moet ik deze plaatsen?


Sub Achmea_ZVW()
'
' Achmea_ZVW Macro
'
' Sneltoets: Ctrl+w
'
ActiveWorkbook.RefreshAll
Selection.QueryTable.Refresh BackgroundQuery:=False
Sheets(Array("Productie Monitor", "Specifieke Zorgsoorten")).Select
Sheets("Specifieke Zorgsoorten").Activate
Sheets(Array("Productie Monitor", "Specifieke Zorgsoorten")).Copy
Range("E10: P16").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End Sub

Ik heb de volgende commando's geprobeerd;

Selection.QueryTable.Refresh BackgroundQuery:=False
Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False
.BackgroundQuery = False

Had al enorm veel respect voor mensen die VBA beheersen, maar nu nog meer. Het is behoorlijk gecompliceerd!

 

[wampier]

Het meeste is gewoon te vinden met wat zoeken

Je macro is eerlijk gezegd ook nog niet optimaal.

Sub Achmea_ZVW()
   For Each objConnection In ThisWorkbook.Connections
         bBackground = objConnection.OLEDBConnection.BackgroundQuery
         objConnection.OLEDBConnection.BackgroundQuery = False
         objConnection.Refresh
         objConnection.OLEDBConnection.BackgroundQuery = bBackground
    Next
    DoEvents
Selection.QueryTable.Refresh BackgroundQuery:=False
Sheets(Array("Productie Monitor", "Specifieke Zorgsoorten")).Select
Sheets("Specifieke Zorgsoorten").Activate
Sheets(Array("Productie Monitor", "Specifieke Zorgsoorten")).Copy
Range("E10:p16").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End Sub

Ik heb je originele code even gekopieerd maar deze code kan ook nog veel eenvoudiger. Je ziet bijvoorbeeld dat je 2 copy statements hebt. de eerste doet dus in de praktijk niets. Als je alleen van een reeks cellen de formule wil omzetten in een vaste waarde kan dat met 1 statement en zonder gebruik van copy /pastespecial

 

[Boudewijn30]

Thanks! Zoals ik zei ik heb (nog) geen kaas gegeten van macro's maar ben nu bezig mij er in te verdiepen en zo.

De macro blijft hangen op het volgende commando: Selection.QueryTable.Refresh BackgroundQuery:=False

Foutmelding:

Fout 1004 tijdens uitvoering:
Door de toepassing of door object gedefinieerde fout

 

[wampier]

Oh die regel komt uit jouw voorbeeld. die moet er gewoon uit

 

[Boudewijn30]

Misschien een hele suffe en dooddoenende vraag maar; heb je tips voor mij om mijn VBA vaardigheden te ontwikkelen? Bijvoorbeeld een boek / website met oefenmateriaal of zo. Probleem met bijvoorbeeld Excel (dat beheers ik wel een beetje) is vaak dat ik niet bekend ben met het jargon of geen probleem heb dat ik 'moet' oplossen en dat vertraagd de ontwikkeling enorm.

Maar de macro werkt inmiddels goed! Hartelijk dank voor je hulp!

 

[OctaFish]

Simpelste tip: Volg de Excel VBA cursus hier via de Nieuwsbrief

 

[wampier]

Het moeilijkste is vaak de stap om alles als een object te zien waar je direct dingen mee kunt doen. Als je macro's opneemt volgen die logische stappen die nodig zijn als een mens iets doet, maar die zijn niet nodig om iets met een object te doen.

Je macro bevat een heleboel "activate" en "select" statements. Die zijn eigenlijk nooit nodig.

 Sheets(2).cells(1,1).value = "hallo"

Veranderd "a1" op blad twee direct in "hallo". Waar en wanneer je ook bent in je spreadsheet. een opgenmomen macro wordt vaak zoiets:

 Sheets(2).activate
 activesheet.cells(1,1).select
 selection.value = "hallo"

Het grote verschil is dat je scherm nu veranderd naar sheet2 en je cursor wordt verplaatst. Volstrekt onnodig en potentieel gevaarlijk tijdens de uitvoering van een complexe macro.