Click a button in IE and download the csv data

[rolando]

Hi folks,

Ive been stuck on this problem for over 4 hours now and can't seem to make it work.

I'm trying to get IE to click on the button called "output to excel" found on this website: http://whalewisdom.com/stock/WFMI

Normally I could just take the url of the button and use a webquery but it doesn't work with this website. The code I have so far is this:

Dim ieapp As Object
Public Sub test()

Dim ieapp As Object

Set ieapp = CreateObject("internetexplorer.application")

With ieapp
    .Visible = True
    .Navigate "http://whalewisdom.com/stock/WFMI" 
    Do While .READYSTATE <> 4: DoEvents: Loop
    Application.Wait (Now + TimeValue("0:00:02"))
    
    'Assuming there is only one element on the page named "Start"
   Document.GetElementById("Output To Excel").Click

End With

End Sub

the html code for the button on the website is this:

< input value="Output to Excel" on click="Stock.export_to_excel();" type="button"

does anyone know what exactly is wrong, i always get the yellow debug error when excuting at the last line, the get elementid one. i also tried variations like get elementbytag, name etc. Ive searched for hours but apparently it looks like its pretty rare to get the button identified by just input value="whatever". Normalyl id is used. Any help would really be immensely appreciated.

 

[ZVI]

Hi Rolando, welcome to the Board!

At least dot symbol is missing before Document property.
Would be .Document

Regards

 

[ZVI]

< input value="Output to Excel" on click="Stock.export_to_excel();" type="button"

Seems there is no ID for this button, so try something like this:
.Document.parentWindow.execScript "Stock.export_to_excel();", "JScript"

 

[rolando]

Thanks so much for your response!

I tried your code and it works perfectly! Its now at the stage where it asks me if I want to save it or open it etc.

Do you know if there is a way to get it to basically download the excel data straight onto a Cell on the sheet its executing from without my intervention of any sorts. Basically I would like to skip the step of opening the downloaded data onto a new sheet and copying and pasting that into my actual worksheet I'm using.

Is that possible taking this route?


Man I wish i had posted here much earlier instead of wasting all that time pulling my hair out. Thanks again!

 

[RSXchin]

ZVI,

Would you mind breaking out the code so I can alter it later if it ever comes up?

 

[ZVI]

There is no legal interface to control the open/saving dialog of IE.
And mainly it’s because of security goals.

But you can parse Web page’s contents (for example of IE.Document) to get the URL of the file to be downloaded.
And then downloads the file without IE.

The example below inserts photo of MrExcel Bill Jelen from web-page to the active cell.

' Copy  picture of Bill Jelen to the active cell
Sub Test_CopyUrlToFile()
  
  ' -> Settings, change to suit
  Const UrlFile$ = "http://www.mrexcel.com/jelenhsa72.jpg"
  Const PathName$ = "C:\Temp\Bill_Jelen.jpg"
  ' <- End of Settings
  
  If CopyUrlToFile(UrlFile, PathName) Then
    ActiveSheet.Pictures.Insert PathName
    ActiveCell.Offset(3, 3).Select
  Else
    MsgBox "Error in downloading of" & vbLf & UrlFile, vbExclamation, "CopyUrlToFile"
  End If
  
  Kill PathName
  
End Sub


' ZVI:2011-04-19 http://www.mrexcel.com/forum/showthread.php?t=544464
' Copy file from UrlFile link to the local PathName file
Function CopyUrlToFile(UrlFile As String, PathName As String) As Boolean
  Dim FN%, t!, a() As Byte
  With CreateObject("MSXML2.XMLHTTP")
    .Open "GET", UrlFile
    .Send
    t = Timer + 5  ' Timeout = 5 seconds for off-line
    While .ReadyState <> 4 And Timer < t
      DoEvents
    Wend
    On Error GoTo exit_
    FN = FreeFile
    Open PathName For Binary Access Write As #FN
    a() = .ResponseBody
    Put #FN, , a()
exit_:
    Close #FN
    CopyUrlToFile = Err = 0 And .Status = 200
  End With
End Function

Regards

 

[rolando]

Thank you, i will try that and see if i can get it to work.

RV