View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.programming
Stephen Knapp Stephen Knapp is offline
external usenet poster
 
Posts: 34
Default Performance problem with VB Macro in Excell XP

After using ActiveWorkbook.Save, you should be closing the workbook
(Workbook("thisworkbook").Close) because by failing to unload the workbook,
memory is getting clogged. Subsequent workbook processes (taking in the next
batch of data) has the operating system trying to page swap like a crazed
madman. And, not knowing how you're opening the new workbooks, you might be
creating multiple instances of Excel which will seriously impact performance
as well.

Steve in Ohio

"RayOTown" wrote:

I use Excel's Web Query (found on the Data, Import External Data menu) to
pull stock and option quotes into an excell spreadsheet. I've put the query
in a vb macro which allows me to pull data for a list of stocks.

After editting the data into the columns I need, I save the file using the
ActiveWorkbook.Save command before retrieving the next set of data.

Performance appears to be fine during the web query, data parsing and
editting, but when I try to save the file it seems take longer and longer
after each save.

I have several excell files which perform this type of function. Most are
working fine with no problems with over 600 records per batch run. But this
file just grinds to a halt after 50 or so records have been written.