CODEWITHSUNDEEP
excelvba
Shoaib Mohamed
Shoaib Mohamed

Reputation: 120

Excel VBA dowload file using the InternetExplorer object

So I have a VBA code that is currently successfully logging into a website and further navigating to other pages of the website and scraping data that I need.

I now need to navigate to a page (no problem), fill a textbox with a query (no problem) and click on a 'download' button (no problem). This then prompts a popup to download a file (open/save/cancel). My requirement is to save this file without user interaction - the macro should save the file in a predetermined directory.

Any ideas on how to achieve this? I couldn't get SendKeys to work at all.

Set appIE = New InternetExplorerMedium

sURL2 = "http://somewebsite.com/query.asp?"

With appIE
    .Navigate sURL2
    .Visible = True
End With

Do While appIE.Busy Or appIE.ReadyState <> 4
    DoEvents
Loop

'code to enter the query in textbox and click on download file button
    appIE.Document.getElementsByTagName("textarea")(0).Value = Sheets("UserEntry").Range("L37")
    appIE.Document.getElementsByName("btnSubmit")(203).Click
    Application.SendKeys "%{s}"

Set appIE = Nothing

Edit: Even if I could get SendKeys to work, I need to 'save as' the file automatically, not just 'save' it in the Downloads folder.

Upvotes: 1

Views: 421

Answers (1)

Shoaib Mohamed
Shoaib Mohamed

Reputation: 120

Ok, got it working, thanks to the approach suggested by @BrownishMonster

I used WinHTTP.WinHTTPRequest.5.1

I also used Fiddler to investigate what the browser was sending in the POST requests and then made VBA do the same

Set WHTTP = CreateObject("WinHTTP.WinHTTPrequest.5.1")

'This is to POST the login info and login to the site
WHTTP.Open "POST", mainUrl, False
WHTTP.setRequestHeader "Content-Type", "application/x-www-form-urlencoded"
WHTTP.send loginString

'This is to POST the download info and download the file
WHTTP.Open "POST", fileUrl_XLSResult, False
WHTTP.setRequestHeader "Content-Type", "application/x-www-form-urlencoded"
WHTTP.send downloadString

FileData = WHTTP.responseBody

'This is to save the file in the location MyFilePath
If WHTTP.Status = 200 Then
    Set oStream = CreateObject("ADODB.Stream")
    oStream.Open
    oStream.Type = 1
    oStream.Write WHTTP.responseBody
    oStream.SaveToFile MyFilePath, 1
    oStream.Close
End If

Upvotes: 1

Related Questions