PDA

View Full Version : Odd behaviour of a worksheet.



Archangel117
12-03-2013, 11:26 AM
Hello, I have a question about a sheet that we have been working with here for several months and it is now displaying some odd behavior. What we are doing is using a sheet to collect hourly performance data for the machinery that was have running in our areas. It has been working fine until about a week ago and it started to slow down when switching from machine to another machine. We noticed now that for some reason the sheet is now saving twice instead of only once. We have made no change to the VB code but we are finding that it is now slowing things down when we enter hourly data. I cant post the workbook because the thing is kind of a monster but any insight as to why its doing that and where to look for our issue would be greatly appreciated. Thank you.

Aussiebear
12-03-2013, 01:48 PM
What about the concept of saving the data to another workbook? This way you can then control the size of the workbooks and keep the code working efficiently from the main workbook containing the code

Archangel117
12-03-2013, 02:05 PM
I will suggest this since it does make sense. The workbook is on a network drive and we noticed just in the last several days that it has begun to do this. In fact we have noticed that this is happening with other .xslm workbooks on the network now as well. Pretty much if it is an .xslm file it is doing the same thing? If you have any insight into that it would be greatly appreciated. I would post up the workbook but I have a feeling that it will be very difficult to duplicate outside of our system.

GTO
12-03-2013, 04:24 PM
Hi there,

Not sure if it would help us without the WB, but maybe post the procedure containing the Save command, along with any code in the BeforeSave event.

Mark

SamT
12-03-2013, 04:31 PM
@ Aussiebear: +1

@ Archangel117:

Be sure that change tracking is turned off for that workbook.
I suspect that is the issue.

Run ExcelDiet() (http://www.vbaexpress.com/kb/getarticle.php?kb_id=83) on the workbook before saving it.
This can greatly reduce the time it takes to save the book. At least it does in Excel < 2007 with monstrous worksheets. :) In the interest of time, you can probably get away with just running it once a day or week on each workbook. You might find that you can schedule different books for different periods.

Check that your code is not just hiding old sheets, but in fact is either deleting them or overwriting them.



I doubt that this is an issue, but if those don't stop the problem, you might try deliberately setting it's Saved Property to True when programmatically saving the book..

Aussiebear
12-03-2013, 08:13 PM
@ Aussiebear: +1


and I was hoping for 1.1, …. oh well next time perhaps.