Google Docs script for pulling character information from the wow API

[Cptan]

How much work would it take me to port these scripts to VBA for excel? Assuming an intermediate level of VBA expertise but no experience at all with the actual pulling of data from a web source.

Not difficult. I used WinHttp, activated from VBA, to get the armory data from Blizzard server, and the rest are just a matter of extracting the keys and values, either from external JSON parser, or in my case, used string manipulation.

wow armory.jpg

Timeout from us.battle.net is not uncommon, especially when getting the item data.

Dim WinHttpReq As WinHttp.WinHttpRequest
Dim txtURL As Variant
Dim LastCol As Long


' Create an instance of the WinHTTPRequest ActiveX object.
Set WinHttpReq = New WinHttpRequest


' get last column
With ActiveSheet
    LastCol = .Cells(6, .Columns.Count).End(xlToLeft).Column
End With


curRow = 7
Do Until Len(Cells(curRow, 2)) = 0      ' blank realm cell indicates end of last row


    strRealm = ActiveSheet.Cells(curRow, 2)
    strToonName = ActiveSheet.Cells(curRow, 3)
    txtURL = "http://us.battle.net/api/wow/character/" & strRealm & "/" & strToonName & "?fields=guild,items"


    ' Assemble an HTTP Request.
    WinHttpReq.Open "GET", txtURL, False
    ' Send the HTTP Request.
    WinHttpReq.Send


    ' assign server response to sText
    sText = WinHttpReq.ResponseText

    ........

Loop

End Sub