Configuring proxy with WinHTTP and Excel
Solution 1
I got the below vbScript from the following link. You may be able to use to get the proxy server and pass it as a variable to your code for "proxyserver:8080":
http://www.activexperts.com/activmonitor/windowsmanagement/scripts/networking/client/retrieving/
If you know vbScript - which is very similar to VBA, I think this should help a lot. If you need help writing this in VBA, let me know.
On Error Resume Next
strComputer = "."
Set objWMIService = GetObject("winmgmts:" _
& "{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2")
Set colItems = objWMIService.ExecQuery("Select * from Win32_Proxy")
For Each objItem in colItems
Wscript.Echo "Proxy Port Number: " & objItem.ProxyPortNumber
Wscript.Echo "Proxy Server: " & objItem.ProxyServer
Wscript.Echo "Server Name: " & objItem.ServerName
Wscript.Echo
Next
Solution 2
In case anyone else stumbles on this page looking for an answer to this same question, I'd like to point to this answer, which mentions using the VBA-Web project to solve this exact problem.
Your code would then look something like this:
Dim client As New WebClient
With client
.BaseUrl = "https://www.google.com"
.EnableAutoProxy = True
End With
Dim request As New WebRequest
With request
.Method = WebMethod.HttpGet
.AddHeader "User-Agent", "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)"
.Format = WebFormat.PlainText
End With
Dim response As WebResponse
Set response = client.Execute(request)
Yuri Oliveira
Updated on March 13, 2020Comments
-
Yuri Oliveira about 4 years
I'm using WinHTTP to do a GET request in an Excel VBA Macro. However, if I try to do the request from a machine in a network with a proxy, it does not work. If I configure it manually, it works, but I don't think the people who'll use the tool I'm developing will know their proxy servers.
Is there a way to autoconfigure the proxy server, or to get the proxy configuration from Windows? Here follows a sample code:
Dim result As String Dim URL As String Dim winHttpReq As Object Set winHttpReq = CreateObject("WinHttp.WinHttpRequest.5.1") URL = "http://google.com/" winHttpReq.Open "GET", URL, False winHttpReq.setRequestHeader "User-Agent", "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)" winHttpReq.setProxy 2, "proxyserver:8080", "" winHttpReq.send result = winHttpReq.responseText
In this case, I don't want to force the user to find the "proxyserver:8080" address - what I want is a way to fill that automatically.
Thanks a lot.