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

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

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

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


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

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

Have you defined READYSTATE_COMPLETE in your code ?

Tim

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

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

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

On Jan 4, 8:50 pm, "Tim Williams" <timjwilliams at gmail dot com
wrote:
Have you defined READYSTATE_COMPLETE in your code ?

Tim

wrote in message

...

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

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

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

Tim


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

On Jan 4, 8:50 pm, "Tim Williams" <timjwilliams at gmail dot com
wrote:
Have you defined READYSTATE_COMPLETE in your code ?

Tim

wrote in message

...

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

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

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.

On Jan 7, 10:15 pm, "Tim Williams" <timjwilliams at gmail dot com
wrote:
ie.document.execCommand "SaveAs", false, "exportText.txt"

Tim

wrote in message

...

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

On Jan 4, 8:50 pm, "Tim Williams" <timjwilliams at gmail dot com
wrote:
Have you defined READYSTATE_COMPLETE in your code ?

Tim

wrote in message

...

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