Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Performance problem with VB Macro in Excell XP
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excell Drop down problem and macro | Excel Discussion (Misc queries) | |||
Macro performance | Excel Discussion (Misc queries) | |||
Performance problem whem McAfee is running | Excel Discussion (Misc queries) | |||
Excel Add-In performance problem | Excel Programming | |||
problem with performance of excel macro, pls help | Excel Programming |