Excel 2007 : Getting data from web services

[Skawn]

Hi folks,

I've got a worksheet that calls a web service to get back some XML. It works great but each week I have to change the query string for the service being called to look at the new weeks data.

For example:

I'm currently using: /mydomain.com/mywebservice.asmx/weeklydata?week=1
next week I'll need data for: /mydomain.com/mywebservice.asmx/weeklydata?week=2

Ideally, I'd like the week parameter to be added to the query string, based on the value of a cell. e.g. if A1 = 3, it'll pass week 3 to the web service in the query string.

Anyone know how this can be achieved?

[pike]

Hi Skawn
Try some thing like ....

": /mydomain.com/mywebservice.asmx/weeklydata?week=" & Sheets(1).range("A1").value

[Skawn]

Sadly it just tries to call the webservice with " & Sheets(1).range("A1").value as the query string.

I suspect this isn't possible... the place you enter the URL is a standard windows dialog box so I'm not sure you can get it to look back at the sheet before it pings the service.

Still, if anyone knows a way...

[pike]

shawn
what the full code???
maybe...

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _
                    Cancel As Boolean)
    Dim strSearch As String

    strSearch = Sheets(1).Range("a1").Formula
    If Target = Range("a1") Then
        Cancel = True
        ActiveWorkbook.FollowHyperlink _
        Address:="/mydomain.com/mywebservice.asmx/weeklydata?week=", _
        ExtraInfo:= & strSearch, _
        Method:=msoMethodGet
    End If
End Sub

[romperstomper]

If you are talking about a web query, then see this article by Jan Karel Pieterse.