通过具有默认凭据的代理从Excel获取http获取请求 [英] Make a http get Request from Excel through a Proxy with default credentials

问题描述

我想从一个网站（某种类型的web请求）获取数据，从Excel中，只有问题我坐在代理后面，我不想硬编码用户名和密码。

有没有办法获取默认代理，并设置当前用户的凭据，无需硬编码？

其他字：我想要使用代理身份验证，用户当前登录在Windows机器上。

这是相关的代码：

 设置objHTTP = CreateObject（MSXML2.ServerXMLHTTP.6.0）
 URL =http://stackoverflow.com/
 objHTTP.OpenGET，URL，False 
 
'如果可能，我想获得默认的proxysettings 
 objHTTP .setProxy 2，proxy：8282
 
'我不会设置此行
 objHTTP.setProxyCredentialsusername，password
 objHTTP.setRequestHeaderUser-Agent ，Mozilla / 4.0（c ompatible; MSIE 6.0; Windows NT 5.0）
 objHTTP.send（）
  

我googled ，但我可以找到一个明确的否或替代解决方案。

它应该在Windows环境中运行，任何帮助和/或提示将不胜感激

解决方案

目前我找不到任何更好的东西，但这是一个快速而脏的解决方案，使用标准组件。

由于代理设置为Internet Explorer，因为每个用户

1）添加Webbrowser控件

2）设置控件的属性 < br>
名称：浏览器，可选更改不可见的大小

3）将此代码写入工作表，浏览器控件 >

  Private Sub browser_DocumentComplete（ByVal pDisp As Object，URL As Variant）
 Debug.Print browser.Document.body.innerHTML 
 End Sub 
 
 Private Sub Worksheet_Activate（）
 browser.Navigatestackoverflow.com
 End Sub 
'使用Excel 2010在Win7上测试
  

代码有时会有点flakey（javascript错误，...），但它使用代理和登录用户。

更新1：

，其中Internet Explorer没有代理集，可以通过使用注册表项设置代理来 （非常脏的解决方法） ：

  HKCU\Software\Microsoft\Windows\CurrentVersion\Internet Settings\ProxyEnable 
 HKCU\Software\Microsoft\Windows \CurrentVersion\Internet Settings\ProxyServer 
  

尽管这样工作，应用程序将不得不终止所有运行的IE，然后启动浏览器。

免责声明： 我没有尝试这个（这只是我的一个想法），因为对于我的>>需要上面的解决方案是足够的，在我看来，它非常>>激进/危险，可能/应该是一个更好的方式。

I want to get data from a Website(some type of webrequest), from an Excel, only problem i am sitting behind a proxy and i dont want to hardcode Username and Password.

Is there a way to get the default Proxy and set the credentials from the current User, without hard coding it?

With other words: I would like authenticate against the proxy, with the user that is currently logged on the Windows machine.

This is the relevant code:

   Set objHTTP = CreateObject("MSXML2.ServerXMLHTTP.6.0")
   URL = "http://stackoverflow.com/"
   objHTTP.Open "GET", URL, False

   'If possible i would like to get the default proxysettings
   objHTTP.setProxy 2, "proxy:8282"

   'I dont wont to set this line
   objHTTP.setProxyCredentials "username", "password"
   objHTTP.setRequestHeader "User-Agent", "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)"
   objHTTP.send ("")

I googled, but i could find a clear no, or an alternative solution.

It should run in en Windows environment. Any Help and/or Hints would be appreciated.

解决方案

I could not find anything better at the moment, but this is a quick and "dirty" solution, that works, with "Standard" components.

Since the proxy is set for the Internet Explorer, for every User

1) Add a Webbrowser control

2) Set the properties for the Control
Name: browser, optional change the size that it wont be visible or so

3) Write this Code into the worksheet, with the browser control

Private Sub browser_DocumentComplete(ByVal pDisp As Object, URL As Variant)
    Debug.Print browser.Document.body.innerHTML
End Sub

Private Sub Worksheet_Activate()
    browser.Navigate "stackoverflow.com"
End Sub
' Tested on Win7 with Excel 2010

the Code reacts sometimes a bit flakey(javascript errors, ...), but it uses the Proxy and the logged on User.

Update 1:

for other Scenarios, where the Internet Explorer has no proxy set, this could be down (very dirty workaround) by setting the proxy with the registry keys:

HKCU\Software\Microsoft\Windows\CurrentVersion\Internet Settings\ProxyEnable
HKCU\Software\Microsoft\Windows\CurrentVersion\Internet Settings\ProxyServer

Although for this to work, the application would have to terminate all running IE's and then Start the browser.

Disclaimer: I didn't try this (it's just an idea i had), since for my >>needs the above solution is enough, and it seems to me very >>radical/dangerous and there could/should be a better way.

这篇关于通过具有默认凭据的代理从Excel获取http获取请求的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！