test for key press

[Robert Chapman]

I have a VBA routine that runs queries in a database
application (QMF) and brings the results of all the
queries back into Excel and processes them. There is no
way of knowing when the queries are finished so the
routine uses Application.Wait to allocate enough time for
the query to run before transfering the data.

A user though is able to see when it's finished and could
press a key (e.g {End}) to tell the routine it can run the
transfer so hurrying the process up. How can I get this
to work? I tried using OnKey and a public variable but
this didn't seem to work and it's inefficient anyway; all
I need is to know at the point in time of the waiting if
the key is pressed. The wait routine would become:

Sub PauseRoutine(pause) (pause = number of seconds)
Dim i As Integer
For i = 1 To pause
Application.Wait Now + TimeValue("00:00:01")
If [end key is pressed] Then i = pause
Next i
End Sub

Any help appreciated.

Thx, Rob

 

[Shailesh Shah]

Hi Robert,

There is no way of knowing when the queries are finished

You can use AfterRefresh event for your query. For that you have to use
class module. For more details and example see VBA Help for "Using
Events with the QueryTable Object."


Regards,
Shah Shailesh
http://members.lycos.co.uk/shahweb/

 

[Tom Ogilvy]

Are you creating a query table?

Have you set the Backgroundquery property to False?

 

[Robert Chapman]

Hi Tom and Shailesh,

I'm not using Excel's query features, I'm simply using
AppActivate to switch to QMF and control QMF using
SendKeys. Afaik it's not possible to do it any other
way.

In terms of the results, I'm getting one or two columns of
data from each of a dozen queries which I compile and
format into the one financial speadsheet.

Hope this clarifies.

Rob

-----Original Message-----
Are you creating a query table?

Have you set the Backgroundquery property to False?

--
Regards,
Tom Ogilvy

I have a VBA routine that runs queries in a database
application (QMF) and brings the results of all the
queries back into Excel and processes them. There is no
way of knowing when the queries are finished so the
routine uses Application.Wait to allocate enough time for
the query to run before transfering the data.

A user though is able to see when it's finished and could
press a key (e.g {End}) to tell the routine it can run the
transfer so hurrying the process up. How can I get this
to work? I tried using OnKey and a public variable but
this didn't seem to work and it's inefficient anyway; all
I need is to know at the point in time of the waiting if
the key is pressed. The wait routine would become:

Sub PauseRoutine(pause) (pause = number of seconds)
Dim i As Integer
For i = 1 To pause
Application.Wait Now + TimeValue("00:00:01")
If [end key is pressed] Then i = pause
Next i
End Sub

Any help appreciated.

Thx, Rob

.

 

[Tom Ogilvy]

I think there are other ways besides Sendkeys:

http://www.rocketsoftware.com/qmf/

might give you some ideas.

If you still think you need to use sendkeys, then make your macro into two
parts - one to initiate the query and one to get the results. Have the
first one end, then the user can start the second one.

--
Regards,
Tom Ogilvy

Hi Tom and Shailesh,

I'm not using Excel's query features, I'm simply using
AppActivate to switch to QMF and control QMF using
SendKeys. Afaik it's not possible to do it any other
way.

In terms of the results, I'm getting one or two columns of
data from each of a dozen queries which I compile and
format into the one financial speadsheet.

Hope this clarifies.

Rob

-----Original Message-----
Are you creating a query table?

Have you set the Backgroundquery property to False?

--
Regards,
Tom Ogilvy

I have a VBA routine that runs queries in a database
application (QMF) and brings the results of all the
queries back into Excel and processes them. There is no
way of knowing when the queries are finished so the
routine uses Application.Wait to allocate enough time for
the query to run before transfering the data.

A user though is able to see when it's finished and could
press a key (e.g {End}) to tell the routine it can run the
transfer so hurrying the process up. How can I get this
to work? I tried using OnKey and a public variable but
this didn't seem to work and it's inefficient anyway; all
I need is to know at the point in time of the waiting if
the key is pressed. The wait routine would become:

Sub PauseRoutine(pause) (pause = number of seconds)
Dim i As Integer
For i = 1 To pause
Application.Wait Now + TimeValue("00:00:01")
If [end key is pressed] Then i = pause
Next i
End Sub

Any help appreciated.

Thx, Rob

.

 

[Robert Chapman]

http://www.rocketsoftware.com/qmf/

might give you some ideas.

Tom,

Yeah I'm aware of this but we don't have it in our armoury
unfortunately.

If you still think you need to use sendkeys, then make your macro into two
parts - one to initiate the query and one to get the results. Have the
first one end, then the user can start the second one.

The whole aim of the macro is that it needs to be able to
run without someone having to continually direct it.
There are so many queries to run and the operator will be
doing other time-pressing work at the time (6am). However
speed is of the essence as well so it would be useful for
them to be able to speed up some of the longer queries.
So being automatic is the number one criteria followed by
speed.

Is it possible to test for a key press somehow as
described? I would really like to know in general if this
is possible.

TIA,

Rob

 

[Tom Ogilvy]

Not if you are using wait.

See CheckKey as Stephen Bullen's site if you want to use a loop to wait for
the query:

http://www.bmsltd.co.uk/Excel/Default.htm

Its about the 10th entry under they said it couldn't be done.