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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Web Queries
and sometimes, at the qryVendHist.refresh command, I get the dialog box that
starts out: "Run-time error '1004': The file could not be accessed. Try one of the following: Make sure the specified folder exists. Make sure the folder that contains the file is not read-only. Make sure the file name does not contain any of the following characters: < ? [ } : | or * Make fure the file/ath name doesn't contain more than 218 ********** I don't know what possible sense that makes in this context, but if I click Debug, and then tell it to continue, it usually finishes up fine. I tried putting in a DoEvents before and after the qryVendHist.refresh , but it's still doing it. |
Reply |
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) |