As another opportunity for Excel VBA automation, I wanted to have the ability to automatically call and retrieve data from a web query.  I have various web data sources that return CSV data that the Excel web query function can import the data and create columns perfectly.  The automation of calling the web query to refresh the data using Excel VBA is what I will give you an example of.

For a sample data source, I will use one from geocoder.us that gives me back latitude and longitude of an address passed via the web service parameters.

Dim url As String
url = "URL;http://rpc.geocoder.us/service/csv?address=1600+Pennsylvania+Ave,+Washington+DC"
With Worksheets("Sheet1").QueryTables.Add(Connection:=url, Destination:=Worksheets("Sheet1").Range("A1"))
  .Name = "Geocoder Query"
  .RowNumbers = False
  .FillAdjacentFormulas = False
  .PreserveFormatting = True
  .RefreshOnFileOpen = False
  .BackgroundQuery = False
  .RefreshStyle = xlOverwriteCells
  .SavePassword = False
  .SaveData = True
  .AdjustColumnWidth = True
  .RefreshPeriod = 0
  .WebSelectionType = xlEntirePage
  .WebFormatting = xlWebFormattingNone
  .WebPreFormattedTextToColumns = True
  .WebConsecutiveDelimitersAsOne = True
  .WebSingleBlockTextImport = False
  .WebDisableDateRecognition = False
  .WebDisableRedirections = False
  .Refresh BackgroundQuery:=False
End With

There are a few important parameters in there that make the whole thing work smoothly.

Refresh BackgroundQuery:=False

This makes it so that the query will be refreshed when this block of code is run. Setting BackgroundQuery False makes it so that the code will block on the refresh call, so that it will wait until the query is done executing before continuing onto the rest of the code.

RefreshStyle = xlOverwriteCells

Setting the RefreshStyle to xlOverwriteCells makes it so that when you repeatedly call the function, the data from the previous call will be replaced, and no magic columns being inserted or anything will happen. For automation purposes, this option works better than having columns be shifted by using an option like xlInsertDeleteCells.