VBA Winhttp URL

Question

I am using Winhttp in the following VBA code and can't get the URL to work, yet if I paste the URL into a browser it works fine. Any ideas would be appreciated:

Sub WinHttp()

Dim arr() As String
Dim pos As Integer
Dim used As Range

Dim url, resp As String
Dim req As New WinHttpRequest
Dim n As Integer

r = ActiveSheet.UsedRange.Rows.Count

For n = 4 To r

    Worksheets("Sheet0").Activate
    pn = Cells(n, 2).Value
    UnitPrice = (Cells(n, 4).Value) * 1

    url = "https://www.google.com/search?q=DNR-12-1G+$4,250"

    req.Open "GET", url, False
    req.send

    resp = req.ResponseText

    Debug.Print resp

    arr = split(resp)

    arr = Filter(arr, "$")

Next n

End Sub

Possible duplicate of [HTTPS POST request using VBA for Excel](http://stackoverflow.com/q/1264303/11683) — GSerg, Dec 25 '16 at 20:53
When I put it in a web debugger goggle returned error 302 and a nice web page but as it's an error XMLHTTP doesn't show it. But the 302 says it has moved and there is a redirect. I also escaped your link after `q=` with VBScript Escape function which gives this `DNR-12-1G+%244%2C250` — , Dec 26 '16 at 01:03
If you change it to your local goggle. It works if I put in `goggle.com.au`. — , Dec 26 '16 at 01:15

fuglede · Answer 1 · 2016-12-26T00:01:37.260

Just tested the following code in Excel 2013, where it runs fine:

Sub Test()
    Dim req As New WinHttpRequest
    Dim url, resp As String

    url = "https://www.google.com/search?q=DNR-12-1G+$4,250"

    req.Open "GET", url, False
    req.send

    resp = req.ResponseText
    Debug.Print resp
End Sub

The response from the server is

<!DOCTYPE html><html lang=en><meta charset=utf-8><meta name=viewport content="initial-scale=1, minimum-scale=1, width=device-width"><title>Error 403 (Forbidden)!!1</title><style>*{margin:0;padding:0}html,code{font:15px/22px arial,sans-serif}html{background:#fff;color:#222;padding:15px}body{margin:7% auto 0;max-width:390px;min-height:180px;padding:30px 0 15px}* > body{background:url(//www.google.com/images/errors/robot.png) 100% 5px no-repeat;padding-right:205px}p{margin:11px 0 22px;overflow:hidden}ins{color:#777;text-decoration:none}a img{border:0}@media screen and (max-width:772px){body{background:none;margin-top:0;max-width:none;padding-right:0}}#logo{background:url(//www.google.com/images/branding/googlelogo/1x/googlelogo_color_150x54dp.png) no-repeat;margin-left:-5px}@media only screen and (min-resolution:192dpi){#logo{background:url(//www.google.com/images/branding/googlelogo/2x/googlelogo_color_150x54dp.png) no-repeat 0% 0%/100% 100%;-moz-border-image:url(//www.google.com/images/branding/googlelogo/2x/
googlelogo_color_150x54dp.png) 0}}@media only screen and (-webkit-min-device-pixel-ratio:2){#logo{background:url(//www.google.com/images/branding/googlelogo/2x/googlelogo_color_150x54dp.png) no-repeat;-webkit-background-size:100% 100%}}#logo{display:inline-block;height:54px;width:150px}</style><a href=//www.google.com/><span id=logo aria-label=Google></span></a><p><b>403.</b> <ins>That’s an error.</ins><p>Your client does not have permission to get URL <code>/search?q=DNR-12-1G+%244,250&amp;gws_rd=cr&amp;ei=IDVgWJ2dDomnsgGfy4u4Dw</code> from this server.  (Client IP address: 194.28.248.113)<br><br>
Please see Google's Terms of Service posted at http://www.google.com/terms_of_service.html
<BR><BR><P>If you believe that you have received this response in error, please <A HREF="https://www.google.com/support/contact/user?hl=en">report</A> your problem. However, please make sure to take a look at our Terms of Service (http://www.google.com/terms_of_service.html). In your email, please send us the <b>entire</b> code displayed below.  Please also send us any information you may know about how you are performing your Google searches-- for example, "I'm using the Opera browser on Linux to do searches from home.  My Internet access is through a dial-up account I have with the FooCorp ISP." or "I'm using the Konqueror browser on Linux to search from my job at myFoo.com.  My machine's IP address is 10.20.30.40, but all of myFoo's web traffic goes through some kind of proxy server whose IP address is 10.11.12.13."  (If you don't know any information like this, that's OK.  But this kind of information can help us track down problems, so please tell us what you can.)</P><P>We will use all this information
 to diagnose the problem, and we'll hopefully have you back up and searching with Google again quickly!</P>
<P>Please note that although we read all the email we receive, we are not always able to send a personal response to each and every email.  So don't despair if you don't hear back from us!</P>
<P>Also note that if you do not send us the <b>entire</b> code below, <i>we will not be able to help you</i>.</P><P>Best wishes,<BR>The Google Team</BR></P><BLOCKQUOTE>/+/+/+/+/+/+/+/+/+/+/+/+/+/+/+/+/+/+/+/+/+/+/+/+/<BR>
Kjm9A1b-agG1uA7g8B-cYcEbbyIbP2Zi4vVoYweB2AoPwPBt3<BR>
bmAY9hROWVTpQsuAA6kmBSVN141uaLrXs0m8ub4wC4dsCpPaQ<BR>
tuOQKksiTpmW8fSvzTxDiDtJ30yguDPQyBEfvx1txlHQiTfk-<BR>
lIVTsj7H3uWatFa4O9Zuxs7LvBBfCxrR69HFHpJpXWkDECkrY<BR>
AeRa1LGcxiPWYskIMweGWKTdkbdmf1q1hcmvDUOL457j5QPNx<BR>
NgwnPMBejraU_xbwSzoWNx2z7SfDmbPnUahlv69y-omfiMhp8<BR>
s1LQK90rg5hYAgbLmoVjMHMZ0WeRoay-XoB1oKQzff-nnkAD0<BR>
fGK074ZB-7MQM3oh-eX0KIwyThj-JwXGJEPRk2pwTJenH7RB1<BR>
9XzB2G-GiIIMoyVzdu7WUyd-Y9ZUJO4Sk8v6AXB8qtT7BUp2t<BR>
gnj2P_8Ve6uLta3Q9QPFH-XFGXJiHXL-xr-wQ4yz-0vh1wpEi<BR>
tYmc7dchSKFtzZfXPp0fGFYaD8My9GscqRNif8Yk35AEAzTJW<BR>
qoK-GRSDdSn4Xx26BXDkd_8-XvcxlCrW9n9uS06ksGBcNpk-z<BR>
DDr3fM-hXjTTFutoSYSNQEi5Tz8CgRFm11MEZT7vBPTvE1aiW<BR>
owzBaftrSrS9Kipke0HfZq6LWh0gBRvCF-fAXqHcjjt8fXCft<BR>
Jrxwv08vYmKxD_HLJkdpR1gxrW-JphLVAZ9ELeALfsi7fc74z<BR>
fvs63_f1mhzMBqjVK86Y1RwggmVeeRv6R0ClCaZT6fJ0jhpU5<BR>
x2woC_0feuZ6t2aHdJZoKNi1uBTNejYSnSH3NRnJQ0NRxjrZw<BR>
KYUjQ26E9qDwb7K3nAnZ0sDrqME93VCFb0ACQSxHiUCbO142a<BR>
6X1giaeMpPxICZB20HHP48ME8-PrtTyzDdodVoCyqxKqc82kZ<BR>
8agHOtp00dVEGTMtejMDZ-4BYMKJ0ddAWyf5Q4gCIWr2T4c2s<BR>
tzYefoqScHzGlEqCBaaom3DRY0i07PkumE04oof-sOzCcW2nn<BR>
DVMH_ubYpWQOtnVgrIP8whGsl7JbtwQSgXhEkVB1fF2Z4CZlK<BR>
pX__Kmh8eK492YFHYkVD4TtTqUtZnaCfOx7Fv7eulEYlhRv7w<BR>
sp1_QRYhohj_8pm2v2KJs5AqUBPnpRyhjxjC3c4n68AuEYHtM<BR>
TVmbK-fyMtcWLMTVXzKH17msxyhMtkIRq8-A9LthpCwUC02VW<BR>
0xVBm5PAgxQg9TaMAGK3wMiry7FDXn3H2Bksj8EGc_g_XP3GO<BR>
PzIouNrBpnnuz7FXjIBgFWCyxolj32V3zxpuaOki4bXjW7gT_<BR>
VaAsqsSqnPEZ3BI2zcMqoxJTur1y3_UjykMgBhvWDxMgG1DpE<BR>
rMe5Uuj3ZmQMA3w28J4ssl42Jst2Zve0IRLU1WmJcpRsIpdTB<BR>
epkPeMr9-6RB5xXnilUH01y2WUSg_QsPIi8AxeEnW--AJhvkH<BR>
pljdzVkGbd5UbtHO5AiJkjDlVoeEFMLqaF5hgPUviNRHW6Kgd<BR>
3clcOsh4NB9kRCQuYzligIU8Dju-SxC9YuUy7_2MvC2OLKIXl<BR>
eo2hq1T_PqJFaChvRszEB_vcwWrDb-xthn9_CmN5331vz7FD1<BR>
xvUE93jm9X4oLgrFQ7Nri52R7ud2K7lPqrq1FsTdAh7_Dc3kN<BR>
IwIT2zKcVjFR-DYLWWtoRS4Ue1lIgMKPqIF8B0rrT1VwrRHVG<BR>
SMI4ndyRqJC4ufhBrNBzFl8GAck6bsnfEzvVOCqO0qdXYpFib<BR>
4FwWT591tnyd<BR>
+/+/+/+/+/+/+/+/+/+/+/+/+/+/+/+/+/+/+/+/+/+/+/+/+<BR></BLOCKQUOTE>


  <ins>That’s all we know.</ins>

That is a customized [HTTP 403](https://en.wikipedia.org/wiki/HTTP_403), which I believe the OP counts as "can't get the URL to work". — GSerg, Dec 25 '16 at 21:14
Could be. In that case, that has very little to do with VBA/WinHTTP though. — fuglede, Dec 25 '16 at 21:16
Could be, but [not necessarily](http://stackoverflow.com/q/1264303/11683). — GSerg, Dec 25 '16 at 21:17
Well, you are generally not supposed to use Google for scripting, so that's what's causing the 403 in this case. You can get around it with `req.SetRequestHeader "User-Agent", "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)"`, but you will want to check up on the terms of service first. — fuglede, Dec 25 '16 at 21:24
Fuglede: Look closely at the error in the return: "client does not have permission to get URL /search?q=DNR-12-1G+%244,250&gws_rd=cr&ei=IDVgWJ2dDomnsgGfy4u4Dw from this server." — Bob, Dec 25 '16 at 23:41
@Bob: That's Google sending you that error: The request is going through just fine. If you want to cheat their User-Agent blocker, you can use the header in the comment above, but check up on their terms of service first. — fuglede, Dec 25 '16 at 23:46

score 0 · Answer 2 · answered Dec 25 '16 at 23:49

0

Thanks! It appears the "req.SetRequestHeader "User-Agent", "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)"

Does the trick.

answered Dec 25 '16 at 23:49

Bob

101
3
13

1

Heh, I think this should have been a comment and not an answer, but you're welcome. – fuglede Dec 26 '16 at 00:06

VBA Winhttp URL

2 Answers2