Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Web Queries
Hi.
I'm using a Microsoft Web Query to access some data that is being retrieved by Oracle's Discoverer Web version. Sometimes, when I change the query's parameter, it flashes the data up almost instantaneously. Other times, it drags on for well more than a minute. Working with it here this evening, a couple of times, it has flashed through the 10 data retrievals I'm using in about 10 seconds. Other times, it takes it more than 10 minutes. Yet other times, it quits processing and brings up that Continue End Debug Cancel dialog box. I've thought of the possibility of these queries getting behind other database jobs. Perhaps that's all it is. Can anyone offer any other ideas? When I open the file, it asks if I want to enable or disable automatic refresh... for a bit, I thought maybe the difference was in my answer to that, but now it doesn't seem so. It's the same code. But vastly different return time. Code below for reference. Thanks. Mark '****************** Sub main() 'dimension variables Dim rgList As Range Dim rgItem As Range Dim rgDest As Range Dim qryVendHist As QueryTable Dim i As Integer 'assign variables Set rgList = Sheets("List").Range("a1") Set rgItem = Sheets("Item").Range("b1") Set rgDest = Sheets("List").Range("c1") Set qryVendHist = Sheets("Query").QueryTables(1) i = 1 'clear query data, put in new part, refresh qryVendHist.BackgroundQuery = False While rgList.Offset(i, 0).Value < "" Sheets("Query").Cells.Clear rgItem.Value = rgList.Offset(i, 0).Value qryVendHist.Refresh rgDest.Offset(i, 0).Value = Sheets("Query").Range("b2").Value rgDest.Offset(i, 1).Value = Sheets("Query").Range("c2").Value i = i + 1 Wend End Sub |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Web queries | Excel Worksheet Functions | |||
Web Queries | Excel Discussion (Misc queries) | |||
Web Queries | Excel Worksheet Functions | |||
Queries | Excel Discussion (Misc queries) | |||
Web Queries | Excel Discussion (Misc queries) |