open for append

  • Thread starter Thread starter Marcel Jahn
  • Start date Start date
M

Marcel Jahn

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?
 
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
Click to expand...
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/DisplayFAQ.ascx?ExcelFAQID=125
 
Back
Top