Re-using Web Query Data

  • Thread starter John Hattersley
  • Start date
J

John Hattersley

Hi All,

I'm using a web-query (programmatically) to insert data into a few
worksheets from an intranet site (WAN). I always access the same page but
just download different tables into different worksheets. The web-page is
pretty large so opening it takes some time, it appears to me that Excel
isn't doing any caching and re-opens the page for each new worksheet. This
is a cut down version of the code:
....
ConnectString = URL; http://blahblahblah/someapp/somepage... you get the
idea.
....
For Each tableName In tableList

' Do the query and add the worksheet.
'
'
Set thisSheet = ActiveWorkbook.Worksheets.Add
With ActiveSheet.QueryTables.Add(Connection:=ConnectString,
Destination:=Range("A1"))
.Name = tableName
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = False
.RefreshOnFileOpen = True
.BackgroundQuery = True
.RefreshStyle = xlOverwriteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingRTF
.WebTables = tableName
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.Refresh BackgroundQuery:=False
End With

Next
....

As you can see noddy stuff, just opens and squirts a specific table into the
worksheet. Is there anyway I can re-use the web-query data, rather than
forcing a new download for each worksheet? The number of tables my get
quite large and it's not exactly quick now.

I appreciate any help you can offer.

Thanks,
John
 
Top