Excel..Wait for Refreshing to finish Updating

[Newbie2223]

Hi All

I have 2 macro that work well..the only problem is that I manually wait a 1minute to ensure that my worksheets have update befroe saving the Data to a .txt file.
Is there a way to make sure Refresh has stopped on the last sheet before continuing the macro

I have added 'Pls Help in my macro below.

Sub Update_Sheet()
'
' Update_Sheet Macro
' Macro recorded 05/11/08 by Matt
'
' Keyboard Shortcut: Ctrl+Shift+U
'
Sheets("PLS1").Select
Cells(1, 1).Select
Selection.QueryTable.Refresh BackgroundQuery:=False
Sheets("PLS2").Select
Cells(1, 1).Select
Selection.QueryTable.Refresh BackgroundQuery:=False
Sheets("PLS3").Select
Cells(1, 1).Select
Selection.QueryTable.Refresh BackgroundQuery:=False
Sheets("PLS4").Select
Cells(1, 1).Select
Selection.QueryTable.Refresh BackgroundQuery:=False
Sheets("PLS5").Select
Cells(1, 1).Select
Selection.QueryTable.Refresh BackgroundQuery:=False
Sheets("PLS6").Select
Cells(1, 1).Select
Selection.QueryTable.Refresh BackgroundQuery:=False
Sheets("PARAMETERS").Select
Range("A17").Select
'Pls Help
'Below is the part that I can't get to work ..I am trying to wait for the Last Sheet to
'stop refreshing before the macro continues to copy and save the Data to a simple .txt
'file
Do
Do While .Refreshing
Loop
Exit Do
'
'below is the saving part of the macro...works fine
Dim r, c As Integer
Close
Open "D:\Just_Text.txt" For Append As #1
For i = 3 To 7
With Worksheets(i).UsedRange
For r = 1 To .Rows.Count
For c = 1 To .Columns.Count - 1
Print #1, .Cells(r, c); "|";
Next c
Print #1, .Cells(r, .Columns.Count)
Next r
End With
Next i
Close #1
End Sub

 

[PHV]

You may consider the DoEvents function.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SkipVought]

BackgroundQuery:=False means that the query refresh happens in real time, one refresh after the other.

Sub Update_Sheet()
'
' Update_Sheet Macro
' Macro recorded 05/11/08 by Matt
'
' Keyboard Shortcut: Ctrl+Shift+U
'
    Sheets("PLS1").QueryTables(1).Refresh BackgroundQuery:=False
    Sheets("PLS2").QueryTables(1).Refresh BackgroundQuery:=False
    Sheets("PLS3").QueryTables(1).Refresh BackgroundQuery:=False
    Sheets("PLS4").QueryTables(1).Refresh BackgroundQuery:=False
    Sheets("PLS5").QueryTables(1).Refresh BackgroundQuery:=False
    Sheets("PLS6").QueryTables(1).Refresh BackgroundQuery:=False

    Dim r, c As Integer
'    Close   ???
    Open "D:\Just_Text.txt" For Append As #1
    For i = 3 To 7
    With Worksheets(i).UsedRange
    For r = 1 To .Rows.Count
    For c = 1 To .Columns.Count - 1
    Print #1, .Cells(r, c); "|";
    Next c
    Print #1, .Cells(r, .Columns.Count)
    Next r
    End With
    Next i
    Close #1
    End Sub

Why are you going thru FIVE sheets when you imported data into SIX sheets?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Newbie2223]

Hi PHV and Skip

PHV... I'll have a look at the DoEvents

Skip...The Refreshable spredsheet was built my someone else ( IT ).
I have to work with the output..which I am trying to Automate to try and work smarter for me.

The Other Sheets are just a intro, to what the workbook does..so rubbish to me.

Thanks

Newbie2223

 

[SkipVought]

No need for ANY delays!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Newbie2223]

Hi Skip

The problem is that this worksheet's macro run's when it's open....that's the way the I.T depart. set it up.
So I thought I would add my auto saving to a .txt file at the end of the I.T worksheet update.
However my Auto saving part doesn't work.

But if I run the whole macro manually the Auto saving part does work..I assumed it had something to do with the
Spreadsheets taking time to update on first opening the workbook.
As once I run the whole macro again manully the update process is much faster..seemless.

Sorry to sound confusing

Chers
Newbie2223

 

[SkipVought]

You must be COMPLETE in your description of exactly what procedures run when.

Exactly what procedure runs when the workbook opens?

Exactly how is this procedure called?

Is it called from the Worksheet_Open event?

What do you mean by AutoSave, as I see nothing that is doing an Auto Save in your code.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Newbie2223]

Hi Skip

I have found a work around...and it works.

Re your question

Exactly what procedure runs when the workbook opens?

I truely have no idea how the spreadsheet Auto updates..I assumed it was the macro that I found
within the worbook..eg the
Selection.QueryTable.Refresh BackgroundQuery:=False
code below
But after reading your explanation I realised it was other code doing it...I have not idea where it is or how it works.
But with your help I was able to added the wait code and Do loop to end up with a .txt file.

Thanks again for the help it pointed me in the right direction

Cheers Newbie2223


Sub Workbook_Open()

newHour = Hour(Now())
newMinute = Minute(Now())
newSecond = Second(Now()) + 4
waitTime = TimeSerial(newHour, newMinute, newSecond)
Application.Wait waitTime

Do ' Outer loop.
Do While Application.Ready = False ' Inner loop.
If Application.Ready = True Then
Exit Do ' Exit inner loop.
End If
Loop
Loop Until Application.Ready = True

Sheets("PLS1").Select
Cells(1, 1).Select
Selection.QueryTable.Refresh BackgroundQuery:=False
Sheets("PLS2").Select
Cells(1, 1).Select
Selection.QueryTable.Refresh BackgroundQuery:=False
Sheets("PLS3").Select
Cells(1, 1).Select
Selection.QueryTable.Refresh BackgroundQuery:=False
Sheets("PLS4").Select
Cells(1, 1).Select
Selection.QueryTable.Refresh BackgroundQuery:=False
Sheets("PLS5").Select
Cells(1, 1).Select
Selection.QueryTable.Refresh BackgroundQuery:=False
Sheets("PLSXXL").Select
Cells(1, 1).Select
Selection.QueryTable.Refresh BackgroundQuery:=False
Sheets("PLS1").Select
Cells(1, 6).Select
Dim r, c As Integer
Close #1
Open "c:\Just_Text.txt" For Append As #1
For i = 3 To 7
With Worksheets(i).UsedRange
For r = 1 To .Rows.Count
For c = 1 To .Columns.Count - 1
Print #1, .Cells(r, c); "|";
Next c
Print #1, .Cells(r, .Columns.Count)
Next r
End With
Next i
Close #1


End Sub

 

[combo]

Alternatively, you can use events (assumed SheetA is the code name) and WithEvents declaration:

Private Sub Workbook_Open()
Set SheetA.QT1 = SheetA.QueryTables(1)
End Sub

Public WithEvents QT1 As QueryTable

Private Sub QT1_AfterRefresh(ByVal Success As Boolean)
If Success = True Then
    MsgBox "Refresh succeed!"
Else
    MsgBox "Refresh failed"
End If
End Sub

combo

 

[SkipVought]

I do not understand the problem.

I have dozens of Excel applications where a number of queries, each returning large amounts of data (tens of thousands of rows) run, one query after the other, followed by join queries within the workbook, followed by a PivotTable refreshes, all called from one Main procedure. NO TIMING ISSUES EVER!

I do not understand the problem.


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}