登录时VBA下载文件不起作用 [英] VBA downloading file with login isn't working

问题描述

我正在尝试从该网站下载文件，尝试了一些我可以找到的代码，并且文件被下载，但显示了登录页面的html

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

以下是我尝试的2个版本.我尝试了所有可以在SO上找到的代码段，但到目前为止还算不上运气.

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

我在这里尝试了两个版本，它们都有相同的问题，但是它们的解决方案对我不起作用. Vba从Internet WinHttpReq下载文件，但登录无法正常工作

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

似乎我没有结束登录过程.我知道下面的代码中的变量(用户名，密码)是错误的，但是我确实尝试了可以​​在源代码中找到的每个变量(UniqueUser，UniqueLogin，LoginName，他们在其中遇到的每个单词)，但还是没有运气.

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.

SET COOKIE行上的某些版本的代码错误，其他版本没有错误，文件已下载，但仍然是文件内登录页面的

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

推荐答案

您正在将登录详细信息发送到错误的登录地址.您的正确登录地址为 https://pnds.health.ny.gov/account/login该页面需要LoginName和Token.令牌是使用SecurityManager.generate(u，p);

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 );

您仍然可以咨询其IT团队，以确保您没有违反其政策.

这是使用IE浏览器对象的一种方法.

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

这篇关于登录时VBA下载文件不起作用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！