Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
What's causing file to increase in size and slow program?
I'm completely baffled.
I hope someone can help. I created a workbook in which I copy information which is in a table on a web page and paste this directly into Excel. Then I run a macro which reformats this data, and sorts it a couple of times in different sections. Two problems have arisen. First, whenever the data is copied from the Webpage, it seems to take a very long time before the paste in Excel finished doing it's thing and allows me to manipulate the data. This problem has been there from the very beginning. Always a long delay between doing the "Copy" of the selected data, and Excel finishing the "Paste" function. Next, problem is something that has only come to light recently. The macros have started running slower and slower. It has gotten to the point where it now takes about 15 or 20 minutes to sort about 30 lines of data (about 30 columns). I've noted that the Excel file has become humoungous in size...well over 9 MB, which makes me think that somehow the file is accumulating a lot of garbage, but I can't see anything except for the data and formulas which I put in the cells. I'm very suspicions that the bloated size has something to do with the problem because at one point Excel closed and generated a recovered file which contained most of the formulas except for a pair of user defined functions, and this only occupied about 150 KB when it was saved. Oh, I've tried clearing out all my temp files ...that doesn't make any difference. What can I try next? Jack |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
What's causing file to increase in size and slow program?
Where does CRTL + END take you?
Excel can over-estimate where the "real" used range is. This is the most common cause of "Bloat". If CRTL + END takes you far below and to the right of what you know is the real used range, select all rows below your data and EditDelete. Do the same for Columns to the right. Save you workbook and in some versions you will have to Close and re-open to see the difference. Gord Dibben Excel MVP - XL97 SR2 & XL2002 On Wed, 30 Jul 2003 16:17:46 -0700, "jfeka" wrote: I'm completely baffled. I hope someone can help. I created a workbook in which I copy information which is in a table on a web page and paste this directly into Excel. Then I run a macro which reformats this data, and sorts it a couple of times in different sections. Two problems have arisen. First, whenever the data is copied from the Webpage, it seems to take a very long time before the paste in Excel finished doing it's thing and allows me to manipulate the data. This problem has been there from the very beginning. Always a long delay between doing the "Copy" of the selected data, and Excel finishing the "Paste" function. Next, problem is something that has only come to light recently. The macros have started running slower and slower. It has gotten to the point where it now takes about 15 or 20 minutes to sort about 30 lines of data (about 30 columns). I've noted that the Excel file has become humoungous in size...well over 9 MB, which makes me think that somehow the file is accumulating a lot of garbage, but I can't see anything except for the data and formulas which I put in the cells. I'm very suspicions that the bloated size has something to do with the problem because at one point Excel closed and generated a recovered file which contained most of the formulas except for a pair of user defined functions, and this only occupied about 150 KB when it was saved. Oh, I've tried clearing out all my temp files ...that doesn't make any difference. What can I try next? Jack |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
What's causing file to increase in size and slow program?
Adding to the previus comment if the Workbook is in a ShareMode turnoff
"Keep History" . "Gord Dibben" wrote in message ... Where does CRTL + END take you? Excel can over-estimate where the "real" used range is. This is the most common cause of "Bloat". If CRTL + END takes you far below and to the right of what you know is the real used range, select all rows below your data and EditDelete. Do the same for Columns to the right. Save you workbook and in some versions you will have to Close and re-open to see the difference. Gord Dibben Excel MVP - XL97 SR2 & XL2002 On Wed, 30 Jul 2003 16:17:46 -0700, "jfeka" wrote: I'm completely baffled. I hope someone can help. I created a workbook in which I copy information which is in a table on a web page and paste this directly into Excel. Then I run a macro which reformats this data, and sorts it a couple of times in different sections. Two problems have arisen. First, whenever the data is copied from the Webpage, it seems to take a very long time before the paste in Excel finished doing it's thing and allows me to manipulate the data. This problem has been there from the very beginning. Always a long delay between doing the "Copy" of the selected data, and Excel finishing the "Paste" function. Next, problem is something that has only come to light recently. The macros have started running slower and slower. It has gotten to the point where it now takes about 15 or 20 minutes to sort about 30 lines of data (about 30 columns). I've noted that the Excel file has become humoungous in size...well over 9 MB, which makes me think that somehow the file is accumulating a lot of garbage, but I can't see anything except for the data and formulas which I put in the cells. I'm very suspicions that the bloated size has something to do with the problem because at one point Excel closed and generated a recovered file which contained most of the formulas except for a pair of user defined functions, and this only occupied about 150 KB when it was saved. Oh, I've tried clearing out all my temp files ...that doesn't make any difference. What can I try next? Jack |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
What's causing file to increase in size and slow program?
Steve, I tried your solution, no luck....still a hugh
file and long delays in just about everything. Any other ideas? Jack -----Original Message----- Where does CRTL + END take you? Excel can over-estimate where the "real" used range is. This is the most common cause of "Bloat". If CRTL + END takes you far below and to the right of what you know is the real used range, select all rows below your data and EditDelete. Do the same for Columns to the right. Save you workbook and in some versions you will have to Close and re-open to see the difference. Gord Dibben Excel MVP - XL97 SR2 & XL2002 On Wed, 30 Jul 2003 16:17:46 -0700, "jfeka" wrote: I'm completely baffled. I hope someone can help. I created a workbook in which I copy information which is in a table on a web page and paste this directly into Excel. Then I run a macro which reformats this data, and sorts it a couple of times in different sections. Two problems have arisen. First, whenever the data is copied from the Webpage, it seems to take a very long time before the paste in Excel finished doing it's thing and allows me to manipulate the data. This problem has been there from the very beginning. Always a long delay between doing the "Copy" of the selected data, and Excel finishing the "Paste" function. Next, problem is something that has only come to light recently. The macros have started running slower and slower. It has gotten to the point where it now takes about 15 or 20 minutes to sort about 30 lines of data (about 30 columns). I've noted that the Excel file has become humoungous in size...well over 9 MB, which makes me think that somehow the file is accumulating a lot of garbage, but I can't see anything except for the data and formulas which I put in the cells. I'm very suspicions that the bloated size has something to do with the problem because at one point Excel closed and generated a recovered file which contained most of the formulas except for a pair of user defined functions, and this only occupied about 150 KB when it was saved. Oh, I've tried clearing out all my temp files ...that doesn't make any difference. What can I try next? Jack . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
What's causing file to increase in size and slow program?
No luck steve, (maybe I should call you Gord to make up
for the faux pax I committed when I replied to Gord and called him steve.) my defined names are few and within what I want them to be. Any other ideas? Jack -----Original Message----- Take a look at your defined names list. I once had this problem and found several hundred names beginning with Table.... Delected these names and things got back to normal. Now I have an event macro that deletes all names Like "Table*" And yes, my paste does take several seconds, but not too long. steve "jfeka" wrote in message ... I'm completely baffled. I hope someone can help. I created a workbook in which I copy information which is in a table on a web page and paste this directly into Excel. Then I run a macro which reformats this data, and sorts it a couple of times in different sections. Two problems have arisen. First, whenever the data is copied from the Webpage, it seems to take a very long time before the paste in Excel finished doing it's thing and allows me to manipulate the data. This problem has been there from the very beginning. Always a long delay between doing the "Copy" of the selected data, and Excel finishing the "Paste" function. Next, problem is something that has only come to light recently. The macros have started running slower and slower. It has gotten to the point where it now takes about 15 or 20 minutes to sort about 30 lines of data (about 30 columns). I've noted that the Excel file has become humoungous in size...well over 9 MB, which makes me think that somehow the file is accumulating a lot of garbage, but I can't see anything except for the data and formulas which I put in the cells. I'm very suspicions that the bloated size has something to do with the problem because at one point Excel closed and generated a recovered file which contained most of the formulas except for a pair of user defined functions, and this only occupied about 150 KB when it was saved. Oh, I've tried clearing out all my temp files ...that doesn't make any difference. What can I try next? Jack . |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
What's causing file to increase in size and slow program?
I have had a case like this once too, what I found is that
my macro was pasting a new web query over the top of the old one. Whenever it refreshed all the web queries ran which took ages. Try deleting all the data on the page (or even the entire page to get rid of the history of it) and rerunning your macro, this may stop the double up web queries ro any other sort of duplicated data that is hanging around. -----Original Message----- No luck steve, (maybe I should call you Gord to make up for the faux pax I committed when I replied to Gord and called him steve.) my defined names are few and within what I want them to be. Any other ideas? Jack -----Original Message----- Take a look at your defined names list. I once had this problem and found several hundred names beginning with Table.... Delected these names and things got back to normal. Now I have an event macro that deletes all names Like "Table*" And yes, my paste does take several seconds, but not too long. steve "jfeka" wrote in message ... I'm completely baffled. I hope someone can help. I created a workbook in which I copy information which is in a table on a web page and paste this directly into Excel. Then I run a macro which reformats this data, and sorts it a couple of times in different sections. Two problems have arisen. First, whenever the data is copied from the Webpage, it seems to take a very long time before the paste in Excel finished doing it's thing and allows me to manipulate the data. This problem has been there from the very beginning. Always a long delay between doing the "Copy" of the selected data, and Excel finishing the "Paste" function. Next, problem is something that has only come to light recently. The macros have started running slower and slower. It has gotten to the point where it now takes about 15 or 20 minutes to sort about 30 lines of data (about 30 columns). I've noted that the Excel file has become humoungous in size...well over 9 MB, which makes me think that somehow the file is accumulating a lot of garbage, but I can't see anything except for the data and formulas which I put in the cells. I'm very suspicions that the bloated size has something to do with the problem because at one point Excel closed and generated a recovered file which contained most of the formulas except for a pair of user defined functions, and this only occupied about 150 KB when it was saved. Oh, I've tried clearing out all my temp files ...that doesn't make any difference. What can I try next? Jack . . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Links Causing File Size Jump | Excel Discussion (Misc queries) | |||
Hidden data causing huge file size | Excel Discussion (Misc queries) | |||
File size increase | Excel Discussion (Misc queries) | |||
Excel slow to close file/program | Excel Discussion (Misc queries) | |||
File size increase | Excel Discussion (Misc queries) |