having excel delay....

[Davers]

I'm having Excel automatically open an application, and then use the SendKeys Method to run a query in the program, and have the query export a document to my desktop, the problem is, the query takes anywhere from 10 seconds, to 10 minutes to run, depending on several factors. Is there a way other than using the Wait Method and specifying a specific length of time, to have Excel wait until the query is finished running and exporting before it goes on to the next part of the code, or key strokes? Here is the pertinent part of the code below....

'Export
SendKeys "%{f}", True
SendKeys "{e 2}", True
SendKeys "~", True
SendKeys "{TAB 8}", True
SendKeys "C:\WINDOWS\Desktop\TRACOutput.xls", True
SendKeys "~ 2", True
KillProcess
End Sub

The KillProcess shuts the program down. It unfortunately shuts the program down immediately. I've tried using SendKeys to use the File Menu to Exit the program, but that runs immediately too....any ideas??

Thanks,

Dave M.

 

[pcc]

I had a similar problem. this is a bit of code from my tool that does a similar task.
You may be able to modify it to suit yours
'***********************
myfile = ""
'now need to detect when the file has been exported
''''check that the destination file exists''
Wait
Wait
' wait a couple of seconds for the file to be initiated
' now check the file size 10 (arbitary) times
'if there's no change, then the file has completed downloading. if it changes, start again

ReDim sizearray(11)
redo:
'cont4:

flenflag = 0
redo1:
myfile = Dir(fname) ' fname is the path and filename of the file being
' created
If myfile = "" Then GoTo redo1
For check = 1 To 10
flen = FileLen(Path & myfile)
sizearray(check) = flen
'Debug.Print sizearray(check), check
Next check
If sizearray(10) <> sizearray(1) Then GoTo redo ' go back ans sample another 10 times if the sizes are diff
If flen = 0 Then flenflag = 1 ' file is zero size,
Do While myfile <> ""
myfile = Dir
Loop
' try a variable wait depending on the size of the file
varwait = Int(sizearray(10) / 200000) + 1
For mydelay = 1 To varwait
Wait
Next mydelay
' once this point is reached, then the file exists, with a constant size, so the export routine heas finished
Wait ' add a couple of waits - for some reason the code can get into a loop here
Wait

AppActivate "Display Actual Cost"
SendKeys "{F3}", True ' cancel the report
Wait
'******************

the Wait routine simply puts in a delay of 1 second - you may like to remove this, or change it to a different delay.

 

[BrianB]

1. There is a command 'DoEvents' (see VB Editor help) which is supposed to do just that, but my experience shows that I still have to use Wait. You may have better luck.

2. If you get an error message when Excel takes control too soon you could use On Error to send it back again.

3. If your external program makes a file you could probably check for its existence before proceeding.

4. Re "shuts the program down immediately" not sure what your problem is with this. Either you want shutdown or not (?). As an alternative, Alt +F4 will close most windows programs.

 

[Davers]

Thanks for responding...unfortunately I am pretty VB illiterate....the problem with the program closing right away is that the query hasn't stopped running yet....the program closes and the query stops. I don't know how to integrate a "do while" or "wait" or other methods. I guess I could just have the application stay open, and close it when I come in the next morning, but it would be nice if it would automatically close....here is the complete section of code:

Sub LaunchSiebel()

Dim strtDate, endDate, Cntr, catCode, Pos As String

strtDate = Range("strtDate")
endDate = Range("endDate")
Cntr = Range("Cntr")
Pos = Range("Pos") ' "Position" field
catCode = Range("catCode") '"Category Code" field

' Code courtesy of "www.TheScarms.com"

ProcID_Siebel = Shell("C:\Progra~1\siebel\BIN\siebel.exe /c c:\Progra~1\siebel\bin\uagent.cfg /d Server /u dmorri18 /p fidget")
'goes to the "All Activities" screen
SendKeys "%{s}", True
SendKeys "{v 2}", True
SendKeys "~", True
SendKeys "{DOWN 2}", True
SendKeys "~", True
SendKeys "^{q}", True
' go to "Center Name" column
SendKeys "{TAB 2}", True
SendKeys ">=" & strtDate & " AND " & "<=" & endDate, True
' go to the "Status" column
SendKeys "{TAB 2}", True
SendKeys Pos & " - " & "BA", True
SendKeys "{TAB 11}", True
SendKeys catCode
SendKeys "~", True
'Export
SendKeys "%{f}", True
SendKeys "{e 2}", True
SendKeys "~", True
SendKeys "{TAB 8}", True
SendKeys "C:\WINDOWS\Desktop\Queries\" & Cntr & "Output" & ".csv", True
SendKeys "~", True

'KillProcess
End Sub

I'm so close I can almost taste it.... :wink: :x

Thanks,

Dave M.

 

[Legacy 98055]

Pausing the application would not be a problem at all. However, it would be nice to test for a condition to determine when the query is finished. Use a simple loop such as the following to pause but also yield processing.

Place this in the general declaration section of the same module that your code resides.

Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)

Place this in the area you wish to delay for ten minutes.

Dim lngCounter As Long
Do While lngCounter < 6000
    lngCounter = lngCounter + 1
    Sleep 100 'wait for 1/10th second
    DoEvents 'yield processes
Loop

If you wish, think of a condition to test for and then reply with one or more of the same if you are unable to figure it out for yourself.

Tom

 

[Davers]

hmmm....which part tells it to wait for 10 minutes???

Dave M.

 

[Davers]

oh..... 1/10 of a second 6000 times.....

Thanks :wink:

 

[Davers]

Hi Tom,

How about if it checked to see if the file it was creating existed or not before it shut down... :wink: Would that be a good way??? and if so.... how would I go about doing that??

You've been a great help already!!!

Thanks,

Dave M.

 

[Juan Pablo González]

TO check if a particular file exists you can use

If Len(Dir(FullNameAndPath)) > 0 Then
'File exists
End If

 

[Legacy 98055]

Use a mix of my example and Juan's example to check for the existence of your file, once per second, in the loop. I am not sure if this will work in your case or not. The file may prove to exist as soon as it is created and not necessarily after the operation has completed. If you have any other suggestions, please post on.

Do While lngCounter < 6000
    lngCounter = lngCounter + 1
    Sleep 100 'wait for 1/10th second
    If lngCounter Mod 10 = 0 _
      And Len(Dir(FullNameAndPath)) _
      Then Exit Do
    DoEvents 'yield processes
Loop

Tom