1

I'm trying to create some automated web searches in this website using Excel VBA but in order to use it, you need to agree with it's terms and conditions. The website then stores your agreement in a cookie "agree=True".

But, while using MSXML2.XMLHTTP, I'm not able to set the cookie request header using setRequestHeader, so it redirects me to the usage agree page on every request. I know there's a bug using MSXML2 as stated here, but I don't see how I can use the workaround provided in the link with Excel. (Invoking setRequestHeader twice will not work).

This is the code I'm using:

Set objHTTP = CreateObject("MSXML2.XMLHTTP.6.0")

Dim CAGE As String

CAGE = "3K723" 'Could be any CAGE Code, using this one as example

url = "https://cage.dla.mil/Search/Results?q=" & CAGE & "&page=1"

objHTTP.Open "GET", url, False
objHTTP.SetRequestHeader "Cookie", "agree=True"
objHTTP.send

Cells(1, 1).Value = objHTTP.ResponseText

I know this problem is solved simply by using MSXML2.ServerXMLHTTP or WinHTTP.WinHTTPRequest.5.1 instead of MSXML2.XMLHTTP, but as the website uses HTTPS protocol and not HTTP, ServerXMLHTTTP and WinHTTP can't resolve it's address. So it returns me the following error:

"The server name of address could not be resolved."

Does anyone knows how can I properly set the Cookie header using MSXML2.XMLHTTP, or how to use WinHTTP with an HTTPS website?

Gustavo Pereira
  • 13
  • 6
  • Why don't you use the Internet Explorer? You click the Agree button once and thats it for the future. – Zwenn Jan 08 '20 at 13:18
  • @Zwenn I'm not sure if I understood you suggestion, but if you're saying to use something like `Set IE = CreateObject("InternetExplorer.Application")` inside ExcelVBA, I don't think it's a good idea because it would take too long for loading the whole webpage on every search instead of a request returning me only the data I asked for. – Gustavo Pereira Jan 08 '20 at 13:32
  • 1
    Have You tried something like this `Set objHTTP = CreateObject("WinHttp.WinHttpRequest.5.1")` `objHTTP.Option(6) = False`. It turns off every certificate security pop up off. – Teamothy Jan 08 '20 at 13:34
  • Thanks @Teamothy, but I have already tried that and also sending the certificate with `objHTTP.setClientCertificate('CURRENT_USER\MY\USERNAME')`, but nothing changed, I still got the same error. I don't think the problem is in the TSL/SSL protocol, otherwise I guess I would get an security error. – Gustavo Pereira Jan 08 '20 at 13:48
  • @Zaad How many requests do you want to make? How much time do you expect to save? Before you find a solution for your specific problem, I would take the pragmatic approach. IE is not that slow, especially since the queries from VBA only work sequentially anyway. If you need a fast working solution you have to use e.g. Python and send several queries in parallel. – Zwenn Jan 08 '20 at 14:07
  • @Zwenn I have a "database" containing thousands of CAGE Codes, and I need those codes to be updated (check if they're still active), currently the data is updated manually every three months approximately, but that's too unpractical, and kind of unreliable. So I'm trying to automate this process. As this database is in an excel worksheet and I have already done some tools using HTTP with VBA, I'm first trying to make it inside excel itself, before moving into another language. – Gustavo Pereira Jan 08 '20 at 14:26
  • @Zaad Ok I understand. No solution for your page but do you have tested other search pages like https://www.govcagecodes.com/?code=3K723 or https://www.cage-codes.com/?q=3K723 – Zwenn Jan 08 '20 at 14:46
  • @Zwenn Actually I haven't thought about that, guess it could be the best way to do it, I'll give it a try. Thank you very much! – Gustavo Pereira Jan 08 '20 at 15:07

0 Answers0