time delay in a macro

[thesnake505]

I have a macro that runs an online report, the report has an option to export the report to excell. My macro tells the report to do so, but then I have a 2nd macro to split the data up. However it takes time to down load the online report so I need a pause. Application.wait doesnt work because it stops the download process. So I need something else! Any ideas?

[Sean Thomas]

have yo tried the sleep function?

http://www.vbaexpress.com/kb/getarticle.php?kb_id=626

[thesnake505]

hi, i have tried this but i cant get it to work!

[noclass1980]

Hi, I'v had this problem as well and solved it by adding a do until loop in until sufficient time has passed to allow the other actions to complete. Insert the following lines immediately after you've started the loading process where you need to pause.

Please Login or Register  to view this content.

the above code waits 4 seconds before continuing. You might need to adjust the wait time to suit your needs. Might be a bit crude but it works for me. Depending on what the code is doing you may or may not need the DoEvents line (hence the ').

[thesnake505]

this doesnt seam to work either, it still stalls the downlaod process.

heres that part of my code

"
While ie.busy
DoEvents
Wend
Application.Wait Now + TimeSerial(0, 0, 15)
SendKeys "O", True
sec1 = Now + TimeValue("00:00:20")
Do Until Now > sec1
DoEvents
Loop


Windows("PPW.EF[1].xls").Activate"


now, i get subscrit out of range for my last command. this is because "sendkeys "O", true" is the bit that tells ie to save or install the file. but the next part paues this download process and the next command cant run!

[noclass1980]

ok, one question: why is there the 20 second delay after the Sendkeys?

I have two suggestions to try:

this is some code I wrote to open a new ie page (navigating to Google), waiting until ie ready, then pasting some previously copied text ("FText") from the ClipBoard.

Please Login or Register  to view this content.

maybe you can substitute the Google line with your web address for the file. The programme pauses until ie is ready but then i found that I had to introduce the 4 second delay before pasting in the Clipboard using the Sendkeys CTRL V but you'll need to change this to "O" (see comment on Sendkeys below).
Also make sure that you have the right References checked ("Tools", "References") as I think you might need the Microsoft Internet Controls ref checked. (Might be wrong though...)

Alternatively, you could put the sendkeys and Windows.Activate command in a separate sub (YourSubName) and use the following Code to call the sub to run after a period of time (in this case 20 seconds) and then sub continues with the rest. if the time is sufficient, this should allow ie to catch up and be ready for the sendkeys commands that you sent earlier.

Please Login or Register  to view this content.

To see how the Ontime works, paste the following into a new module of a new book and then run the "testontime" macro to see what happens.

Please Login or Register  to view this content.

Lastly, using Sendkeys can be temperamental and I've found that using/not using brackets (as in my example) can make a difference. Just have to try the different approaches I'm afraid.

[thesnake505]

The reason for the delay is because ie is converting the report to a spresdsheet, however I have just clicked, ie may not be doing it, I may need to make excel and object and have a

Please Login or Register  to view this content.

May be my problem

[noclass1980]

sorry, don't really understand what you are trying to do. good luck

[thesnake505]

Ok, we have s reportin systrm at work, my macro takes control, opens the page, inserts yesterdays date, views the report, then there is an option to export the report to excel. However the command after the export always fails because the report isnt exported. All the wait commands pause excel which also pause the download process. Which means after the pause there still isnt a report. Does this make more sence

[HitTheEXCELerator]

This is the simple timer code I use.

Sorry I can't remember the proper format for written code.

TimeDelay = Timer
StopTimer = TimeDelay + x(Time in seconds)
While TimeDelay < StopTimer
DoEvents
TimeDelay = Timer
Wend