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.

 

[Davers]

Unfortunately the file is created right away...so the application closes down....the file is then blank.... Any other ideas?

How about if it tries to open the file...if it's not done exporting data to it, or it's still in use by the system...isn't it possible to put something in the code based on that?

I'm grasping at straws here....

Thanks for all the help!!

Dave M.

 

[Davers]

Sorry about this...or, I think it's possible to check the size of a file? How about if it keeps checking the size of the file, and if it stays the same size after several seconds then quit the app?



Dave M.

 

[Juan Pablo González]

You can get the current file size with

FileLen(PathName)

I guess you could store in a variable the file size "before" the current check, and compare it with the current one, if its the same, the file has been finished creating ??

 

[Legacy 98055]

This may or may not work. Depends on the locking used by the application which is writing to the file. I am unsure about using the file length because I doubt that the buffer will be written to the disk in a timely manner. The code below will attempt to lock the file from other applications. An error should occur if another application is already accessing it. When no error is found, it will be assumed that the query is finished.

Dim lngCounter As Long
Dim intFileHwnd As Integer

Do While lngCounter < 6000
    lngCounter = lngCounter + 1
    Sleep 100 'wait for 1/10th second
    If lngCounter Mod 10 = 0 Then
        intFileHwnd = FreeFile
        On Error Resume Next
        Open FullNameAndPath For Append Lock Read Write As #intFileHwnd
        Close #intFileHwnd
        If Err.Number = 0 Then Exit Do
    End If
    DoEvents 'yield processes
Loop

Tom

 

[Juan Pablo González]

Agree Tom, that is a nicer approach.

 

[Davers]

THANK YOU VERY MUCH!!!!! Works like a charm!!! No one will know how I get all my work done so fast in the mornings!!! :D

Thanks again!

Dave M.