0 Replies Latest reply: Dec 8, 2011 8:21 AM by Nikul Sanghvi RSS

VBA for mutliple REST web queries

Newbie

A quick how-to on creating bulk web queries for Webtrends REST URLs using VBA in Excel

 

Creating bulk web queries can become boring...

 

When working on chunky dashboards or projects, it can be pretty tedious to keep creating or amending multiple web queries for REST…

 

For example

  •          Trending particular pages / content (without using predefined content groups)
  •          Searching across multiple profiles in one go
  •          Searching for multiple keywords over time

 

This is how I speed things up...

 

Below is a very simple VBA code that reads down a specified column in Excel and creates REST web queries for the available cell values.

I’m sure it could have many applications, but I found it particularly useful when building query intensive dashboards.

 

I used this mostly when I was searching for a list of URIs that I needed to extract data for – possibly trended over a particular time period.

 

Here’s how it’s done… [with destination for data as Sheet2 and queries read from J10 on Sheet1]

 

 

Sub Web_Query1()

    Dim wsWQ As Worksheet

    Dim rCell As Range

 

'Set wsWQ as your destination sheet

    Set wsWQ = Sheet2

 

'Set Sheet1.Range as start cell and column

   For Each rCell In Sheet1.Range("J10", Sheet1.Cells(Sheet1.Rows.Count, "J").End(xlUp))

         With wsWQ.QueryTables.Add(Connection:= _

            "URL;https://ws.webtrends.com/v2/ReportService/profiles/" & rCell & "&format=html&suppress_error_codes=true", _

                Destination:=Sheet2.Cells(Sheet2.Rows.Count, "A").End(xlUp)(2, 1))

            .BackgroundQuery = True

            .TablesOnlyFromHTML = True

            .Refresh BackgroundQuery:=False

            .SaveData = True

        End With

    Next rCell

End Sub


 

 

Looking at the rCell in more detail...

 

The value for rCell will take a similar format to a regular REST url without the remainder of the URL that is hard coded above.

For example – you can concatenate the following string below - with [brackets] as variables… and use it as the rCell value

 

[ProfileID]/reports/[ReportID]/?totals=[all]&period=[YYYY]m[mm]&search=[search-string]

 

In the instance above, the code reads this concatenated value from cell J10 downwards on Sheet1

 

It's sounds a lot more complex than it really is, or maybe I suck at explaining things

If I've missed a trick and there's already an easier way to do this - I'd be eternally grateful to learn.

 

-----

 

 

Please note - I’ve waited too long to share this (sorry!) - now I’m not sure if it's still as handy after the release of DX3.0

DX3 means it’s a lot easier to trend data over time.

 

I’m hoping that this method is still useful – and if you find anything else cool that you can do with it, I’d be really glad to hear back from you.

As well as the code above, I’ve created a quick example of how the code can be used within a macro. Please feel free to use / hack as you see fit.

Apologies if it doesn't work perfectly, it's just to give an idea.

 

During some downtime over Xmas - I’ll try to update and test the code and example sheet with the new A10 REST features from DX 3.0

 

(Special thanks to Paul Lawbaugh who encouraged me at WT Engage2011 to share this).