Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
open for append
Can I open a workbook for append. I am programmming a VBA program and I add
every Minute 1 Row to a workbook. After some Hours, the workbook gets very big and saving takes a lot of time. Any suggestions? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
open for append
At one row per minute - that is only 6000 rows after 100 hours.
This is not that much. So the problem may be you are adding rows and or columns that are not needed. These should be deleted. (see the comments from Bill Lunney below). The other thing to look at is the complexity and number of formulas. These could slow the saving process down. I just created a workbook with over 6000 rows and 20 columns. This took about 10 seconds to save. Also check your Temp folders: Empty your Temp folder regularly. Go into Internet Explorer and empty your Temporary Internet folder by going to Tools Internet Options Delete Files Delete all offline content OK Empty your trash can. These 3 folders fill up and can slow Excel down. steve ============================================= From Bill Lunney (to another post) - Typically code is not what adds significantly to file size. It's the worksheets themselves. Recreation of the worksheets is the best and only way to get a reasonable file size reduction (at least have the potential of). However with that many worksheets this may not be practical. I'm no longer surprised by the gains that can be achieved by doing this purely because over time even a clean-ish looking sheet can contain lots of invisible formatting. Try ensuring your file is saved in Excel 97 only format. Saving in other formats can double the size due to compatibility constraints. Although reading the rest of the post suggests you've already experimented with this. If your confident that the worksheets are reasonably cleanly designed (and there's no stray random formatting etc. everywhere) then it's clear you have to target the code use. Perhaps consider using a COM component if your architecture allows for it. This way you can centralise a lot of your code base and save repetition. However public functions would, to a large degree, do the same thing in your case. -- Regards, Bill Lunney www.billlunney.com also - This may not be particulary useful in your case but here are some quick bullet points on the subject. Feedback is welcome as I'll expand this as/when I get time. http://www.billlunney.com/Excel/FAQ/...ExcelFAQID=125 "Marcel Jahn" wrote in message ... Can I open a workbook for append. I am programmming a VBA program and I add every Minute 1 Row to a workbook. After some Hours, the workbook gets very big and saving takes a lot of time. Any suggestions? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
COPY / APPEND` | Excel Worksheet Functions | |||
Append query | New Users to Excel | |||
How do you append one row to another? | Excel Worksheet Functions | |||
Append text | Excel Discussion (Misc queries) | |||
Append worksheets together in one | Excel Discussion (Misc queries) |