VBA从Internet WinHttpReq下载文件,登录不起作用(Vba download file from internet WinHttpReq with login not working)

278 IT屋

我一直在寻找一种解决方案来自动化站点中csv表的下载量,但是我没有找到可行的解决方案.

如果我使用IE或Chrome浏览器,则在上次登录后输入URL,该文件将自动开始下载.为此,我有另一种方法,可以通过导航然后保存来通过IE和HTML对象实现所需的功能,但是它使用sendkeys,因此它不是合适的解决方案. 我还尝试通过WinHttpReq下载来实现这一目标,对我来说,这似乎是最有效,最优雅的方式.问题是:它下载了文件,但不幸的是,它输出了带有登录页面的HTML代码的csv文件->因此,它必须登录失败.该站点是HTTP.

以下是我在几个论坛上发布的代码.

Sub DownloadFile()

Dim myURL As String
valore = Range("f6").value
myURL = "www.myurlpointingtodownload.com"

Dim WinHttpReq As Object
Set WinHttpReq = CreateObject("Microsoft.XMLHTTP")
WinHttpReq.Open "GET", myURL, False, "eimail@g.com", "passs"
WinHttpReq.send

myURL = WinHttpReq.responseBody
If WinHttpReq.Status = 200 Then
    Set oStream = CreateObject("ADODB.Stream")
    oStream.Open
    oStream.Type = 1
    oStream.Write WinHttpReq.responseBody
    oStream.SaveToFile "C:\Users\mee\Desktop\fileoioi.csv", 2 
    oStream.Close
End If

End Sub

谢谢你, 鲍勃

解决方案

由于alex和Kyle的提示,我查阅了这两篇解决了我问题的文章.

nb->没有cookie处理,并且http POST的Fiddlr正文副本请求我执行2个必需的步骤

这是我的解决方案:

Sub eds()

Dim strCookie As String, strResponse As String, _
  strUrl As String
  Dim xobj As Object
  Dim WinHttpReq As Object
  Set xobj = New WinHttp.WinHttpRequest

  UN = "2myusername"
  PW = "mypass"

  strUrl = "http://my.website.com/"
  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 = http://my.website.com/date=09-10-1945%&search 'the url pointing to the CSV file
  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 "C:\Users\me\Desktop\ciao\file.csv", 1
    oStream.Close
End If
End Sub

I have been looking for a solution to automatize the dowload of a csv table from a site, but I haven't found a working solution.

If I get on IE or Chrome, after previous log in I enter the url and the file automatically start dowloading. At this purpose I have another way of achieving what I need through IE and HTML object by navigating and then saving, but it uses sendkeys and it is not a suitable solution. I also tried to achieve that by a WinHttpReq download which to me seems the most efficient and elegant way to do it. The problem is : it downloads the file but unfortunately it outputs a csv file with the HTML code of the login page -> Thus it must fail logging in. The site is an HTTP.

Following my code which I found posted as is in several forums.

Sub DownloadFile()

Dim myURL As String
valore = Range("f6").value
myURL = "www.myurlpointingtodownload.com"

Dim WinHttpReq As Object
Set WinHttpReq = CreateObject("Microsoft.XMLHTTP")
WinHttpReq.Open "GET", myURL, False, "eimail@g.com", "passs"
WinHttpReq.send

myURL = WinHttpReq.responseBody
If WinHttpReq.Status = 200 Then
    Set oStream = CreateObject("ADODB.Stream")
    oStream.Open
    oStream.Type = 1
    oStream.Write WinHttpReq.responseBody
    oStream.SaveToFile "C:\Users\mee\Desktop\fileoioi.csv", 2 
    oStream.Close
End If

End Sub

Thank you, bob.

解决方案

Thanks to alex and Kyle's hints I looked up to these 2 posts which solved my problem.

nb -> no cookie handling and Fiddlr body copy of the http POST request the 2 required steps for me

here it is my solution:

Sub eds()

Dim strCookie As String, strResponse As String, _
  strUrl As String
  Dim xobj As Object
  Dim WinHttpReq As Object
  Set xobj = New WinHttp.WinHttpRequest

  UN = "2myusername"
  PW = "mypass"

  strUrl = "http://my.website.com/"
  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 = http://my.website.com/date=09-10-1945%&search 'the url pointing to the CSV file
  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 "C:\Users\me\Desktop\ciao\file.csv", 1
    oStream.Close
End If
End Sub

本文地址:IT屋 » VBA从Internet WinHttpReq下载文件,登录不起作用

相关文章: