Logo 
Search:

MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Excel crashes after multiple queries

  Asked By: Margaret    Date: Jan 13    Category: MS Office    Views: 1157
  

I'm downloading historical data on a list of 20 or so stocks with
Excel 2000, using the following steps for each:

1. Select scratch worksheet
2. Cells.Delete
3. Cells(1,1).Select
4. Create the URL for this stock
5. Execute the query
6. Copy data from scratch sheet



The program runs great for the first 10 or so stocks, then Excel
crashes. There's no obvious reason, and the particular stock is not
the problem. (I know, because if that stock is moved up higher in the
list it runs fine.)



At first the crash occurred on the 15th query, so I thought the problem
might be a limitation on the number of open queries allowed. In
subsequent runs, it occurred at different points in the list, but just
to make sure I added this code before the query:

If ActiveSheet.QueryTables.Count > 0 Then
ActiveSheet.QueryTables(1).Delete



That did keep the query table small, but the problem persisted.I'm
stuck with breaking my list into small chunks and processing each chunk
separately. Clumsy, but it works.




Thinking that I might be running out of memory, I printed out


Application.MemoryFree,


Application.MemoryTotal


Application.MemoryUsed


before each query, and found that the numbers changed only slightly, so
that can't be it.




Any ideas would be welcome and appreciated.

Share: 

 

1 Answer Found

 
Answer #1    Answered By: Adelaide Fischer     Answered On: Jan 13

Not saying it will work... But the timing on getting stuff from the net can
sometimes be a bit strange. Try sprinkling your code with DoEvents commands
to flush out waiting processes.

 
Didn't find what you were looking for? Find more on Excel crashes after multiple queries Or get search suggestion and latest updates.




Tagged: