Macro problems: Waiting for site to load and commanding Save todownload prompt

[brianatee]

I have been getting a lot of help on this forum to arrive at where I
am now. Thanks again. Hopefully this last question will be it.

Below is my current code. I have only gotten it to work sloppily and
slowly using Wait and Sendkey functions. I have flagged the problems
in comments.

Problems 1 and 2 are the same. The program does not wait until the URL
is loaded and cannot find the links to click. I think this is because
the URL changes automatically from the one entered to the disclaimer
URL, perhaps passing through a ready state in between. Is there a
command to make it wait more or to wait until a specific address is
loaded? (I have gotten around these to check the rest of the code by
using Wait functions)

Problem 3: What is the code to tell the Open/Save prompt to Save the
file and then to close IE?

Thanks for the help!
Briana


_______________________________

Sub Basis_web_query()

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"

'[PROBLEM 1]
Do Until ie.ReadyState = READYSTATE_COMPLETE
DoEvents
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 2]
Do Until ie.ReadyState = READYSTATE_COMPLETE
DoEvents
Loop

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

'Saves the file to default location with default name
'[PROBLEM 3]
Application.Wait (Now + TimeValue("0:00:20"))
SendKeys "{LEFT}"
Application.Wait (Now + TimeValue("0:00:0001"))
SendKeys "{ENTER}"
Application.Wait (Now + TimeValue("0:00:01"))
SendKeys "{ENTER}"
Application.Wait (Now + TimeValue("0:00:20"))

'[PROBLEM 3.1]
'I am anticipating that I will need a function to wait until the file
is fully downloaded before opening it

Workbooks.Open Filename:="FileLocation", _
UpdateLinks:=0
Sheets("Settlement").Select
Columns("A:E").Select
Selection.Copy

'etc...

 

[Jimmy Pena]

I believe the proper syntax is:

Do Until ie.ReadyState = READYSTATE_COMPLETE
Loop

Not

Do Until ie.ReadyState = READYSTATE_COMPLETE
DoEvents
Loop

Try that and let us know if it works.

HTH,
JP

 

[Tim Williams]

Have you defined READYSTATE_COMPLETE in your code ?

Tim

 

[brianatee]

Thanks Jimmy and Tim,

I hadn't defined readystate_complete. Can you tell this is my first
venture into the world of VBA? I have gotten that portion of the code
working now. Any hints on the latter problem (problem 3) anyone?

Thanks,
Briana

Have you defined READYSTATE_COMPLETE in your code ?

Tim

I have been getting a lot of help on this forum to arrive at where I
am now. Thanks again. Hopefully this last question will be it.

Below is my current code. I have only gotten it to work sloppily and
slowly using Wait and Sendkey functions. I have flagged the problems
in comments.

Problems 1 and 2 are the same. The program does not wait until the URL
is loaded and cannot find the links to click. I think this is because
the URL changes automatically from the one entered to the disclaimer
URL, perhaps passing through a ready state in between. Is there a
command to make it wait more or to wait until a specific address is
loaded? (I have gotten around these to check the rest of the code by
using Wait functions)

Problem 3: What is the code to tell the Open/Save prompt to Save the
file and then to close IE?

Thanks for the help!
Briana

Sub Basis_web_query()

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"

'[PROBLEM 1]
Do Until ie.ReadyState = READYSTATE_COMPLETE
DoEvents
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 2]
Do Until ie.ReadyState = READYSTATE_COMPLETE
DoEvents
Loop

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

'Saves the file to default location with default name
'[PROBLEM 3]
Application.Wait (Now + TimeValue("0:00:20"))
SendKeys "{LEFT}"
Application.Wait (Now + TimeValue("0:00:0001"))
SendKeys "{ENTER}"
Application.Wait (Now + TimeValue("0:00:01"))
SendKeys "{ENTER}"
Application.Wait (Now + TimeValue("0:00:20"))

'[PROBLEM 3.1]
'I am anticipating that I will need a function to wait until the file
is fully downloaded before opening it

Workbooks.Open Filename:="FileLocation", _
UpdateLinks:=0
Sheets("Settlement").Select
Columns("A:E").Select
Selection.Copy

'etc...

 

[Tim Williams]

ie.document.execCommand "SaveAs", false, "exportText.txt"

Tim

Thanks Jimmy and Tim,

I hadn't defined readystate_complete. Can you tell this is my first
venture into the world of VBA? I have gotten that portion of the code
working now. Any hints on the latter problem (problem 3) anyone?

Thanks,
Briana

Have you defined READYSTATE_COMPLETE in your code ?

Tim

I have been getting a lot of help on this forum to arrive at where I
am now. Thanks again. Hopefully this last question will be it.

Below is my current code. I have only gotten it to work sloppily and
slowly using Wait and Sendkey functions. I have flagged the problems
in comments.

Problems 1 and 2 are the same. The program does not wait until the URL
is loaded and cannot find the links to click. I think this is because
the URL changes automatically from the one entered to the disclaimer
URL, perhaps passing through a ready state in between. Is there a
command to make it wait more or to wait until a specific address is
loaded? (I have gotten around these to check the rest of the code by
using Wait functions)

Problem 3: What is the code to tell the Open/Save prompt to Save the
file and then to close IE?

Thanks for the help!
Briana

Sub Basis_web_query()

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"

'[PROBLEM 1]
Do Until ie.ReadyState = READYSTATE_COMPLETE
DoEvents
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 2]
Do Until ie.ReadyState = READYSTATE_COMPLETE
DoEvents
Loop

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

'Saves the file to default location with default name
'[PROBLEM 3]
Application.Wait (Now + TimeValue("0:00:20"))
SendKeys "{LEFT}"
Application.Wait (Now + TimeValue("0:00:0001"))
SendKeys "{ENTER}"
Application.Wait (Now + TimeValue("0:00:01"))
SendKeys "{ENTER}"
Application.Wait (Now + TimeValue("0:00:20"))

'[PROBLEM 3.1]
'I am anticipating that I will need a function to wait until the file
is fully downloaded before opening it

Workbooks.Open Filename:="FileLocation", _
UpdateLinks:=0
Sheets("Settlement").Select
Columns("A:E").Select
Selection.Copy

'etc...

 

[brianatee]

ie.document.execCommand "SaveAs", false, "exportText.txt"

This saves the html window that is open. Is there a command to make
the macro wait until the "File Download" window appears and then to
select Save and confirm the default name it gives?

Thanks for all the help.

ie.document.execCommand "SaveAs", false, "exportText.txt"

Tim

Thanks Jimmy and Tim,

I hadn't defined readystate_complete. Can you tell this is my first
venture into the world of VBA? I have gotten that portion of the code
working now. Any hints on the latter problem (problem 3) anyone?

Thanks,
Briana

Have you defined READYSTATE_COMPLETE in your code ?
Tim

I have been getting a lot of help on this forum to arrive at where I
am now. Thanks again. Hopefully this last question will be it.
Below is my current code. I have only gotten it to work sloppily and
slowly using Wait and Sendkey functions. I have flagged the problems
in comments.
Problems 1 and 2 are the same. The program does not wait until the URL
is loaded and cannot find the links to click. I think this is because
the URL changes automatically from the one entered to the disclaimer
URL, perhaps passing through a ready state in between. Is there a
command to make it wait more or to wait until a specific address is
loaded? (I have gotten around these to check the rest of the code by
using Wait functions)
Problem 3: What is the code to tell the Open/Save prompt to Save the
file and then to close IE?
Thanks for the help!
Briana
_______________________________
Sub Basis_web_query()
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"
'[PROBLEM 1]
Do Until ie.ReadyState = READYSTATE_COMPLETE
DoEvents
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 2]
Do Until ie.ReadyState = READYSTATE_COMPLETE
DoEvents
Loop
'clicks on "Download all available..."
ie.Document.all.Item("ctl00_btnExport").Click
'Saves the file to default location with default name
'[PROBLEM 3]
Application.Wait (Now + TimeValue("0:00:20"))
SendKeys "{LEFT}"
Application.Wait (Now + TimeValue("0:00:0001"))
SendKeys "{ENTER}"
Application.Wait (Now + TimeValue("0:00:01"))
SendKeys "{ENTER}"
Application.Wait (Now + TimeValue("0:00:20"))
'[PROBLEM 3.1]
'I am anticipating that I will need a function to wait until the file
is fully downloaded before opening it
Workbooks.Open Filename:="FileLocation", _
UpdateLinks:=0
Sheets("Settlement").Select
Columns("A:E").Select
Selection.Copy
'etc...