• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Unable to set proxy for "WinHttp.WinHttpRequest.5.1" object

Status
Not open for further replies.
A

Abhishaik Srivastava

New Member
I have a requirement to connect to public website that is REST enabled and get the data using VBA. I am able to achieve the same but it is only running in my home network. Whenever I am connected to office network, it is not able to connect to the server. I tested the same with ping command also i.e. whenever I am in my home network, I get ping response from the server, but whenever I tried to do the same from office network, it says server not reachable. I am assuming that it is proxy issue i.e. from office network, it requires proxy to reach out public website.

I have following code snippet that is working well in my home network
Code: 
Function getData(url As String) As Object
    Dim JsonText As String
    Dim MyRequest As Object
    Set MyRequest = CreateObject("WinHttp.WinHttpRequest.5.1")

    MyRequest.Open "GET", url, False
    MyRequest.SetRequestHeader "ZSESSIONID", MyApiKey
  
    ' Send Request.
    MyRequest.send

    'And we get this response
    JsonText = MyRequest.responseText
    Set getData = JsonConverter.ParseJson(JsonText)
  
End Function
I am not an expert in VBA, so looking out for help. I tried to find multiple options from internet for proxy setting for "WinHttp.WinHttpRequest.5.1", but didn't find the concrete one.
Found example for "ServerXMLHTTP60", but it requires to import the module.

Is there anyway to set the proxy correctly before sending the request. It is not a proxy IP, but a proxy script that I need to set.
 
It's quite simple really. As long as you don't need to change/read global setting.

Code: 
MyRequest.SetProxy 2,"http://proxyurl:8080"
 
Thanks for the prompt reply. As mentioned in my comments earlier, I don't have proxy URL with port, but a script to configure. Is there any way to pass script for proxy settings ?
 
Depends on type of script. I assume you'll need to run it using Shell function.
But it isn't easy or straight forward. A lot will depend on your system and how network/IT admin configured your company network.

One way is, like you've mentioned, to use VBA-Web add-in. Other than that, you'll have to consult with your network/IT admin and find out what needs to be done for your specific instance.

Edit: If you are familiar with R or Python, you could do scraping using those and then import result to Excel. It has more robust web scraping frame-work than what's readily available for VBA. However, if your not familiar with it. This could be more difficult (or your IT policy may not allow install).
 
I'm having a similar issue right now. I wonder whether you found a solution that helped you eventually.
 
From the code snippet you've shared and the information you've provided, it sounds like the issue may be related to a proxy setting
 
Status
Not open for further replies.
Back
Top