Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
ie Command to Wait for "File Download" and "Save As" windows
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
ie Command to Wait for "File Download" and "Save As" windows
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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
ie Command to Wait for "File Download" and "Save As" windows
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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
ie Command to Wait for "File Download" and "Save As" windows
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 - |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
ie Command to Wait for "File Download" and "Save As" windows
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 - |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
ie Command to Wait for "File Download" and "Save As" windows
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
ie Command to Wait for "File Download" and "Save As" windows
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Selecting "Save As" adds "Copy of" to file name- MS Excel 2007 | Excel Discussion (Misc queries) | |||
Excel - Golf - how to display "-2" as "2 Under" or "4"as "+4" or "4 Over" in a calculation cell | Excel Discussion (Misc queries) | |||
"Save" and "Save As" options greyed out - "Save as Webpage" option | Excel Discussion (Misc queries) | |||
Disabling "SAVE AS" option under "File" | Excel Discussion (Misc queries) | |||
Problem- Recording macros for "file save" and "File open" | Excel Programming |