VBA downloading file with login isn't working

I'm trying to download a file from this website, tried a bunch of code i can find and the file is downloaded but shows the html of the login page

Below are 2 versions that I tried. I tried every code snippet I could find on SO and have had no luck so far.

I tried both versions here, they had the same problem but their solution isn't working for me. Vba download file from internet WinHttpReq with login not working

It seems like I'm not getting past the login process. I know that the variables (username, password) are wrong in the code below, but I did try every variable I can find in the source (UniqueUser, UniqueLogin, LoginName, every word they had there) and still no luck.

Some versions of the code error on the SET COOKIE line, others give no errors, the file is downloaded but it's still the html of the login page inside the file

 Sub DownloadFile2(myURL As String)


Dim CurPath As String

CurPath = CurrentProject.Path & "\"
Dim strCookie As String, strResponse As String, _
  strUrl As String
  Dim xobj As Object
  Dim WinHttpReq As Object
  Set xobj = New WinHttp.WinHttpRequest

UN = "hhhhh"
PW = "gggg"

  strUrl = "https://pnds.health.ny.gov/login"
  xobj.Open "POST", strUrl, False
  xobj.SetRequestHeader "User-Agent", "Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/33.0.1750.154 Safari/537.36"
  xobj.SetRequestHeader "Content-Type", "application/x-www-form-urlencoded"
  xobj.Send "username=" & UN & "&password=" & PW & "&login=login"
  strResponse = xobj.ResponseText

  strUrl = myURL
  xobj.Open "GET", strUrl, False

  xobj.SetRequestHeader "Connection", "keep-alive"
  xobj.SetRequestHeader "User-Agent", "Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/33.0.1750.154 Safari/537.36"
  xobj.Send

  strCookie = xobj.GetResponseHeader("Set-Cookie")
  strResponse = xobj.ResponseBody

 If xobj.Status = 200 Then
    Set oStream = CreateObject("ADODB.Stream")
    oStream.Open
    oStream.Type = 1
    oStream.Write xobj.ResponseBody
    oStream.SaveToFile CurPath & "ValidationDataHFIS.csv", 2 ' 1 = no overwrite, 2 = overwrite
    oStream.Close
End If
End Sub


Sub ddd()

DownloadFile2 ("https://pnds.health.ny.gov/xxxx/xxxx/8")
End Sub

Solution

You are sending login details to an incorrect login address. Your correct login address is https://pnds.health.ny.gov/account/login the page expects LoginName and Token. The token is generated using SecurityManager.generate( u, p );

You can still consult with their IT Team to make sure you are not violating their policy.

Here is a way of doing it using a IE browser object.

Private Sub DownloadValidationData()
'Create Internet explorer object
Dim IE As Object
Set IE = CreateObject("INTERNETEXPLORER.APPLICATION")

IE.Visible = True

Dim URL As String: URL = "https://pnds.health.ny.gov/account/login"

IE.Navigate URL
While IE.READYSTATE <> READYSTATE_COMPLETE
    DoEvents
Wend

Dim userName As String: userName = "test"
Dim password As String: password = "test"

'Fill the login form
IE.Document.getElementById("UniqueUser").Value = userName
IE.Document.getElementById("UniquePass").Value = password

'Submit the form
IE.Document.querySelector("button.SignIn").Click

'Wait for login to complete
While IE.READYSTATE <> READYSTATE_COMPLETE
    DoEvents
Wend
'Verify you are logged in: As we don't know what the site looks like after login in. Only you can do this step.

'Navigate to Download Page. This should prompt to save the file.
IE.Navigate theDownloadUrl '"https://pnds.health.ny.gov/xxxx/xxxx/8"

'Once downloaded just close the browser and exit
'IE.Quit
'Set IE = Nothing


'If you are interested in geting/generating the token using their script you can play around with below lines. These lines come before loging in. Please note: execScript is depreciated now

'Dim Token as string
'IE.Document.parentwindow.execScript ("$('#Token').val(SecurityManager.generate(""" & username & """, """ & password & """ ))")
'Token = IE.Document.getElementById("Token").Value
'Use the token to sign in using your code. That'll be xobj.Send "LoginName =" & userName & "&Token=" & Token
'But not sure if it will work.



End Sub