Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Macro problems: Waiting for site to load and commanding Save todownload prompt
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 12
Default 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...
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 4
Default 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...


Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1,588
Default 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...


Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 12
Default 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...

Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1,588
Default 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...



Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 12
Default 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...


Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Hybrid Mode Hybrid Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Need simple prompt to save at end of macro KelliInCali Excel Programming 4 September 11th 06 06:54 PM
How do I set a macro in excel to save to a web site Mary Thomas Excel Programming 5 July 25th 06 07:47 PM
save prompt for user exit, but no save prompt for batch import? lpj Excel Discussion (Misc queries) 1 February 25th 06 02:08 AM
Can a MACRO prompt for the filename to open and/or save? Dave Peterson[_3_] Excel Programming 1 September 3rd 03 04:53 PM
Save to FTP site through a macro L Monetti Excel Programming 0 July 29th 03 03:17 PM


All times are GMT +1. The time now is 06:45 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement