General discussion

Locked

excel query

By rlnewman8 ·
what code would i need to stop the vb code finishing before the query has finished updating.
i have tried a time delay code but it delays the query as well and im left with the code running from un updated info?

This conversation is currently closed to new comments.

5 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

by PaulRH In reply to excel query

My first thought would be to have the vb code checking on a cell value that is only populated once the query has updated. Someone might be along with a more comprehensive answer soon tho.

Collapse -

by MarvisCollor In reply to excel query

I'm not a VB expert but this worked for me:
Selection.QueryTable.Refresh BackgroundQuery:=False

Visual Basic Help Info -- BackgroundQuery Optional Variant. Used only with query tables based on the results of an SQL query. True to return control to the procedure as soon as a database connection is made and the the query is submitted (the query is updated in the background). False to return control to the procedure only after all data has been fetched to the worksheet. If this argument isn?t specified, the setting of the BackgroundQuery property determines the query mode.

Collapse -

by Don Christner In reply to excel query

Here's what I did in Access to halt the Access program until the VB program finished.

DoCmd.Hourglass True
Call ExecCmd("s:\data\access\config.exe") 'This halts Access until completion of config.exe
Me!MODEL1.SetFocus
DoCmd.RunCommand acCmdPaste
DoCmd.Hourglass False

And this part went into a module in Access.
Public Sub ExecCmd(cmdline$)
Dim proc As PROCESS_INFORMATION
Dim start As STARTUPINFO
Dim ReturnValue As Integer

'intitialize the STARTUPINFO structure
start.cb = Len(start)

'start the shelled application
ReturnValue = CreateProcessA(0&, cmdline$, 0&, 0&, 1&, NORMAL_PRIORITY_CLASS, 0&, 0&, start, proc)

'wait for the shelled app. to finish
Do ReturnValue = WaitForSingleObject(proc.hProcess, 0)
DoEvents
Loop Until ReturnValue <> 258

ReturnValue = CloseHandle(proc.hProcess)
End Sub

See Microsoft article Q178116 for more details. It's not as hard as it looks, 99% of it is copy and paste.

Don

Collapse -

by rlnewman8 In reply to excel query

thanks to all that have answered my problem.

Collapse -

by beurling In reply to excel query

I had a similar problem. I founded the answer in Visual Basic Help. The topic to read is about AfterRefresh, event.

If you read all the infos about AfterRefresh, you will find the code to write in order to start your code after the Query has finished updating.

Have fun,

Beurling.

Back to Web Development Forum
5 total posts (Page 1 of 1)  

Related Discussions

Related Forums