Database query eerst laten uitvoeren, daarna afsluiten

[jelle87]

Goedenmorgen,

Ik wil graag met een druk op een knop een bepaald Excel bestand openen, opslaan en weer afsluiten. Dit specifieke bestand haalt echter direct na het openen gegevens op uit een database query. Dit duurt ongeveer 6 seconden, voordat alles bijgewerkt is. Ik zat daarom te denken om de macro 10 seconden te laten wachten, en daarna deze macro het bestand op te laten slaan en af laten sluiten.

Ik ben zo ver gekomen:

Workbooks.Open Filename:= _
"C:\Voorbeeldbestand.xlsm"
Application.Wait (Now + TimeValue("0:00:10"))
ActiveWorkbook.Save
ActiveWindow.Close

Maar hij blokkeert in principe gewoon het complete bestand, waarna hij hem (na die 10 seconden) direct afsluit. Hierdoor worden de queries dus niet bijgewerkt voordat er afgesloten wordt.

Is er een mogelijkheid om het bestand te openen, hem de tijd te geven om de database queries uit te laten voeren en daarna pas op te slaan en af te sluiten?

Alvast bedankt!

Met vriendelijke groet,

Jelle

 

[jkpieterse]

Je zou ook de queries middels VBA kunnen laten bijwerken:

ActiveWorkbook.RefreshAll

Ik weet echter niet of dit wel wacht voordat de code verder gaat. Anders zou je het per querytabel moeten doen:

Sub RefreshActive()
    Dim oSh As Worksheet
    Dim oQT As QueryTable
    Dim oLo As ListObject
    For Each oSh In Worksheets
        For Each oQT In oSh.QueryTables
            oQT.Refresh False
        Next
        For Each oLo In oSh.ListObjects
            On Error Resume Next
            'avoid error if no query
            oLo.QueryTable.Refresh False
            On Error GoTo 0
        Next
    Next
End Sub

 

[jelle87]

Bedankt voor het meedenken!

Je zou ook de queries middels VBA kunnen laten bijwerken:

ActiveWorkbook.RefreshAll

Ik weet echter niet of dit wel wacht voordat de code verder gaat.
[/code]

Dat wordt hem helaas niet. De bestanden worden namelijk ook afzonderlijk geopend en moeten op dat moment wel bijwerken.

Anders zou je het per querytabel moeten doen:

Sub RefreshActive()
    Dim oSh As Worksheet
    Dim oQT As QueryTable
    Dim oLo As ListObject
    For Each oSh In Worksheets
        For Each oQT In oSh.QueryTables
            oQT.Refresh False
        Next
        For Each oLo In oSh.ListObjects
            On Error Resume Next
            'avoid error if no query
            oLo.QueryTable.Refresh False
            On Error GoTo 0
        Next
    Next
End Sub

Dit stops het bijwerken inderdaad. Echter, nadat hij dit gedaan heeft, zal hij toch nog alles moeten vernieuwen, daarna het bestand opslaan en afsluiten. Hoe krijg ik dat er bij in?

 

[jkpieterse]

De routien stopt het bijwerken niet, het voert het juist uit. Roep die routine aan voordat je het bestand weer sluit.

 

[jelle87]

Ik ben er (nog) niet helemaal in thuis, zoals je waarschijnlijk door hebt. Ik heb nu dit achter elkaar gezet, maar dat werkt niet:

Private Sub CommandButton1_Click()

Workbooks.Open Filename:= _
"C:\Voorbeeldbestand.xlsm"
End Sub

Sub RefreshActive()
Dim oSh As Worksheet
Dim oQT As QueryTable
Dim oLo As ListObject
For Each oSh In Worksheets
For Each oQT In oSh.QueryTables
oQT.Refresh False
Next
For Each oLo In oSh.ListObjects
On Error Resume Next
'avoid error if no query
oLo.QueryTable.Refresh False
On Error GoTo 0
Next
Next
ActiveWorkbook.RefreshAll
ActiveWorkbook.Save
ActiveWindow.Close
End Sub

Wat doe ik fout ?

 

[snb]

Gebruik in het te openen bestand de afterrefresh -gebeurtenis van iedere querytable:


Maak een klassemodule (in dit voorbeeld 'clsModQt') met:

Public WithEvents qt As QueryTable

Private Sub qt_AfterRefresh(ByVal Success As Boolean)
    y=y+1
    if y=12 then qt.parent.parent.close 0 
End Sub

Maar dit alles lijkt me te complex voor amateurs.

 

[jelle87]

Maar dit alles lijkt me te complex voor amateurs.

Dat kan ik bevestigen, haha. Ik kan dit wel simpelweg overnemen, maar heb dan werkelijk geen idee waar ik mee bezig ben.

Er is geen andere mogelijkheid, bijvoorbeeld een soort timer die aftelt en dan na 10 seconden het bestand weer afsluit? In die 10 seconden is alles wel bijgewerkt namelijk.

 

[jkpieterse]

Gewoon:

Private Sub CommandButton1_Click()

 Workbooks.Open Filename:= _
 "C:\Voorbeeldbestand.xlsm"
 [B][COLOR="#FF0000"]RefreshActive[/COLOR][/B]
 End Sub

 

[jelle87]

Ik ga er mee aan de slag, nogmaals bedankt!