Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
test for key press
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
test for key press
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/ *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
test for key press
Are you creating a query table?
Have you set the Backgroundquery property to False? -- Regards, Tom Ogilvy "Robert Chapman" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
test for key press
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 "Robert Chapman" wrote in message ... 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 . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
test for key press
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 "Robert Chapman" wrote in message ... 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 "Robert Chapman" wrote in message ... 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 . |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
test for key press
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
test for key press
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. -- Regards, Tom Ogilvy "Robert Chapman" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
find&press button | New Users to Excel | |||
when i press TAB key in an excel sheet nothing happens? | Excel Discussion (Misc queries) | |||
Calculate mean of test scores from rows of test answers | Excel Discussion (Misc queries) | |||
Information hidden when I press F2 | Excel Discussion (Misc queries) | |||
When I Press the Enter Key Nothing happens. | Excel Discussion (Misc queries) |