CODEWITHSUNDEEP
jsonexcelvbacurl
AAA
AAA

Reputation: 2450

Curl URL and post results in Excel cell?

I have 200 rows in an Excel sheet that I would like to include in a curl (or any kind of HTTP get) and see the results in a second column.

**Column A**
123
456
789
012
...

I have tried using the Excel option to get data from an external web page but it doesn't appear to work for multiple row on the same sheet. Is there a way for me to append the value in Column A to a static URL (ie:http://testurl.net/page.php?ID=[Column A]) so that the result of the page is shown in Column B? I know the response from the URL will be a rest response that will display only a couple words.

Thank you

Upvotes: 5

Views: 30322

Answers (2)

rupweb
rupweb

Reputation: 3328

You can also use the WEBSERVICE(url)

enter image description here

Upvotes: 1

cboden
cboden

Reputation: 823

you can do this by using a http request object:

Dim oRequest As Object
Set oRequest = CreateObject("WinHttp.WinHttpRequest.5.1")
oRequest.Open "GET", "http://www.cboden.de"
oRequest.Send
MsgBox oRequest.ResponseText

If you are behind a proxy you can use something like this:

Const HTTPREQUEST_PROXYSETTING_PROXY = 2
Dim oRequest As Object
Set oRequest = CreateObject("WinHttp.WinHttpRequest.5.1")
oRequest.setProxy HTTPREQUEST_PROXYSETTING_PROXY, "http://proxy.intern:8080"
oRequest.Open "GET", "http://www.cboden.de"
oRequest.Send
MsgBox oRequest.ResponseText

and if you want to use POST (instead of the GET method) to pass some values to the webserver, you can try this:

Dim oRequest As Object
Set oRequest = CreateObject("WinHttp.WinHttpRequest.5.1")
oRequest.Open "POST", "http://www.cboden.de/misc/posttest.php"
oRequest.SetRequestHeader "Content-Typ", "application/x-www-form-urlencoded"
oRequest.Send "var1=123&anothervar=test"
MsgBox oRequest.ResponseText

if you put it into a function then you can use it in you worksheet:

Function getCustomHyperlink(ByVal pURL As String) As String
    Dim oRequest As Object
    Set oRequest = CreateObject("WinHttp.WinHttpRequest.5.1")
    oRequest.Open "GET", pURL
    oRequest.Send
    getCustomHyperlink = oRequest.ResponseText
End Function

within the worksheet you can then say for example:

=getCustomHyperlink("https://www.google.com/search?q=" & A1 )

if your search value is in A1

Upvotes: 6

Related Questions