Opening Excel files with VBA

[andland]

I am trying to open an excel file online, but I think that it requires
Java programming to do so. I want to go to
http://www.powertochoose.org/_content/_compare/compare.aspx then put
in zip code 77028 and then download the excel file on the next page. I
have figured out how to put in the zip code and get to the next page,
but not how to download the excel file (there is a link on the bottom
right of the screen). Here is what I have:

Sub AutomateIE()
Dim ie As InternetExplorer
Dim MyStr As String

Set ie = New InternetExplorer

ie.Navigate "http://www.powertochoose.org/_content/_compare/
compare.aspx"
ie.Visible = True
'Loop unitl ie page is fully loaded
Do Until ie.ReadyState = READYSTATE_COMPLETE
Loop

ie.Document.getelementbyid("txtZipCode").Value = "77028"

ie.Document.getelementbyid("cmdSearchZip").Click
Do Until ie.ReadyState = READYSTATE_COMPLETE
Loop
Application.Wait TimeSerial(Hour(Now()), Minute(Now()),
Second(Now()) + 2)
ie.Document.getelementbyid("lnkExportOffers").Click
Set ie = Nothing
End Sub

 

[GTVT06]

I am trying to open an excel file online, but I think that it requires
Java programming to do so. I want to go tohttp://www.powertochoose.org/_content/_compare/compare.aspxthen put
in zip code 77028 and then download the excel file on the next page. I
have figured out how to put in the zip code and get to the next page,
but not how to download the excel file (there is a link on the bottom
right of the screen). Here is what I have:

Sub AutomateIE()
    Dim ie As InternetExplorer
    Dim MyStr As String

    Set ie = New InternetExplorer

    ie.Navigate "http://www.powertochoose.org/_content/_compare/
compare.aspx"
    ie.Visible = True
     'Loop unitl ie page is fully loaded
    Do Until ie.ReadyState = READYSTATE_COMPLETE
    Loop

    ie.Document.getelementbyid("txtZipCode").Value = "77028"

    ie.Document.getelementbyid("cmdSearchZip").Click
    Do Until ie.ReadyState = READYSTATE_COMPLETE
    Loop
    Application.Wait TimeSerial(Hour(Now()), Minute(Now()),
Second(Now()) + 2)
    ie.Document.getelementbyid("lnkExportOffers").Click
    Set ie = Nothing
End Sub

on the buttom of your code, change
ie.Document.getelementbyid("lnkExportOffers").Click
To
ie.Document.all("lnkExportOffers").Click

 

[Andrew Landgraf]

on the buttom of your code, change
ie.Document.getelementbyid("lnkExportOffers").Click
To
ie.Document.all("lnkExportOffers").Click- Hide quoted text -

- Show quoted text -

Thanks for the help.
When I modified the program, Internet Explorer gives a message saying
that it blocked the site from downloading files on my computer. When I
manually tell it to download the file anyway, it just refreshes the
page and nothing downloads.
Is there a way to disable protection/ error messages like this in the
code or for specific websites?
I am using IE7 by the way.

 

[GTVT06]

Thanks for the help.
When I modified the program, Internet Explorer gives a message saying
that it blocked the site from downloading files on my computer. When I
manually tell it to download the file anyway, it just refreshes the
page and nothing downloads.
Is there a way to disable protection/ error messages like this in the
code or for specific websites?
I am using IE7 by the way.- Hide quoted text -

- Show quoted text -

If you set the website as a "Trusted Web Site" in your IE settings you
should be able to bypass this error.

 

[Andrew Landgraf]

If you set the website as a "Trusted Web Site" in your IE settings you
should be able to bypass this error.- Hide quoted text -

- Show quoted text -

I added the Trusted Web Site but that didn't make any difference, but
I read the IE help on how to disable the pop up blocker for certain
things and I changed those settings. Then I still got 2 messages. One
was asking me to open or save. I unchecked the box that said always
ask this question (it may have been better to figure out how to do
this in the code but it's taken me long enough). The second message
asked me if I am sure I want to open the file because it is .aspx for
some reason. I got rid of this by adding:

Application.DisplayAlerts = False

So it looks like I am good to go for now.
Thanks for your help

 

[Andrew Landgraf]

I added the Trusted Web Site but that didn't make any difference, but
I read the IE help on how to disable the pop up blocker for certain
things and I changed those settings. Then I still got 2 messages. One
was asking me to open or save. I unchecked the box that said always
ask this question (it may have been better to figure out how to do
this in the code but it's taken me long enough). The second message
asked me if I am sure I want to open the file because it is .aspx for
some reason. I got rid of this by adding:

Application.DisplayAlerts = False

So it looks like I am good to go for now.
Thanks for your help- Hide quoted text -

- Show quoted text -

Ok, there website has changed a little (plus I never really figured
out completely how to do what I was doing before). Now it creates an
XML file instead of an excel sheet. I can get it to create a dialog
box asking me what to do with the xml file (Save or Open). I would
like to be able to save the xml file to a specified location. Again,
here is what I have:

Sub AutomateIE()
Application.DisplayAlerts = False

Dim ie As InternetExplorer
Dim MyStr As String

Set ie = New InternetExplorer

ie.Navigate "http://www.powertochoose.org/_content/_compare/
compare.aspx"
ie.Visible = True

'Loop unitl ie page is fully loaded
Do Until ie.ReadyState = READYSTATE_COMPLETE
Loop

ie.Document.getelementbyid("txtZipCode").Value = "77028"

ie.Document.getelementbyid("cmdSearchZip").Click
Application.Wait TimeSerial(Hour(Now()), Minute(Now()),
Second(Now()) + 2)
Do Until ie.ReadyState = READYSTATE_COMPLETE
Loop
ie.Document.all("lnkExportOffers").Click
End Sub


Any help would be appreciated.