Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel - huge file sizes
I have experienced some problems with writting formulas
in visual basic for application in Excel. I have been searching all over the net and posting questions on communities, but without any help. I haop that you can help me. We have a rather large spreadsheet which is automated with visual basic modules. The problem is when we write the formulas to the spreadsheets in the VB module the spreadsheet file ends up being VERY large - usually resulting in Excel crashing. If the workbook with the formulas entered by VBA is saved, closed, opened and saved again the size of the file is about 1/20 of the size before. Or if the worksheets with the formulas that VBA wrote is copied and then deleted - again the file size is again normal. How can that be?? Are you aware of that bug - and do you have a solution?? We have made a workarraound, but as soon as the spreadsheets reach a certain size it still causes Excel to crash. Any help would be appriciated!! You can test the problem easily by running this simple code, saving the workbook and notecing the size of the file. Then close the file, open it again and save and see how the file size is reduced. Sub test() For x = 1 To 100 For y = 1 To 100 With Worksheets("sheet1").Range("B2").Offset(x, y) .FormulaR1C1 = "= R[-1]C[-1]" End With Next y Next x End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel - huge file sizes
Hi,
I tried your problem using the code given and discovered the following..... 1. The workbook when first saved was 369kB 2. After closing, opening and resaving the volume reduced to 332kB With no further changes to the file, it has stayed at this value despite opening and closing the file and Excel several times. I do not appear to have the same problem - is it your operating system ? I use Excel 2002 SP2 on XP system. "Ekelund" wrote in message ... I have experienced some problems with writting formulas in visual basic for application in Excel. I have been searching all over the net and posting questions on communities, but without any help. I haop that you can help me. We have a rather large spreadsheet which is automated with visual basic modules. The problem is when we write the formulas to the spreadsheets in the VB module the spreadsheet file ends up being VERY large - usually resulting in Excel crashing. If the workbook with the formulas entered by VBA is saved, closed, opened and saved again the size of the file is about 1/20 of the size before. Or if the worksheets with the formulas that VBA wrote is copied and then deleted - again the file size is again normal. How can that be?? Are you aware of that bug - and do you have a solution?? We have made a workarraound, but as soon as the spreadsheets reach a certain size it still causes Excel to crash. Any help would be appriciated!! You can test the problem easily by running this simple code, saving the workbook and notecing the size of the file. Then close the file, open it again and save and see how the file size is reduced. Sub test() For x = 1 To 100 For y = 1 To 100 With Worksheets("sheet1").Range("B2").Offset(x, y) .FormulaR1C1 = "= R[-1]C[-1]" End With Next y Next x End Sub ----== Posted via Newsfeed.Com - Unlimited-Uncensored-Secure Usenet News==---- http://www.newsfeed.com The #1 Newsgroup Service in the World! 100,000 Newsgroups ---= 19 East/West-Coast Specialized Servers - Total Privacy via Encryption =--- |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel - huge file sizes
I had a similar experience with regard to the initial and subsequent file
sizes. Copying the sheet and deleting the original sheet shaved another 1kb or so. I also noticed that Ctrl-Home takes me to cell A2 and Ctrl-End takes me to cell CX2 which is not what I would expect. I'm using Windows 2000 and Excel 2000 SP-3 Regards Trevor "Nigel" wrote in message ... Hi, I tried your problem using the code given and discovered the following..... 1. The workbook when first saved was 369kB 2. After closing, opening and resaving the volume reduced to 332kB With no further changes to the file, it has stayed at this value despite opening and closing the file and Excel several times. I do not appear to have the same problem - is it your operating system ? I use Excel 2002 SP2 on XP system. "Ekelund" wrote in message ... I have experienced some problems with writting formulas in visual basic for application in Excel. I have been searching all over the net and posting questions on communities, but without any help. I haop that you can help me. We have a rather large spreadsheet which is automated with visual basic modules. The problem is when we write the formulas to the spreadsheets in the VB module the spreadsheet file ends up being VERY large - usually resulting in Excel crashing. If the workbook with the formulas entered by VBA is saved, closed, opened and saved again the size of the file is about 1/20 of the size before. Or if the worksheets with the formulas that VBA wrote is copied and then deleted - again the file size is again normal. How can that be?? Are you aware of that bug - and do you have a solution?? We have made a workarraound, but as soon as the spreadsheets reach a certain size it still causes Excel to crash. Any help would be appriciated!! You can test the problem easily by running this simple code, saving the workbook and notecing the size of the file. Then close the file, open it again and save and see how the file size is reduced. Sub test() For x = 1 To 100 For y = 1 To 100 With Worksheets("sheet1").Range("B2").Offset(x, y) .FormulaR1C1 = "= R[-1]C[-1]" End With Next y Next x End Sub ----== Posted via Newsfeed.Com - Unlimited-Uncensored-Secure Usenet News==---- http://www.newsfeed.com The #1 Newsgroup Service in the World! 100,000 Newsgroups ---= 19 East/West-Coast Specialized Servers - Total Privacy via Encryption =--- |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel - huge file sizes
in a different environment:
I had the same experience as Nigel 367KB after running the macro 330 KB after all subsequent opening, saves, closings. Excel 97 SR2, Win 98 SE -- Regards, Tom Ogilvy Nigel wrote in message ... Hi, I tried your problem using the code given and discovered the following..... 1. The workbook when first saved was 369kB 2. After closing, opening and resaving the volume reduced to 332kB With no further changes to the file, it has stayed at this value despite opening and closing the file and Excel several times. I do not appear to have the same problem - is it your operating system ? I use Excel 2002 SP2 on XP system. "Ekelund" wrote in message ... I have experienced some problems with writting formulas in visual basic for application in Excel. I have been searching all over the net and posting questions on communities, but without any help. I haop that you can help me. We have a rather large spreadsheet which is automated with visual basic modules. The problem is when we write the formulas to the spreadsheets in the VB module the spreadsheet file ends up being VERY large - usually resulting in Excel crashing. If the workbook with the formulas entered by VBA is saved, closed, opened and saved again the size of the file is about 1/20 of the size before. Or if the worksheets with the formulas that VBA wrote is copied and then deleted - again the file size is again normal. How can that be?? Are you aware of that bug - and do you have a solution?? We have made a workarraound, but as soon as the spreadsheets reach a certain size it still causes Excel to crash. Any help would be appriciated!! You can test the problem easily by running this simple code, saving the workbook and notecing the size of the file. Then close the file, open it again and save and see how the file size is reduced. Sub test() For x = 1 To 100 For y = 1 To 100 With Worksheets("sheet1").Range("B2").Offset(x, y) .FormulaR1C1 = "= R[-1]C[-1]" End With Next y Next x End Sub ----== Posted via Newsfeed.Com - Unlimited-Uncensored-Secure Usenet News==---- http://www.newsfeed.com The #1 Newsgroup Service in the World! 100,000 Newsgroups ---= 19 East/West-Coast Specialized Servers - Total Privacy via Encryption =--- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel Huge File. | Excel Discussion (Misc queries) | |||
Excel File Huge with no data | Excel Discussion (Misc queries) | |||
i need to import a huge txt file into excel | Excel Discussion (Misc queries) | |||
huge huge excel file... why? | Excel Discussion (Misc queries) | |||
Excel File sizes and speed | Excel Discussion (Misc queries) |