Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Re-using Web Query Data
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:=ConnectStr ing, 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Use MS Query to query data within the current work book | Excel Discussion (Misc queries) | |||
Importing Data via Web Query - Can values be passed to query? | Excel Discussion (Misc queries) | |||
Save data retreived from query without saving query | Excel Discussion (Misc queries) | |||
How to Process Query Data after Query is Refreshed | Excel Programming | |||
Query returning more data than will fit on worksheet with VBA DB query... | Excel Programming |