Configuring proxy with WinHTTP and Excel

Question

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.

score 2 · Accepted Answer · answered May 14 '12 at 20:21

2

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

answered May 14 '12 at 20:21

Scott Holtzman

27,099
5
37
72

Hi! This seems to be the way, but whenever I try to execute the query, it says that Win32_Proxy is an invalid class. I've tried with other classes that I found on the MSDN and they worked, so I think it is a problem specific to this class. – Yuri Oliveira Jun 25 '12 at 15:32
Interesting. This worked for me. Let me know if you find the problem. If it's any help, I am on XP. – Scott Holtzman Jun 25 '12 at 15:40
Win32_Proxy no longer available post win-xp – anand Dec 24 '14 at 10:21

score 2 · Answer 2 · edited May 23 '17 at 10:29

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)

Configuring proxy with WinHTTP and Excel

2 Answers2

Linked