Download PDF from URL in Excel VBA

Question

looking to download a PDF from a URL. I can't seem to get past the login screen as the pdf that is downloaded only contains the code for the login page if I open it in NotePad. I examined the post request after logging in manually and pasted it after "FormData." I'm not sure if it matters what I called this variable? In one of the posts I reference below, he used "strAuthenticate."

When I examine the pdf that I want to download in chrome DevTools, it says this:

<embed id="plugin" type="application/x-google-chrome-pdf"                                 
src="***SAME AS FILE URL IN VBA CODE***" 
stream-url="chrome-extension://mhjfbmdgcfjbbpaeojofohoefgiehjai/761e8d06-3486-4bab-b043-df5b9a3c2510"     headers="accept-ranges: bytes
cache-control: max-age=1, must-revalidate
content-length: 375845
content-type: application/pdf
date: Wed, 06 May 2020 23:35:10 GMT
etag: 1588701484391
expires: Thu, 07 May 2020 00:35:10 GMT
last-modified: Tue, 05 May 2020 17:58:04 GMT
p3p: policyref=&quot;/w3c/p3p.xml&quot;, CP=&quot;NON DSP CURa ADMa DEVa TAIa OUR BUS IND UNI COM NAV INT&quot;
server: Microsoft-IIS/10.0
status: 200
x-content-type-options: nosniff
x-included-test: true
" background-color="0xFF525659" top-toolbar-height="56" javascript="allow" full-frame="">

Does it matter that the the pdf is a plugin rather than an attachment. Also the src is the site as what I have in the fileUrl in the below code.

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

mainUrl = "https://www.website.com/j_security_check"
fileUrl = "https://www.website.com.com/controlFileRetrieve?ignorePresentViaObject=true&curDomId=111&posId=4574137"
filePath = "C:\myfile.pdf"

myuser = "xxxxxx"
mypass = "xxxxxx"

j_security_check = "j_username=" & myuser & "j_password=" & mypass

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


WHTTP.Open "POST", mainUrl, False 'WHTTP.Open "POST", fileUrl, False
WHTTP.setRequestHeader "Content-Type", "application/x-www-form-urlencoded"
WHTTP.send j_security_check


WHTTP.Open "GET", fileUrl, False
WHTTP.send

Debug.Print WHTTP.getAllResponseHeaders()

FileData = WHTTP.responseBody
Set WHTTP = Nothing

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

Links that I have referenced or looked at:

VBA WinHTTP to download file from password proteced https website

How to make a POST request to a page that may redirect to a login page

Any help is appreciated!

Answer 1

This works for me.

Sub TryMe()
    Dim i As Long
    Dim FileNum As Long
    Dim FileData() As Byte
    Dim MyFile As String
    Dim WHTTP As Object
    Dim lrow As Long
    Dim ws As Excel.Worksheet

    On Error Resume Next
        Set WHTTP = CreateObject("WinHTTP.WinHTTPrequest.5")
        If Err.Number <> 0 Then
            Set WHTTP = CreateObject("WinHTTP.WinHTTPrequest.5.1")
        End If
    On Error GoTo 0

    If Dir("C:\MyDownloads", vbDirectory) = Empty Then MkDir "C:\MyDownloads"

    Set ws = ThisWorkbook.Worksheets("Sheet1")

    With ws
        For lrow = 1 To .Cells(.Rows.Count, "A").End(xlUp).Row
            MyFile = Cells(lrow, 1).Text
            TempFile = Right(MyFile, InStr(1, StrReverse(MyFile), "/") - 1)
            WHTTP.Open "GET", MyFile, False
            WHTTP.Send
            FileData = WHTTP.ResponseBody

            FileNum = FreeFile
            Open "C:\MyDownloads\" & TempFile For Binary Access Write As #FileNum
                Put #FileNum, 1, FileData
            Close #FileNum
            DoEvents
        Next lrow
    End With

    Set WHTTP = Nothing
    MsgBox "Open the folder [ C:\MyDownloads ] for the downloaded file..."
End Sub

Setup: