VBA WinHTTP to download file from password proteced https website

by

Ok, I did it. Here the code:

Sub SaveFileFromURL()

Dim FileNum As Long
Dim FileData() As Byte
Dim WHTTP As Object

mainUrl = "https://www.website.com/"
fileUrl = "https://www.website.com/dir1/dir2/file.xls"
filePath = "C:\myfile.xls"

myuser = "username"
mypass = "password"

'@David Zemens, I got this by examining webpage code using Chrome, thanks!
strAuthenticate = "start-url=%2F&user=" & myuser & "&password=" & mypass & "&switch=Log+In"

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

'I figured out that you have to POST authentication string to the main website address not to the direct file address
WHTTP.Open "POST", mainUrl, False 'WHTTP.Open "POST", fileUrl, False
WHTTP.SetRequestHeader "Content-Type", "application/x-www-form-urlencoded"
WHTTP.Send strAuthenticate

'Then you have to GET direct file url
WHTTP.Open "GET", fileUrl, False
WHTTP.Send

FileData = WHTTP.ResponseBody
Set WHTTP = Nothing

'Save the file
FileNum = FreeFile
Open filePath For Binary Access Write As #FileNum
    Put #FileNum, 1, FileData
Close #FileNum

MsgBox "File has been saved!", vbInformation, "Success"

End Sub

Thanks for all your help.

BTW I’ve found this posts very useful:

http://www.mrexcel.com/forum/excel-questions/353006-download-file-excel.html

Not understanding why WinHTTP does NOT authenticate certain HTTPS resource

How to parse line by line WinHTTP response: UTF-8 encoded CSV?

More Related Contents:

  1. Automate IE via Excel to fill in a dropdown and continue
  2. Controlling Internet Explorer local intranet using Excel VBA
  3. Get existing IE via VBA
  4. Update an Excel table with new values from another table
  5. Excel VBA, getting range from an inactive sheet
  6. Is there a way to crack the password on an Excel VBA Project?
  7. How do I declare a global variable in VBA?
  8. How to use the Implements in Excel VBA
  9. VBA code doesn’t run when cell is changed by a formula
  10. Easiest way to loop through a filtered list with VBA?
  11. What is the difference between dim and set in vba
  12. Change date format using substitute or replace
  13. Parsing an ISO8601 date/time (including TimeZone) in Excel
  14. Getting the actual usedrange
  15. Delete a row in Excel VBA
  16. VBA: Convert Text to Number
  17. How can I run VBA code each time a cell gets its value changed by a formula?
  18. How to convert 32 bit VBA code into 64 bit VBA code
  19. Referencing Excel Userform Control Name Value From String (VBA)
  20. Refreshing all the pivot tables in my excel workbook with a macro
  21. Countif With Multiple OR Criteria
  22. Vlookup to copy color of a cell
  23. every possible combination of the contents of 2 columns in excel
  24. How to delete rows in Excel based on criteria using VBA?
  25. Macro to export MS Word tables to Excel sheets
  26. Change Name of ActiveX Command Button using VBA in Excel
  27. VBA: Modify chart data range
  28. VBA Excel simple Error Handling
  29. How to do a “Save As” in vba code, saving my current Excel workbook with datestamp?
  30. How to save semi-colon delimited csv file using VBA?

Leave a Comment