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?

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


On Jan 8, 2:15*pm, wrote:
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?

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?

On Jan 8, 11:21 am, JP wrote:
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

On Jan 8, 2:15 pm, wrote:

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?

Can you post your code?


--JP

On Jan 8, 4:47*pm, wrote:
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?

On Jan 8, 11:21 am, JP wrote:



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

On Jan 8, 2:15 pm, wrote:

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?- Hide quoted text -

- Show quoted text -

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



wrote in message
...
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?

On Jan 8, 11:21 am, JP wrote:
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

On Jan 8, 2:15 pm, wrote:

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?

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


On Jan 8, 1:54 pm, JP wrote:
Can you post your code?

--JP

On Jan 8, 4:47 pm, wrote:

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?

On Jan 8, 11:21 am, JP wrote:

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

On Jan 8, 2:15 pm, wrote:

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?- Hide quoted text -

- Show quoted text -

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


--JP

On Jan 8, 5:53*pm, wrote:
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

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)

On Jan 8, 3:22 pm, JP wrote:
Can you try the code Steve posted and let us know if it helps?

--JP

On Jan 8, 5:53 pm, wrote:

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

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

On Jan 8, 8:09*pm, wrote:
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)

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



On Jan 8, 5:54 pm, JP wrote:
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

On Jan 8, 8:09 pm, wrote:

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)

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


--
-John
Please rate when your question is answered to help us and others know what
is helpful.


" wrote:

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



On Jan 8, 5:54 pm, JP wrote:
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

On Jan 8, 8:09 pm, wrote:

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)

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 ***