Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations sizbut on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Delay the completion of a macro 2

Status
Not open for further replies.

GoKingBee

Technical User
Mar 5, 2004
10
CA
I have a spreadsheet that accesses an external datasource using MSQUERY. I have written a macro that will REFRESH the data from the datasource. As you know this can take up to 20 seconds to complete.

The spreadsheet is Worksheet Protected. Part of the macro is to Unprotect the worksheet before the Refresh then to Protect it again once the Refresh is complete.

The problem I have is that the code to Protect the worksheet is running before the Refresh completes. This causes an error because the Refresh is now writing to a Protected sheet.

Can I delay the execution of the Protect Worksheet macro command until the Refresh is complete?

thx
Bill King
 
The best way i've found...is to record the steps of editing your query (even if it's already done)and returning the data to excel.

this will create a vba version of your linked query...vba by default will wait until the data is returned by it's own request.

This is also a way to splice strings into your sql statement that MSQuery is not capable of.
 
you could record the time at starting the code such as
startime = time
then say at the end of of the code
startime = time
REFRESH DATA FROM MS QUERY

do until endtime > starttime + 45 seconds
endtime = time'do nothing really, but record current/end time


[yinyang] Tranpkp [pc2]
 
Oh yeah...then you call this code to refresh your query...

instead of calling for a refresh of the query via code.
 
Have you tried something like this ?
YourQueryTable.Refresh BackgroundQuery:=False

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884
 
I had a similar request a while back, my macro ran an outside C++ program and would continue even though data was required from the c++ program and fall over.
The best solution that I found (Probably somebody will recognise it as more than likely I got it from here)
is...
PUT IN DECLARATIONS
Private Const SYNCHRONIZE = &H100000
Private Const INFINITE = -1&

---

Public Sub RunUntilFinished(ByVal sApp As String)

Dim lProcID As Long
Dim hProc As Long

' Start the App
On Error GoTo ErrHndlr
lProcID = Shell(sApp, vbNormalFocus) ' sApp is the address of you outside program c:/....

On Error GoTo 0

DoEvents

' Wait for the App
hProc = OpenProcess(SYNCHRONIZE, 0, lProcID)
If hProc <> 0 Then
WaitForSingleObject hProc, INFINITE
CloseHandle hProc
End If
Exit Sub

ErrHndlr:
MsgBox "Error starting App:" & vbCrLf & _
"App: " & sApp & vbCrLf & _
"Err Desc: " & Err.Description
Err.Clear
End Sub


I am not aware of how the link works in your code but here the macro will hold until the c++ program is closed.

Hope this helps

It may have hit every branch on its way out of the ugly tree, but hey! It works. (but don't quote me on that)
 
whoops forgot you need this bit as well
sub waitfor
Dim sPrgm As String

sPrgm = extract$ 'extract was the name of my program

RunUntilFinished sPrgm

End Sub
Andrew

It may have hit every branch on its way out of the ugly tree, but hey! It works. (but don't quote me on that)
 
Thank you to all who responded to my question. I am a novice VBA writer and am having trouble deciphering your suggestions and how they would fit into my code. Here is my code:

Sub UpdateTenderFields1()
Sheets("Milestones").Select
ActiveSheet.Unprotect
Range("A4").Select
ActiveWorkbook.RefreshAll 'there are 3 separate queries
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
End Sub

Someone suggested:
YourQueryTable.Refresh BackgroundQuery:=False

What would this do?

Thanks again
Bill
 
YourQueryTable.Refresh BackgroundQuery:=False
Without the code I've made some assumption about your refresh method.
ActiveWorkbook.RefreshAll 'there are 3 separate queries
Right click on each of your 3 query range to uncheck the Background refresh property.
Or if you prefer the VBA way:
Sub UpdateTenderFields1()
Sheets("Milestones").Select
ActiveSheet.Unprotect
Range("A4").Select
[highlight] For Each qt In ActiveSheet.QueryTables
qt.BackgroundQuery = False
Next[/highlight]
ActiveWorkbook.RefreshAll 'there are 3 separate queries
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
End Sub

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884
 
try this


With worksheets("Milestones").querytables("milestones")

do until .refreshing=false
x=x+1
loop

end with

I know from you code that the sheet name is correct but not sure about the querytable name you will have to change that to suit. X=x+1 is simply there to give it something to do and also useful to put a break to see if the code works.
I have not checked this in any way so can't guarantee anything but it looks sensible.
Good Luck
Andrew

It may have hit every branch on its way out of the ugly tree, but hey! It works. (but don't quote me on that)
 
OH! Now I get it.

My thanks to everyone who provided assistance. Sometimes the simplest idea is the best. In this case the idea that PHV offered did the trick.

By deselecting Backgroung Refresh the macro naturally waited until the Refresh was complete before executing the next line of code. Very cool.

thx alot
Bill King
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top