execution speed issue when closing files

[c1802362]

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 thecode 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

 

[GS]

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

 

[c1802362]

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

 

[GS]

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

 

[GS]

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

 

[GS]

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