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