Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
execution speed issue when closing files
I have a rather large application running on Windows 7/Excel 2007. The app has multiple routines, including some which update large networked directories of individual files with new data, some which read data from these files into a summarized report, etc.
The execution speed has always been a problem. The more I investigate, I'm finding the issue occurs when each individual file is being closed or saved.. The system hangs up for 30-60 seconds for each file. If I run a quick program which just opens and closes the files, the execution is significantly faster (on the order of 1-2 seconds/file) I'm running down the network aspect, but I'm having little or no issues when I manually open/close/save these or any files. So, now I'm looking at the code itself. I'm wondering if I've got an object creation issue, specifically: Any of these routines start by creating a directory tree of the files I'm interested in on a temporary worksheet (I set this sheet as a worksheet object). I also create a target worksheet if I'm running the summary report function Next I loop through the directory sheet, opening up each file individually, set this sheet as another worksheet object, reducing/updating/extracting data as the case may be. Each of these directory files is then closed/saved and the loop continues to the end of the file list. When the loop is complete and all files are updated, summarized, etc, all existing objects are set to nothing before the execution finishes. Does VBA have problems with the way I'm creating or destroying objects? Should I set all local objects to nothing before I close each file, or does setting the objects in the next file I open keep the stack in check? Art |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
execution speed issue when closing files
Anytime you modify/save files opened from an external location (network
share, usb stick, etc) the processing time will be significantly slower due to having to write to the external drive location. The write process invokes permissions checks and related background processing on the network share that doesn't happen if you just open/close a file without saving changes, or write back to an external drive plugged into the local machine. -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
execution speed issue when closing files
agreed - but I'm getting the same slowness even when I'm only reading from the file (no mods or saves).
The reason I posted is that other routines of this type I've written over the years don't have this problem - even when saving back to the network Art |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
execution speed issue when closing files
c1802362 laid this down on his screen :
agreed - but I'm getting the same slowness even when I'm only reading from the file (no mods or saves). The reason I posted is that other routines of this type I've written over the years don't have this problem - even when saving back to the network Art Show your code! I pull huge amounts of data into arrays without any perceived slowness. As I said.., if your code reads/writes to ranges or controls then it's going to *always* be *much* slower than accessing an array in memory. -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
execution speed issue when closing files
Sorry.., I posted to the wrong thread!
I suspect, then, that the issue might be the connection speed. OR You might also have a resources issue (available memory) if you leave your machine on over long periods. (I get slowdowns over my network because I leave my machine running for days, and so I need to reboot every now and then) OR Sometimes Windows has problems accessing the network. I see evidence of this when running Windows Explorer. I suspect this is a 'Timed out' thing but I don't know enough about the underlying processes to say for sure otherwise! The most common 'perceied slowness' is when IE does printing to any printer on my printer server. Sometimes it's also slow when trying to print from PDF viewers, but otherwise it rints immediately from other apps. Not that this has anything to do with your issue, but if what you're doing worked fine previously then it's likely that your routines aren't the problem. -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
execution speed issue when closing files
Geez! I just hate when the keysboard doesn't follow my typing...
The most common 'perceived slowness' is when IE does printing to any printer on my printer server. Sometimes it's also slow when trying to print from PDF viewers, but otherwise it prints immediately from other apps. Not that this has anything to do with your issue, but if what you're doing worked fine previously then it's likely that your routines aren't the problem. -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Speed of Execution | Excel Programming | |||
Speed up Excel execution | Excel Programming | |||
Execution speed for UDF | Excel Programming | |||
Extremely Slow VBA Execution Speed | Excel Programming | |||
MACRO execution speed | Excel Programming |