Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
1.04 Mb with nothing in file ???
I have a large project with the following:
a. File size 1.78 Mb (suspiciously large) b. 7 worksheets c. Approx. 35 rectangles d. 6 userforms e. 13 code modules f. No addins installed This project will eventually be distributed throughout the entire company and will be used frequently. Unfortunately, it takes approx. 16 seconds to save on close. Not exactly elegant. I was suspicious of the file size and unhappy with closing time and so made a copy. I selectively deleted parts of the copy and checked the closing time and file size after each deletion. No smoking gun found. I ultimately ended up deleting everything including my xlb file. I also rebooted. I added a new (empty) worksheet so that I could delete the last sheet. Even though the project has no code modules, no userforms, not one word of code, no shapes, no addins installed, and only one blank worksheet, the file size is still listed as 1.04 Mb. Of note, improvement in closing time is not linear: at 1.78 Mb was 16 seconds, at 1.45 Mb was 8 seconds and at 1.04 Mb is 3 seconds. Any thoughts? I guess I should rebuild the whole thing. Also wondering if there would be an advantage to putting most of it in an addin and thus separate from the project. Theoretically, the save time will be improved since the addin contents won't be saved on close ??? Extremely appreciative of contributions. Rebuilding this is going to be a huge pain. Greg |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
1.04 Mb with nothing in file ???
For starters try running VBA Codecleaner:
http://www.appspro.com/Utilities/CodeCleaner.htm RBS "Greg Wilson" wrote in message ... I have a large project with the following: a. File size 1.78 Mb (suspiciously large) b. 7 worksheets c. Approx. 35 rectangles d. 6 userforms e. 13 code modules f. No addins installed This project will eventually be distributed throughout the entire company and will be used frequently. Unfortunately, it takes approx. 16 seconds to save on close. Not exactly elegant. I was suspicious of the file size and unhappy with closing time and so made a copy. I selectively deleted parts of the copy and checked the closing time and file size after each deletion. No smoking gun found. I ultimately ended up deleting everything including my xlb file. I also rebooted. I added a new (empty) worksheet so that I could delete the last sheet. Even though the project has no code modules, no userforms, not one word of code, no shapes, no addins installed, and only one blank worksheet, the file size is still listed as 1.04 Mb. Of note, improvement in closing time is not linear: at 1.78 Mb was 16 seconds, at 1.45 Mb was 8 seconds and at 1.04 Mb is 3 seconds. Any thoughts? I guess I should rebuild the whole thing. Also wondering if there would be an advantage to putting most of it in an addin and thus separate from the project. Theoretically, the save time will be improved since the addin contents won't be saved on close ??? Extremely appreciative of contributions. Rebuilding this is going to be a huge pain. Greg |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
1.04 Mb with nothing in file ???
Thanks for the response. I've been using Code Cleaner for years. Didn't help.
I broke down and rebuilt the wb. Wasn't as bad as I was expecting. I wrote the appended code which rebuilt the worksheets excluding borders and shapes. Not the best I'm sure, but worked. I also repeated advice given by Jim Cone recently when it was acting up: http://tinyurl.com/358e9k The result was that it shrunk from 1.78 mb to 680 kb. Time to save now 9 to 10 seconds instead of 16. Still a little slow but I think that's just the way it is. I am also experimenting with making the application invisible when closing with the following snippet. This closes the application instantly (make sure the VBE main window insn't open) and lets you do everything as far as I can tell short of opening a new application. With Application If .Workbooks.Count = 1 Then .Visible = False End With 'code that saves wb and closes... Code to rebuild the worksheets and named ranges. Not complete and not the best but did the job. Create a workbook named "Rebuild" first and make sure it's open. Sub CopyToNewWB() Dim ws As Worksheet, ws2 As Worksheet Dim c As Range, ma As Range Dim x As Integer With Workbooks("Rebuild.xls") Do Until .Worksheets.Count = ThisWorkbook.Worksheets.Count .Worksheets.Add Loop x = 0 For Each ws In ThisWorkbook.Worksheets x = x + 1 Set ws2 = .Worksheets(x) ws2.name = ws.name If ws.name < "Time Sheet Review" Then For Each c In ws.UsedRange.Cells If c.MergeCells And c.Address = c.MergeArea(1, 1).Address Then ws2.Range(c.MergeArea.Address).MergeCells = True FormatRng ws2.Range(c.MergeArea.Address), c.MergeArea Else FormatRng ws2.Range(c.Address), c.MergeArea End If Next End If Next For Each nm In ThisWorkbook.Names .Names.Add nm.name, nm.RefersTo Next End With End Sub Private Sub FormatRng(rng1 As Range, rng2 As Range) With rng1(1, 1) If .Column = 1 Then .RowHeight = rng2(1, 1).RowHeight If .Row = 1 Then .ColumnWidth = rng2(1, 1).ColumnWidth .Formula = rng2(1, 1).Formula .NumberFormat = rng2(1, 1).NumberFormat .MergeArea.Locked = rng2.Locked .Font.Size = rng2(1, 1).Font.Size .VerticalAlignment = rng2(1, 1).VerticalAlignment .HorizontalAlignment = rng2(1, 1).HorizontalAlignment .Orientation = rng2(1, 1).Orientation .Font.Color = rng2(1, 1).Font.Color If rng2(1, 1).Interior.ColorIndex < xlNone Then _ .Interior.ColorIndex = rng2(1, 1).Interior.ColorIndex .Font.name = rng2(1, 1).Font.name .Font.FontStyle = rng2(1, 1).Font.FontStyle End With End Sub Regards, Greg |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
1.04 Mb with nothing in file ???
Apart from the shapes, what is in the worksheets?
If there is a lot of data in the sheets then maybe it is an option to move this away from Excel to something else like a text file or maybe a database. RBS "Greg Wilson" wrote in message ... Thanks for the response. I've been using Code Cleaner for years. Didn't help. I broke down and rebuilt the wb. Wasn't as bad as I was expecting. I wrote the appended code which rebuilt the worksheets excluding borders and shapes. Not the best I'm sure, but worked. I also repeated advice given by Jim Cone recently when it was acting up: http://tinyurl.com/358e9k The result was that it shrunk from 1.78 mb to 680 kb. Time to save now 9 to 10 seconds instead of 16. Still a little slow but I think that's just the way it is. I am also experimenting with making the application invisible when closing with the following snippet. This closes the application instantly (make sure the VBE main window insn't open) and lets you do everything as far as I can tell short of opening a new application. With Application If .Workbooks.Count = 1 Then .Visible = False End With 'code that saves wb and closes... Code to rebuild the worksheets and named ranges. Not complete and not the best but did the job. Create a workbook named "Rebuild" first and make sure it's open. Sub CopyToNewWB() Dim ws As Worksheet, ws2 As Worksheet Dim c As Range, ma As Range Dim x As Integer With Workbooks("Rebuild.xls") Do Until .Worksheets.Count = ThisWorkbook.Worksheets.Count .Worksheets.Add Loop x = 0 For Each ws In ThisWorkbook.Worksheets x = x + 1 Set ws2 = .Worksheets(x) ws2.name = ws.name If ws.name < "Time Sheet Review" Then For Each c In ws.UsedRange.Cells If c.MergeCells And c.Address = c.MergeArea(1, 1).Address Then ws2.Range(c.MergeArea.Address).MergeCells = True FormatRng ws2.Range(c.MergeArea.Address), c.MergeArea Else FormatRng ws2.Range(c.Address), c.MergeArea End If Next End If Next For Each nm In ThisWorkbook.Names .Names.Add nm.name, nm.RefersTo Next End With End Sub Private Sub FormatRng(rng1 As Range, rng2 As Range) With rng1(1, 1) If .Column = 1 Then .RowHeight = rng2(1, 1).RowHeight If .Row = 1 Then .ColumnWidth = rng2(1, 1).ColumnWidth .Formula = rng2(1, 1).Formula .NumberFormat = rng2(1, 1).NumberFormat .MergeArea.Locked = rng2.Locked .Font.Size = rng2(1, 1).Font.Size .VerticalAlignment = rng2(1, 1).VerticalAlignment .HorizontalAlignment = rng2(1, 1).HorizontalAlignment .Orientation = rng2(1, 1).Orientation .Font.Color = rng2(1, 1).Font.Color If rng2(1, 1).Interior.ColorIndex < xlNone Then _ .Interior.ColorIndex = rng2(1, 1).Interior.ColorIndex .Font.name = rng2(1, 1).Font.name .Font.FontStyle = rng2(1, 1).Font.FontStyle End With End Sub Regards, Greg |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
1.04 Mb with nothing in file ???
Hi Greg,
The result was that it shrunk from 1.78 mb to 680 kb Might be a result of code now non-compiled vs previously partly/fully compiled, particularly if you have neither done Debug - compile nor run some code from each module before saving (worth doing). Time to save now 9 to 10 seconds instead of 16. Still seems slow. I have larger project that saves faster in what I'll bet is a much older system than yours. I notice the "linear" time difference does not tally with what you quoted in your OP - "and at 1.04 Mb is 3 seconds". Regards, Peter T "Greg Wilson" wrote in message ... Thanks for the response. I've been using Code Cleaner for years. Didn't help. I broke down and rebuilt the wb. Wasn't as bad as I was expecting. I wrote the appended code which rebuilt the worksheets excluding borders and shapes. Not the best I'm sure, but worked. I also repeated advice given by Jim Cone recently when it was acting up: http://tinyurl.com/358e9k The result was that it shrunk from 1.78 mb to 680 kb. Time to save now 9 to 10 seconds instead of 16. Still a little slow but I think that's just the way it is. I am also experimenting with making the application invisible when closing with the following snippet. This closes the application instantly (make sure the VBE main window insn't open) and lets you do everything as far as I can tell short of opening a new application. With Application If .Workbooks.Count = 1 Then .Visible = False End With 'code that saves wb and closes... Code to rebuild the worksheets and named ranges. Not complete and not the best but did the job. Create a workbook named "Rebuild" first and make sure it's open. Sub CopyToNewWB() Dim ws As Worksheet, ws2 As Worksheet Dim c As Range, ma As Range Dim x As Integer With Workbooks("Rebuild.xls") Do Until .Worksheets.Count = ThisWorkbook.Worksheets.Count .Worksheets.Add Loop x = 0 For Each ws In ThisWorkbook.Worksheets x = x + 1 Set ws2 = .Worksheets(x) ws2.name = ws.name If ws.name < "Time Sheet Review" Then For Each c In ws.UsedRange.Cells If c.MergeCells And c.Address = c.MergeArea(1, 1).Address Then ws2.Range(c.MergeArea.Address).MergeCells = True FormatRng ws2.Range(c.MergeArea.Address), c.MergeArea Else FormatRng ws2.Range(c.Address), c.MergeArea End If Next End If Next For Each nm In ThisWorkbook.Names .Names.Add nm.name, nm.RefersTo Next End With End Sub Private Sub FormatRng(rng1 As Range, rng2 As Range) With rng1(1, 1) If .Column = 1 Then .RowHeight = rng2(1, 1).RowHeight If .Row = 1 Then .ColumnWidth = rng2(1, 1).ColumnWidth .Formula = rng2(1, 1).Formula .NumberFormat = rng2(1, 1).NumberFormat .MergeArea.Locked = rng2.Locked .Font.Size = rng2(1, 1).Font.Size .VerticalAlignment = rng2(1, 1).VerticalAlignment .HorizontalAlignment = rng2(1, 1).HorizontalAlignment .Orientation = rng2(1, 1).Orientation .Font.Color = rng2(1, 1).Font.Color If rng2(1, 1).Interior.ColorIndex < xlNone Then _ .Interior.ColorIndex = rng2(1, 1).Interior.ColorIndex .Font.name = rng2(1, 1).Font.name .Font.FontStyle = rng2(1, 1).Font.FontStyle End With End Sub Regards, Greg |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
1.04 Mb with nothing in file ???
There isn't a lot in the worksheets but there is a lot of code. 98 pages of
code if you wanted to print it based on an actual count (i.e. number of times it fills the screen). The 6 userforms have a large effect I would think. I currently database in text files and import/export. The 98 pages of code sounds like a lot, but I don't think it's unusual. If you count them in one of your own large projects you will probably be surprised. The tests showed that there was something in the file that couldn't be cleaned up. As mentioned, it took 3 seconds to save the file when there was nothing left in it except a single worksheet with one character in it (so that Excel would prompt to save it). This compares with a fraction of a second with a new wb containing the default 3 pages. I was thinking of converting most of it to an addin. This is based on the assumption that it would be much faster because addins aren't saved on close. I was thinking that only the worksheets and class modules would therefore be in the file that gets saved. Don' want to do this unless there's a huge advantage. Greg "RB Smissaert" wrote: Apart from the shapes, what is in the worksheets? If there is a lot of data in the sheets then maybe it is an option to move this away from Excel to something else like a text file or maybe a database. RBS "Greg Wilson" wrote in message ... Thanks for the response. I've been using Code Cleaner for years. Didn't help. I broke down and rebuilt the wb. Wasn't as bad as I was expecting. I wrote the appended code which rebuilt the worksheets excluding borders and shapes. Not the best I'm sure, but worked. I also repeated advice given by Jim Cone recently when it was acting up: http://tinyurl.com/358e9k The result was that it shrunk from 1.78 mb to 680 kb. Time to save now 9 to 10 seconds instead of 16. Still a little slow but I think that's just the way it is. I am also experimenting with making the application invisible when closing with the following snippet. This closes the application instantly (make sure the VBE main window insn't open) and lets you do everything as far as I can tell short of opening a new application. With Application If .Workbooks.Count = 1 Then .Visible = False End With 'code that saves wb and closes... Code to rebuild the worksheets and named ranges. Not complete and not the best but did the job. Create a workbook named "Rebuild" first and make sure it's open. Sub CopyToNewWB() Dim ws As Worksheet, ws2 As Worksheet Dim c As Range, ma As Range Dim x As Integer With Workbooks("Rebuild.xls") Do Until .Worksheets.Count = ThisWorkbook.Worksheets.Count .Worksheets.Add Loop x = 0 For Each ws In ThisWorkbook.Worksheets x = x + 1 Set ws2 = .Worksheets(x) ws2.name = ws.name If ws.name < "Time Sheet Review" Then For Each c In ws.UsedRange.Cells If c.MergeCells And c.Address = c.MergeArea(1, 1).Address Then ws2.Range(c.MergeArea.Address).MergeCells = True FormatRng ws2.Range(c.MergeArea.Address), c.MergeArea Else FormatRng ws2.Range(c.Address), c.MergeArea End If Next End If Next For Each nm In ThisWorkbook.Names .Names.Add nm.name, nm.RefersTo Next End With End Sub Private Sub FormatRng(rng1 As Range, rng2 As Range) With rng1(1, 1) If .Column = 1 Then .RowHeight = rng2(1, 1).RowHeight If .Row = 1 Then .ColumnWidth = rng2(1, 1).ColumnWidth .Formula = rng2(1, 1).Formula .NumberFormat = rng2(1, 1).NumberFormat .MergeArea.Locked = rng2.Locked .Font.Size = rng2(1, 1).Font.Size .VerticalAlignment = rng2(1, 1).VerticalAlignment .HorizontalAlignment = rng2(1, 1).HorizontalAlignment .Orientation = rng2(1, 1).Orientation .Font.Color = rng2(1, 1).Font.Color If rng2(1, 1).Interior.ColorIndex < xlNone Then _ .Interior.ColorIndex = rng2(1, 1).Interior.ColorIndex .Font.name = rng2(1, 1).Font.name .Font.FontStyle = rng2(1, 1).Font.FontStyle End With End Sub Regards, Greg |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
1.04 Mb with nothing in file ???
I think changing it to an .xla sounds like a good idea.
Another thing worth trying is use Andrew Baker's Workbook Rebuilder. I have seen it that it cleaned a lot more than the free CodeCleaner although I can't explain why that would be. I think you can download a trial version. RBS "Greg Wilson" wrote in message ... There isn't a lot in the worksheets but there is a lot of code. 98 pages of code if you wanted to print it based on an actual count (i.e. number of times it fills the screen). The 6 userforms have a large effect I would think. I currently database in text files and import/export. The 98 pages of code sounds like a lot, but I don't think it's unusual. If you count them in one of your own large projects you will probably be surprised. The tests showed that there was something in the file that couldn't be cleaned up. As mentioned, it took 3 seconds to save the file when there was nothing left in it except a single worksheet with one character in it (so that Excel would prompt to save it). This compares with a fraction of a second with a new wb containing the default 3 pages. I was thinking of converting most of it to an addin. This is based on the assumption that it would be much faster because addins aren't saved on close. I was thinking that only the worksheets and class modules would therefore be in the file that gets saved. Don' want to do this unless there's a huge advantage. Greg "RB Smissaert" wrote: Apart from the shapes, what is in the worksheets? If there is a lot of data in the sheets then maybe it is an option to move this away from Excel to something else like a text file or maybe a database. RBS "Greg Wilson" wrote in message ... Thanks for the response. I've been using Code Cleaner for years. Didn't help. I broke down and rebuilt the wb. Wasn't as bad as I was expecting. I wrote the appended code which rebuilt the worksheets excluding borders and shapes. Not the best I'm sure, but worked. I also repeated advice given by Jim Cone recently when it was acting up: http://tinyurl.com/358e9k The result was that it shrunk from 1.78 mb to 680 kb. Time to save now 9 to 10 seconds instead of 16. Still a little slow but I think that's just the way it is. I am also experimenting with making the application invisible when closing with the following snippet. This closes the application instantly (make sure the VBE main window insn't open) and lets you do everything as far as I can tell short of opening a new application. With Application If .Workbooks.Count = 1 Then .Visible = False End With 'code that saves wb and closes... Code to rebuild the worksheets and named ranges. Not complete and not the best but did the job. Create a workbook named "Rebuild" first and make sure it's open. Sub CopyToNewWB() Dim ws As Worksheet, ws2 As Worksheet Dim c As Range, ma As Range Dim x As Integer With Workbooks("Rebuild.xls") Do Until .Worksheets.Count = ThisWorkbook.Worksheets.Count .Worksheets.Add Loop x = 0 For Each ws In ThisWorkbook.Worksheets x = x + 1 Set ws2 = .Worksheets(x) ws2.name = ws.name If ws.name < "Time Sheet Review" Then For Each c In ws.UsedRange.Cells If c.MergeCells And c.Address = c.MergeArea(1, 1).Address Then ws2.Range(c.MergeArea.Address).MergeCells = True FormatRng ws2.Range(c.MergeArea.Address), c.MergeArea Else FormatRng ws2.Range(c.Address), c.MergeArea End If Next End If Next For Each nm In ThisWorkbook.Names .Names.Add nm.name, nm.RefersTo Next End With End Sub Private Sub FormatRng(rng1 As Range, rng2 As Range) With rng1(1, 1) If .Column = 1 Then .RowHeight = rng2(1, 1).RowHeight If .Row = 1 Then .ColumnWidth = rng2(1, 1).ColumnWidth .Formula = rng2(1, 1).Formula .NumberFormat = rng2(1, 1).NumberFormat .MergeArea.Locked = rng2.Locked .Font.Size = rng2(1, 1).Font.Size .VerticalAlignment = rng2(1, 1).VerticalAlignment .HorizontalAlignment = rng2(1, 1).HorizontalAlignment .Orientation = rng2(1, 1).Orientation .Font.Color = rng2(1, 1).Font.Color If rng2(1, 1).Interior.ColorIndex < xlNone Then _ .Interior.ColorIndex = rng2(1, 1).Interior.ColorIndex .Font.name = rng2(1, 1).Font.name .Font.FontStyle = rng2(1, 1).Font.FontStyle End With End Sub Regards, Greg |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
1.04 Mb with nothing in file ???
Thanks again. I downloaded the trial version and gave it a go. It initially
reduced the listed file size hugely to about 450 kb. However, after the first save it was up to about 625. Save time not much improved, perhaps a second. Looks like a good product. The downside of making it an *.xla is that referencing the addin increases the load time. Right now it's instant. Greg "RB Smissaert" wrote: I think changing it to an .xla sounds like a good idea. Another thing worth trying is use Andrew Baker's Workbook Rebuilder. I have seen it that it cleaned a lot more than the free CodeCleaner although I can't explain why that would be. I think you can download a trial version. RBS "Greg Wilson" wrote in message ... There isn't a lot in the worksheets but there is a lot of code. 98 pages of code if you wanted to print it based on an actual count (i.e. number of times it fills the screen). The 6 userforms have a large effect I would think. I currently database in text files and import/export. The 98 pages of code sounds like a lot, but I don't think it's unusual. If you count them in one of your own large projects you will probably be surprised. The tests showed that there was something in the file that couldn't be cleaned up. As mentioned, it took 3 seconds to save the file when there was nothing left in it except a single worksheet with one character in it (so that Excel would prompt to save it). This compares with a fraction of a second with a new wb containing the default 3 pages. I was thinking of converting most of it to an addin. This is based on the assumption that it would be much faster because addins aren't saved on close. I was thinking that only the worksheets and class modules would therefore be in the file that gets saved. Don' want to do this unless there's a huge advantage. Greg "RB Smissaert" wrote: Apart from the shapes, what is in the worksheets? If there is a lot of data in the sheets then maybe it is an option to move this away from Excel to something else like a text file or maybe a database. RBS "Greg Wilson" wrote in message ... Thanks for the response. I've been using Code Cleaner for years. Didn't help. I broke down and rebuilt the wb. Wasn't as bad as I was expecting. I wrote the appended code which rebuilt the worksheets excluding borders and shapes. Not the best I'm sure, but worked. I also repeated advice given by Jim Cone recently when it was acting up: http://tinyurl.com/358e9k The result was that it shrunk from 1.78 mb to 680 kb. Time to save now 9 to 10 seconds instead of 16. Still a little slow but I think that's just the way it is. I am also experimenting with making the application invisible when closing with the following snippet. This closes the application instantly (make sure the VBE main window insn't open) and lets you do everything as far as I can tell short of opening a new application. With Application If .Workbooks.Count = 1 Then .Visible = False End With 'code that saves wb and closes... Code to rebuild the worksheets and named ranges. Not complete and not the best but did the job. Create a workbook named "Rebuild" first and make sure it's open. Sub CopyToNewWB() Dim ws As Worksheet, ws2 As Worksheet Dim c As Range, ma As Range Dim x As Integer With Workbooks("Rebuild.xls") Do Until .Worksheets.Count = ThisWorkbook.Worksheets.Count .Worksheets.Add Loop x = 0 For Each ws In ThisWorkbook.Worksheets x = x + 1 Set ws2 = .Worksheets(x) ws2.name = ws.name If ws.name < "Time Sheet Review" Then For Each c In ws.UsedRange.Cells If c.MergeCells And c.Address = c.MergeArea(1, 1).Address Then ws2.Range(c.MergeArea.Address).MergeCells = True FormatRng ws2.Range(c.MergeArea.Address), c.MergeArea Else FormatRng ws2.Range(c.Address), c.MergeArea End If Next End If Next For Each nm In ThisWorkbook.Names .Names.Add nm.name, nm.RefersTo Next End With End Sub Private Sub FormatRng(rng1 As Range, rng2 As Range) With rng1(1, 1) If .Column = 1 Then .RowHeight = rng2(1, 1).RowHeight If .Row = 1 Then .ColumnWidth = rng2(1, 1).ColumnWidth .Formula = rng2(1, 1).Formula .NumberFormat = rng2(1, 1).NumberFormat .MergeArea.Locked = rng2.Locked .Font.Size = rng2(1, 1).Font.Size .VerticalAlignment = rng2(1, 1).VerticalAlignment .HorizontalAlignment = rng2(1, 1).HorizontalAlignment .Orientation = rng2(1, 1).Orientation .Font.Color = rng2(1, 1).Font.Color If rng2(1, 1).Interior.ColorIndex < xlNone Then _ .Interior.ColorIndex = rng2(1, 1).Interior.ColorIndex .Font.name = rng2(1, 1).Font.name .Font.FontStyle = rng2(1, 1).Font.FontStyle End With End Sub Regards, Greg |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
1.04 Mb with nothing in file ???
Thanks Peter for responding. This is a repeat reply. My initial reply seems
to have been lost. I used Code Cleaner before the rebuild with no success. I rebooted also. I would think this would remove compiled code. I also noted the time difference (3 secs. for 1.04 mb). I think this is because there are gaps in the file somehow. The o/s perhaps thinks the file size is defined by the position of the last info in the file ignoring gaps (???). However, when tested against a new wb with the default 3 pages, saving time was almost instant. So there appears to be something in the file that can't be removed. I tried Andrew Bakers Workbook Rebuilder with modest success at speeding up the save time (perhaps a second). File size reduced to about 625 kb (after first save) versus 690 kb for my own rebuilt. These values fluctuate quite a bit. Greg "Peter T" wrote: Hi Greg, The result was that it shrunk from 1.78 mb to 680 kb Might be a result of code now non-compiled vs previously partly/fully compiled, particularly if you have neither done Debug - compile nor run some code from each module before saving (worth doing). Time to save now 9 to 10 seconds instead of 16. Still seems slow. I have larger project that saves faster in what I'll bet is a much older system than yours. I notice the "linear" time difference does not tally with what you quoted in your OP - "and at 1.04 Mb is 3 seconds". Regards, Peter T "Greg Wilson" wrote in message ... Thanks for the response. I've been using Code Cleaner for years. Didn't help. I broke down and rebuilt the wb. Wasn't as bad as I was expecting. I wrote the appended code which rebuilt the worksheets excluding borders and shapes. Not the best I'm sure, but worked. I also repeated advice given by Jim Cone recently when it was acting up: http://tinyurl.com/358e9k The result was that it shrunk from 1.78 mb to 680 kb. Time to save now 9 to 10 seconds instead of 16. Still a little slow but I think that's just the way it is. I am also experimenting with making the application invisible when closing with the following snippet. This closes the application instantly (make sure the VBE main window insn't open) and lets you do everything as far as I can tell short of opening a new application. With Application If .Workbooks.Count = 1 Then .Visible = False End With 'code that saves wb and closes... Code to rebuild the worksheets and named ranges. Not complete and not the best but did the job. Create a workbook named "Rebuild" first and make sure it's open. Sub CopyToNewWB() Dim ws As Worksheet, ws2 As Worksheet Dim c As Range, ma As Range Dim x As Integer With Workbooks("Rebuild.xls") Do Until .Worksheets.Count = ThisWorkbook.Worksheets.Count .Worksheets.Add Loop x = 0 For Each ws In ThisWorkbook.Worksheets x = x + 1 Set ws2 = .Worksheets(x) ws2.name = ws.name If ws.name < "Time Sheet Review" Then For Each c In ws.UsedRange.Cells If c.MergeCells And c.Address = c.MergeArea(1, 1).Address Then ws2.Range(c.MergeArea.Address).MergeCells = True FormatRng ws2.Range(c.MergeArea.Address), c.MergeArea Else FormatRng ws2.Range(c.Address), c.MergeArea End If Next End If Next For Each nm In ThisWorkbook.Names .Names.Add nm.name, nm.RefersTo Next End With End Sub Private Sub FormatRng(rng1 As Range, rng2 As Range) With rng1(1, 1) If .Column = 1 Then .RowHeight = rng2(1, 1).RowHeight If .Row = 1 Then .ColumnWidth = rng2(1, 1).ColumnWidth .Formula = rng2(1, 1).Formula .NumberFormat = rng2(1, 1).NumberFormat .MergeArea.Locked = rng2.Locked .Font.Size = rng2(1, 1).Font.Size .VerticalAlignment = rng2(1, 1).VerticalAlignment .HorizontalAlignment = rng2(1, 1).HorizontalAlignment .Orientation = rng2(1, 1).Orientation .Font.Color = rng2(1, 1).Font.Color If rng2(1, 1).Interior.ColorIndex < xlNone Then _ .Interior.ColorIndex = rng2(1, 1).Interior.ColorIndex .Font.name = rng2(1, 1).Font.name .Font.FontStyle = rng2(1, 1).Font.FontStyle End With End Sub Regards, Greg |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
1.04 Mb with nothing in file ???
What I do after editing my main .xla is compile, then save then run the WB
Rebuilder. I do this every time, just to be on the safe side as this is really large ..xla, over 6 Mb with all the comments, and empty lines, leading and trailing tabs/spaces etc. stripped. This is with nil at all in the sheets. Not sure now if there has to be a difference in loading an .xla compared to a .xls, but my main .xla is not loaded as an add-in (so not ticked under Tools, Addins), but just opened as a normal .xls file would be opened. Will have to figure out how to do it, but I have actually a licence for the WB Rebuilder to give away, if you are interested. RBS "Greg Wilson" wrote in message ... Thanks again. I downloaded the trial version and gave it a go. It initially reduced the listed file size hugely to about 450 kb. However, after the first save it was up to about 625. Save time not much improved, perhaps a second. Looks like a good product. The downside of making it an *.xla is that referencing the addin increases the load time. Right now it's instant. Greg "RB Smissaert" wrote: I think changing it to an .xla sounds like a good idea. Another thing worth trying is use Andrew Baker's Workbook Rebuilder. I have seen it that it cleaned a lot more than the free CodeCleaner although I can't explain why that would be. I think you can download a trial version. RBS "Greg Wilson" wrote in message ... There isn't a lot in the worksheets but there is a lot of code. 98 pages of code if you wanted to print it based on an actual count (i.e. number of times it fills the screen). The 6 userforms have a large effect I would think. I currently database in text files and import/export. The 98 pages of code sounds like a lot, but I don't think it's unusual. If you count them in one of your own large projects you will probably be surprised. The tests showed that there was something in the file that couldn't be cleaned up. As mentioned, it took 3 seconds to save the file when there was nothing left in it except a single worksheet with one character in it (so that Excel would prompt to save it). This compares with a fraction of a second with a new wb containing the default 3 pages. I was thinking of converting most of it to an addin. This is based on the assumption that it would be much faster because addins aren't saved on close. I was thinking that only the worksheets and class modules would therefore be in the file that gets saved. Don' want to do this unless there's a huge advantage. Greg "RB Smissaert" wrote: Apart from the shapes, what is in the worksheets? If there is a lot of data in the sheets then maybe it is an option to move this away from Excel to something else like a text file or maybe a database. RBS "Greg Wilson" wrote in message ... Thanks for the response. I've been using Code Cleaner for years. Didn't help. I broke down and rebuilt the wb. Wasn't as bad as I was expecting. I wrote the appended code which rebuilt the worksheets excluding borders and shapes. Not the best I'm sure, but worked. I also repeated advice given by Jim Cone recently when it was acting up: http://tinyurl.com/358e9k The result was that it shrunk from 1.78 mb to 680 kb. Time to save now 9 to 10 seconds instead of 16. Still a little slow but I think that's just the way it is. I am also experimenting with making the application invisible when closing with the following snippet. This closes the application instantly (make sure the VBE main window insn't open) and lets you do everything as far as I can tell short of opening a new application. With Application If .Workbooks.Count = 1 Then .Visible = False End With 'code that saves wb and closes... Code to rebuild the worksheets and named ranges. Not complete and not the best but did the job. Create a workbook named "Rebuild" first and make sure it's open. Sub CopyToNewWB() Dim ws As Worksheet, ws2 As Worksheet Dim c As Range, ma As Range Dim x As Integer With Workbooks("Rebuild.xls") Do Until .Worksheets.Count = ThisWorkbook.Worksheets.Count .Worksheets.Add Loop x = 0 For Each ws In ThisWorkbook.Worksheets x = x + 1 Set ws2 = .Worksheets(x) ws2.name = ws.name If ws.name < "Time Sheet Review" Then For Each c In ws.UsedRange.Cells If c.MergeCells And c.Address = c.MergeArea(1, 1).Address Then ws2.Range(c.MergeArea.Address).MergeCells = True FormatRng ws2.Range(c.MergeArea.Address), c.MergeArea Else FormatRng ws2.Range(c.Address), c.MergeArea End If Next End If Next For Each nm In ThisWorkbook.Names .Names.Add nm.name, nm.RefersTo Next End With End Sub Private Sub FormatRng(rng1 As Range, rng2 As Range) With rng1(1, 1) If .Column = 1 Then .RowHeight = rng2(1, 1).RowHeight If .Row = 1 Then .ColumnWidth = rng2(1, 1).ColumnWidth .Formula = rng2(1, 1).Formula .NumberFormat = rng2(1, 1).NumberFormat .MergeArea.Locked = rng2.Locked .Font.Size = rng2(1, 1).Font.Size .VerticalAlignment = rng2(1, 1).VerticalAlignment .HorizontalAlignment = rng2(1, 1).HorizontalAlignment .Orientation = rng2(1, 1).Orientation .Font.Color = rng2(1, 1).Font.Color If rng2(1, 1).Interior.ColorIndex < xlNone Then _ .Interior.ColorIndex = rng2(1, 1).Interior.ColorIndex .Font.name = rng2(1, 1).Font.name .Font.FontStyle = rng2(1, 1).Font.FontStyle End With End Sub Regards, Greg |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
1.04 Mb with nothing in file ???
I am indeed interested since I was seriously considering buying the product.
In exchange, I have a native Excel calendar control that I hope is worthy. It's actuality a toolbar but looks and acts very similar to a MonthView control. Should end the hassle with distributing stuff since it's entirely native and self-contained. Tested for accuracy with no problems (relies on VBA's date functions so not a surprise). Fits nicely in a code module. Graphics look good in XP at least. Greg "RB Smissaert" wrote: What I do after editing my main .xla is compile, then save then run the WB Rebuilder. I do this every time, just to be on the safe side as this is really large ..xla, over 6 Mb with all the comments, and empty lines, leading and trailing tabs/spaces etc. stripped. This is with nil at all in the sheets. Not sure now if there has to be a difference in loading an .xla compared to a .xls, but my main .xla is not loaded as an add-in (so not ticked under Tools, Addins), but just opened as a normal .xls file would be opened. Will have to figure out how to do it, but I have actually a licence for the WB Rebuilder to give away, if you are interested. RBS "Greg Wilson" wrote in message ... Thanks again. I downloaded the trial version and gave it a go. It initially reduced the listed file size hugely to about 450 kb. However, after the first save it was up to about 625. Save time not much improved, perhaps a second. Looks like a good product. The downside of making it an *.xla is that referencing the addin increases the load time. Right now it's instant. Greg "RB Smissaert" wrote: I think changing it to an .xla sounds like a good idea. Another thing worth trying is use Andrew Baker's Workbook Rebuilder. I have seen it that it cleaned a lot more than the free CodeCleaner although I can't explain why that would be. I think you can download a trial version. RBS "Greg Wilson" wrote in message ... There isn't a lot in the worksheets but there is a lot of code. 98 pages of code if you wanted to print it based on an actual count (i.e. number of times it fills the screen). The 6 userforms have a large effect I would think. I currently database in text files and import/export. The 98 pages of code sounds like a lot, but I don't think it's unusual. If you count them in one of your own large projects you will probably be surprised. The tests showed that there was something in the file that couldn't be cleaned up. As mentioned, it took 3 seconds to save the file when there was nothing left in it except a single worksheet with one character in it (so that Excel would prompt to save it). This compares with a fraction of a second with a new wb containing the default 3 pages. I was thinking of converting most of it to an addin. This is based on the assumption that it would be much faster because addins aren't saved on close. I was thinking that only the worksheets and class modules would therefore be in the file that gets saved. Don' want to do this unless there's a huge advantage. Greg "RB Smissaert" wrote: Apart from the shapes, what is in the worksheets? If there is a lot of data in the sheets then maybe it is an option to move this away from Excel to something else like a text file or maybe a database. RBS "Greg Wilson" wrote in message ... Thanks for the response. I've been using Code Cleaner for years. Didn't help. I broke down and rebuilt the wb. Wasn't as bad as I was expecting. I wrote the appended code which rebuilt the worksheets excluding borders and shapes. Not the best I'm sure, but worked. I also repeated advice given by Jim Cone recently when it was acting up: http://tinyurl.com/358e9k The result was that it shrunk from 1.78 mb to 680 kb. Time to save now 9 to 10 seconds instead of 16. Still a little slow but I think that's just the way it is. I am also experimenting with making the application invisible when closing with the following snippet. This closes the application instantly (make sure the VBE main window insn't open) and lets you do everything as far as I can tell short of opening a new application. With Application If .Workbooks.Count = 1 Then .Visible = False End With 'code that saves wb and closes... Code to rebuild the worksheets and named ranges. Not complete and not the best but did the job. Create a workbook named "Rebuild" first and make sure it's open. Sub CopyToNewWB() Dim ws As Worksheet, ws2 As Worksheet Dim c As Range, ma As Range Dim x As Integer With Workbooks("Rebuild.xls") Do Until .Worksheets.Count = ThisWorkbook.Worksheets.Count .Worksheets.Add Loop x = 0 For Each ws In ThisWorkbook.Worksheets x = x + 1 Set ws2 = .Worksheets(x) ws2.name = ws.name If ws.name < "Time Sheet Review" Then For Each c In ws.UsedRange.Cells If c.MergeCells And c.Address = c.MergeArea(1, 1).Address Then ws2.Range(c.MergeArea.Address).MergeCells = True FormatRng ws2.Range(c.MergeArea.Address), c.MergeArea Else FormatRng ws2.Range(c.Address), c.MergeArea End If Next End If Next For Each nm In ThisWorkbook.Names .Names.Add nm.name, nm.RefersTo Next End With End Sub Private Sub FormatRng(rng1 As Range, rng2 As Range) With rng1(1, 1) If .Column = 1 Then .RowHeight = rng2(1, 1).RowHeight If .Row = 1 Then .ColumnWidth = rng2(1, 1).ColumnWidth .Formula = rng2(1, 1).Formula .NumberFormat = rng2(1, 1).NumberFormat .MergeArea.Locked = rng2.Locked .Font.Size = rng2(1, 1).Font.Size .VerticalAlignment = rng2(1, 1).VerticalAlignment .HorizontalAlignment = rng2(1, 1).HorizontalAlignment .Orientation = rng2(1, 1).Orientation .Font.Color = rng2(1, 1).Font.Color If rng2(1, 1).Interior.ColorIndex < xlNone Then _ .Interior.ColorIndex = rng2(1, 1).Interior.ColorIndex .Font.name = rng2(1, 1).Font.name .Font.FontStyle = rng2(1, 1).Font.FontStyle End With End Sub Regards, Greg |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
1.04 Mb with nothing in file ???
I've just discouvered that the slow save is limited to my laptop:
XL 2003 / XP Professional. Takes about 8 seconds at this point but appears to be growing with each save. On another computer running XL 2000 / 2000 Professional it takes only about 2 1/2 seconds. File size appears to be growing here too but slower. Any ideas anyone? Greg "Greg Wilson" wrote: I have a large project with the following: a. File size 1.78 Mb (suspiciously large) b. 7 worksheets c. Approx. 35 rectangles d. 6 userforms e. 13 code modules f. No addins installed This project will eventually be distributed throughout the entire company and will be used frequently. Unfortunately, it takes approx. 16 seconds to save on close. Not exactly elegant. I was suspicious of the file size and unhappy with closing time and so made a copy. I selectively deleted parts of the copy and checked the closing time and file size after each deletion. No smoking gun found. I ultimately ended up deleting everything including my xlb file. I also rebooted. I added a new (empty) worksheet so that I could delete the last sheet. Even though the project has no code modules, no userforms, not one word of code, no shapes, no addins installed, and only one blank worksheet, the file size is still listed as 1.04 Mb. Of note, improvement in closing time is not linear: at 1.78 Mb was 16 seconds, at 1.45 Mb was 8 seconds and at 1.04 Mb is 3 seconds. Any thoughts? I guess I should rebuild the whole thing. Also wondering if there would be an advantage to putting most of it in an addin and thus separate from the project. Theoretically, the save time will be improved since the addin contents won't be saved on close ??? Extremely appreciative of contributions. Rebuilding this is going to be a huge pain. Greg |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
1.04 Mb with nothing in file ???
OK, I will look into it and come back off-list. Shouldn't be a problem.
RBS "Greg Wilson" wrote in message ... I am indeed interested since I was seriously considering buying the product. In exchange, I have a native Excel calendar control that I hope is worthy. It's actuality a toolbar but looks and acts very similar to a MonthView control. Should end the hassle with distributing stuff since it's entirely native and self-contained. Tested for accuracy with no problems (relies on VBA's date functions so not a surprise). Fits nicely in a code module. Graphics look good in XP at least. Greg "RB Smissaert" wrote: What I do after editing my main .xla is compile, then save then run the WB Rebuilder. I do this every time, just to be on the safe side as this is really large ..xla, over 6 Mb with all the comments, and empty lines, leading and trailing tabs/spaces etc. stripped. This is with nil at all in the sheets. Not sure now if there has to be a difference in loading an .xla compared to a .xls, but my main .xla is not loaded as an add-in (so not ticked under Tools, Addins), but just opened as a normal .xls file would be opened. Will have to figure out how to do it, but I have actually a licence for the WB Rebuilder to give away, if you are interested. RBS "Greg Wilson" wrote in message ... Thanks again. I downloaded the trial version and gave it a go. It initially reduced the listed file size hugely to about 450 kb. However, after the first save it was up to about 625. Save time not much improved, perhaps a second. Looks like a good product. The downside of making it an *.xla is that referencing the addin increases the load time. Right now it's instant. Greg "RB Smissaert" wrote: I think changing it to an .xla sounds like a good idea. Another thing worth trying is use Andrew Baker's Workbook Rebuilder. I have seen it that it cleaned a lot more than the free CodeCleaner although I can't explain why that would be. I think you can download a trial version. RBS "Greg Wilson" wrote in message ... There isn't a lot in the worksheets but there is a lot of code. 98 pages of code if you wanted to print it based on an actual count (i.e. number of times it fills the screen). The 6 userforms have a large effect I would think. I currently database in text files and import/export. The 98 pages of code sounds like a lot, but I don't think it's unusual. If you count them in one of your own large projects you will probably be surprised. The tests showed that there was something in the file that couldn't be cleaned up. As mentioned, it took 3 seconds to save the file when there was nothing left in it except a single worksheet with one character in it (so that Excel would prompt to save it). This compares with a fraction of a second with a new wb containing the default 3 pages. I was thinking of converting most of it to an addin. This is based on the assumption that it would be much faster because addins aren't saved on close. I was thinking that only the worksheets and class modules would therefore be in the file that gets saved. Don' want to do this unless there's a huge advantage. Greg "RB Smissaert" wrote: Apart from the shapes, what is in the worksheets? If there is a lot of data in the sheets then maybe it is an option to move this away from Excel to something else like a text file or maybe a database. RBS "Greg Wilson" wrote in message ... Thanks for the response. I've been using Code Cleaner for years. Didn't help. I broke down and rebuilt the wb. Wasn't as bad as I was expecting. I wrote the appended code which rebuilt the worksheets excluding borders and shapes. Not the best I'm sure, but worked. I also repeated advice given by Jim Cone recently when it was acting up: http://tinyurl.com/358e9k The result was that it shrunk from 1.78 mb to 680 kb. Time to save now 9 to 10 seconds instead of 16. Still a little slow but I think that's just the way it is. I am also experimenting with making the application invisible when closing with the following snippet. This closes the application instantly (make sure the VBE main window insn't open) and lets you do everything as far as I can tell short of opening a new application. With Application If .Workbooks.Count = 1 Then .Visible = False End With 'code that saves wb and closes... Code to rebuild the worksheets and named ranges. Not complete and not the best but did the job. Create a workbook named "Rebuild" first and make sure it's open. Sub CopyToNewWB() Dim ws As Worksheet, ws2 As Worksheet Dim c As Range, ma As Range Dim x As Integer With Workbooks("Rebuild.xls") Do Until .Worksheets.Count = ThisWorkbook.Worksheets.Count .Worksheets.Add Loop x = 0 For Each ws In ThisWorkbook.Worksheets x = x + 1 Set ws2 = .Worksheets(x) ws2.name = ws.name If ws.name < "Time Sheet Review" Then For Each c In ws.UsedRange.Cells If c.MergeCells And c.Address = c.MergeArea(1, 1).Address Then ws2.Range(c.MergeArea.Address).MergeCells = True FormatRng ws2.Range(c.MergeArea.Address), c.MergeArea Else FormatRng ws2.Range(c.Address), c.MergeArea End If Next End If Next For Each nm In ThisWorkbook.Names .Names.Add nm.name, nm.RefersTo Next End With End Sub Private Sub FormatRng(rng1 As Range, rng2 As Range) With rng1(1, 1) If .Column = 1 Then .RowHeight = rng2(1, 1).RowHeight If .Row = 1 Then .ColumnWidth = rng2(1, 1).ColumnWidth .Formula = rng2(1, 1).Formula .NumberFormat = rng2(1, 1).NumberFormat .MergeArea.Locked = rng2.Locked .Font.Size = rng2(1, 1).Font.Size .VerticalAlignment = rng2(1, 1).VerticalAlignment .HorizontalAlignment = rng2(1, 1).HorizontalAlignment .Orientation = rng2(1, 1).Orientation .Font.Color = rng2(1, 1).Font.Color If rng2(1, 1).Interior.ColorIndex < xlNone Then _ .Interior.ColorIndex = rng2(1, 1).Interior.ColorIndex .Font.name = rng2(1, 1).Font.name .Font.FontStyle = rng2(1, 1).Font.FontStyle End With End Sub Regards, Greg |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
1.04 Mb with nothing in file ???
Hi Greg,
I'm sure that's highly indicative, can't say of what though! I'm sure you've excluded the following but just in case - temp files, anti-virus. I note you have rebuilt the wb, was that in both setups. In particular was the original template sourced from respective versions. Just for curiosity how long to save a simple data file with loads of one's in cells and nothing else, say about 1 Mg, in each system. I typed A1:J10000 in the Name box, 1 in the Inputbar and ctrl-Enter. 100k one's saved for me in well under one second to a 927Mb file. What would that do for you in respective setups. Regards, Peter T "Greg Wilson" wrote in message ... I've just discouvered that the slow save is limited to my laptop: XL 2003 / XP Professional. Takes about 8 seconds at this point but appears to be growing with each save. On another computer running XL 2000 / 2000 Professional it takes only about 2 1/2 seconds. File size appears to be growing here too but slower. Any ideas anyone? Greg <snip |
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
1.04 Mb with nothing in file ???
Thanks for staying with this one for this long.
Is that all macros disabled or just close event code disabled? That was with all macros disabled. As I mentioned, I normally don't pay much attention to file size. This may a non-issue. I'll keep an eye on it though. If I come up with the answer I'll post it to the ng. Bart gave me a free registration for Andrew Baker's Workbook Rebuilder. I will put it to good use. So this has had a silver lining. Thanks again. Greg "Peter T" wrote: Close to 9 secs. for 700 kb. About 5 secs. when macros disabled. Is that all macros disabled or just close event code disabled? File size seems to be growing too but does reverse also. I'm thinking about installing code that monitors it's size over time, logging to a text file. That way I can see if it stabalizes at some point. I wouldn't get too hung up over minor fluctuations in file size while developing. There can be a 2-3 fold difference in size between totally uncopompiled (just after a clean), fully compiled, and states in between (after running code for first time). Also other normally harmless garbage can creep in that's unlikely to impact significantly on save time.. Saves in about 2 1/2 secs. on xl2000 running 2000 Professional. But on another machine running exactly the same thing takes about 7 seconds. So I'm at a complete loss. Now that really is intriguing! Everything same with the overall state of the file and Excel + VBE just before the save? Were the two files made from scratch in respective systems? If not, and the faster save relates to the system the file was made on might suggest some difference in references (inlc ocx /controls) assuming both file systems operating normally. Afraid I'm out of ideas, not that I had any useful ones in the first place. However it might be worth focusing on what's potentially different between otherwise similar systems.. Hopefully you will come up with a little pearl for the archives :-) Regards, Peter T "Greg Wilson" wrote in message ... Close to 9 secs. for 700 kb. About 5 secs. when macros disabled. File size seems to be growing too but does reverse also. I'm thinking about installing code that monitors it's size over time, logging to a text file. That way I can see if it stabalizes at some point. I can't think of any code that is unusual besides an Auto_Open macro. Tested with it commented out with no effect. Only non-default reference is to Forms 2.0. Only has two API declarations: GetKeyState and GetCursorPos. Saves in about 2 1/2 secs. on xl2000 running 2000 Professional. But on another machine running exactly the same thing takes about 7 seconds. So I'm at a complete loss. The UFs seem to be the worst offenders on file size. I exported all modules and then reimported them, checking the file size after each import. I've been googling for info on file growth. There is an issue with Pivot Tables. - Save triggers a recalc, if suspicious replace all formulas with "=" to "#=" I commented out about 1/3 of the formula with vertually no effect. Thanks hugely for the help. Greg "Peter T" wrote: I'm under the impression that this is just the way it is with xl2003 and XP and code modules. You might be right but I'm not so sure, still seems slow. What's it now, 8 sec for roughly a 1Mb project.? I would have thought if anything xl2003 & wXP would be faster than xl2000 w2000, but it seems not for you. Deleted the .xlb file, Although occasionally the xlb can bloat I can't imagine how it would affect this issue. More long shots before saving: - close all module windows and then the VBE - Save triggers a recalc, if suspicious replace all formulas with "=" to "#=" Regards, Peter T |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
File:1 and File:2 -- Double Files when Opening One File | Excel Discussion (Misc queries) | |||
I saved file A over file B. Can I get file B back? | Excel Discussion (Misc queries) | |||
opening an excel file opens a duplicate file of the same file | Excel Discussion (Misc queries) | |||
I SAVED A FILE OVER ANOTHER A FILE IN EXCEL. THE OLD FILE WAS AN . | Excel Discussion (Misc queries) | |||
i received a file that reads powerpoint document file file exten. | Excel Discussion (Misc queries) |