ie Command to Wait for "File Download" and "Save As" windows

B

brianatee

I am importing some web data into excel. The website is .aspx so a
direct URL is not an option. Does anyone know of a command to make ie
wait until the File Download window and subsequently the Save As
window load before executing SendKeys?
 
J

JP

You want to check the "readystate" of the IE window, here is an
example.


Dim appIE As InternetExplorer
Dim sURL As String

sURL = "http://www.mapquest.com/"

Set appIE = New InternetExplorer
appIE.navigate sURL

Do Until appIE.readyState = READYSTATE_COMPLETE
Loop


HTH,
JP
 
B

brianatee

JP,

Thanks for the response. I have tried using readystate commands with
only patchy success and haven't been able to get them to to the trick
in this case. I am wondering if there is another way to make the macro
wait for the window to appear. Some sort of ActiveWindow command or
something to tell the macro to click "Save" in the donwload prompt and
the Save As window???

Any other ideas?
 
J

JP

Can you post your code?


--JP

JP,

Thanks for the response. I have tried using readystate commands with
only patchy success and haven't been able to get them to to the trick
in this case. I am wondering if there is another way to make the macro
wait for the window to appear. Some sort of ActiveWindow command or
something to tell the macro to click "Save" in the donwload prompt and
the Save As window???

Any other ideas?







- Show quoted text -
 
S

Steve Yandl

You could try something like this:

Set objIE = CreateObject("InternetExplorer.Application")
objIE.Navigate "http://www.mapquest.com/"

Do While (objIE.Busy)
Application.Wait (Now + TimeValue("0:00:05"))
Loop


Steve Yandl
 
B

brianatee

Thanks again for the help. Below is the code with the problems flagged
in comments. I have gotten through the first with a simple wait time,
but the speed of my connection varies greatly in terms of the wait at
Problems 2 and 3. I would like to avoid any other Application.Wait
(Now + TimeValue()) functions if possible.

______________________
Sub Basis()

Dim ie As Object
Dim nFile As Integer

Set ie = CreateObject("InternetExplorer.Application")

ie.Visible = True
ie.Navigate "http://www.nymex.com/settle_fut_otc.aspx"

Do Until ie.ReadyState = READYSTATE_COMPLETE
Loop

'Agrees with the Disclaimer form
If ie.LocationURL Like "*disclaimer*" Then
'Selects 'I agree'
ie.Document.Links(4).Click

'submits the form
ie.Document.getElementById("aspnetForm").submit

End If

'[PROBLEM 1 - wait time]
Do Until ie.ReadyState = READYSTATE_COMPLETE
Loop
'Readystate not enough, needed to add wait time
Application.Wait (Now + TimeValue("0:00:03"))

'clicks on "Download all available..."
ie.Document.all.Item("ctl00_btnExport").Click

'Saves the file to default location with default name
'[PROBLEM 2 - Wait for File Download Window]
Do While ie.ReadyState = 4
Loop

SendKeys "{LEFT}"
Application.Wait (Now + TimeValue("0:00:001"))
SendKeys "{ENTER}"

'[PROBLEM 3 - Wait for Save As Window]
Do Until ie.ReadyState = READYSTATE_COMPLETE
Loop
SendKeys "{ENTER}"

Do While Dir("[FILE LOCATION]") = ""
Loop
Do While FileLen("FILE LOCATION") = 0
Loop

ie.Quit

Workbooks.Open Filename:="FILE LOCATION", _

End Sub
 
B

brianatee

I just tried Steve's code in place of my previous readystate commands
and unfortunately I get an error at Problems 2 and 3 at the line

Do While (ie.Busy)

Can you try the code Steve posted and let us know if it helps?

--JP

Thanks again for the help. Below is the code with the problems flagged
in comments. I have gotten through the first with a simple wait time,
but the speed of my connection varies greatly in terms of the wait at
Problems 2 and 3. I would like to avoid any other Application.Wait
(Now + TimeValue()) functions if possible.
______________________
Sub Basis()
Dim ie As Object
Dim nFile As Integer
Set ie = CreateObject("InternetExplorer.Application")
Do Until ie.ReadyState = READYSTATE_COMPLETE
Loop
'Agrees with the Disclaimer form
If ie.LocationURL Like "*disclaimer*" Then
'Selects 'I agree'
ie.Document.Links(4).Click
'submits the form
ie.Document.getElementById("aspnetForm").submit
'[PROBLEM 1 - wait time]
Do Until ie.ReadyState = READYSTATE_COMPLETE
Loop
'Readystate not enough, needed to add wait time
Application.Wait (Now + TimeValue("0:00:03"))
'clicks on "Download all available..."
ie.Document.all.Item("ctl00_btnExport").Click
'Saves the file to default location with default name
'[PROBLEM 2 - Wait for File Download Window]
Do While ie.ReadyState = 4
Loop
SendKeys "{LEFT}"
Application.Wait (Now + TimeValue("0:00:001"))
SendKeys "{ENTER}"
'[PROBLEM 3 - Wait for Save As Window]
Do Until ie.ReadyState = READYSTATE_COMPLETE
Loop
SendKeys "{ENTER}"
Do While Dir("[FILE LOCATION]") = ""
Loop
Do While FileLen("FILE LOCATION") = 0
Loop

Workbooks.Open Filename:="FILE LOCATION", _
 
J

JP

Sorry, at this point I'm just fishing.

How about changing

Do Until ie.ReadyState = READYSTATE_COMPLETE

to

Do While ie.ReadyState <> READYSTATE_COMPLETE


Kindly allow me some time to test your code and see if I can offer any
further suggestions.


Thx,
JP
 
B

brianatee

JP,

No Luck with "Do While ie.ReadyState <> READYSTATE_COMPLETE" either.
Thanks so much for the help though. This is one of those daily tasks
that I'm looking to automate, so no hurry. I am happy to receive any
suggestions!

-Briana
 
J

John Bundy

I did something similar in .net, i can't get it to set the newly created
window as the active one but this may get you that far//

Dim bBusy as Boolean at the top, then

Public Sub NavigateToUrlSync(ByVal url As String)

bBusy = True
wb.Navigate(url)
While (bBusy)
Application.DoEvents()
End While
End Sub

Private Sub wb_DocumentComplete(ByVal sender As Object, ByVal e As
AxSHDocVw.DWebBrowserEvents2_DocumentCompleteEvent) Handles
wb.DocumentComplete
bBusy = False
End Sub

wb is the name of my webbrowser
 
V

Vishwanath N

Hi include below code or call macro1

Private Declare Function FindWindow Lib "user32" Alias "FindWindowA" ( _
ByVal lpClassName As String, _
ByVal lpWindowName As String _
) As Long
Private Declare Function ShowWindow Lib "user32" ( _
ByVal hwnd As Long, _
ByVal nCmdShow As Long _
) As Long
Private Const SW_SHOWNORMAL = 1

Sub Macro1()
Dim wHwnd As Long
wHwnd = 0
Do Until wHwnd > 0
wHwnd = FindWindow(vbNullString, "File Download")
ShowWindow wHwnd, SW_SHOWNORMAL
Loop

End Sub




*** Sent via Developersdex http://www.developersdex.com ***
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top