Where will your actual query reside? MSQuery maybe. Do you want to do
it all in VBA or just use VBA to trigger the data to refresh?

i.e. do you want to create a report written in VBA and return the data
to a number of single cells, or just use vba to refresh the data that's
already there?

The latter is by far the easiest, something like

Sub RefreshMyData()
Dim myRange as Range
Set myRange = Range("B54") 'wherever you have data
myRange.QueryTable.Refresh
End Sub

or perhaps:

Sub RefreshMyData()
Dim i as integer
For i = 1 to 13 'however many separate data queries you have
Sheets("My Sheet").Querytables(i).Refresh
Next i
End Sub

Whereas creating the queries in VBA is a whole load more work!