OLE crash from Excel to Word

[tommy_mo]

Hello Folks- Just wondering if anyone here has a tip for my problem. I've got an Excel file that is being used to fill in word documents via linked objects. The problem is that the excel file is growing exponentially, and seems to crash frequently. Right now the file is about 15 megs, and takes several minutes to open. Is there a better way to link the Excel files to the word documents that won't cause such a slow down? Any tips? Cheers to anyone who can help. -Tom

 

[shades]

Howdy. Part of that depends on the design of the spreadsheet. I have seen very efficient 15 MB workbooks, and very inefficient, slow, prone-to-crash 2 MB workbooks.

I suspect that "several minutes to open" suggests formula issues. The sequence of formulas makes a big difference in how efficient/fast XL performs those tasks. How many formulas are involved? What is the sequence for them?
________
Extreme vaporizer review

 

[tommy_mo]

Thanks for the response! We have over 200 formulas for sure. The spreadsheet is used to calculate times that are then linked to checklists in Word. It seems like the more Word documents we link the bigger the .xls file gets. Which I've read is true for OLE. Does anyone know if there is a performance difference between pasting an Excel Object vs. unformatted text? I'm wondering if using unformatted text will speed things up.

Can you expand on the sequencing issue you were talking about? I know the formulas generally references the same cell and add time.

eg. =$AF$10+TIME(11,50,0)

Thanks for your help. Cheers. - Tom

 

[shades]

I would suspect that unformatted text would be faster (although I haven't done this specific activity).

Sequencing refers to the flow of the formulas (left-to-right, then top-to-bottom, etc.). But also sequencing of the worksheets themselves. The naming convention becomes critical at this point. When I start working with someone to rectify the problem, I tell them to have all data worksheets first (so, every datasheet would have a name such as A1_Data1, A2_Data2, A3_Data3, etc.). Then you want the first set of formula sheets (B1_Formula1, B2_Formula2, etc.). These can pull data from any data worksheet, and any initial formula worksheet prior to it. So B3_Formula3 can pull from B1_Formula1, but not the other way around. Then if you have another set of formula sheets, then would be C1_Formula1, C2_Formula2, etc. Again, they can pull from any Data sheet, and any initial formula sheets. If you use Display worksheets that draw from each of these, just be sure to pull from previous ones. I took a very inefficient workbook of 19 MB that someone "developed" over a period of time that took a LONG time to re-calculate. After I was done, it was 4.5 MB and took less than 5 seconds to recalculate.

It also depends on what type of formulas you use. Some take more time/RAM to calculate.
________
HONDA CBF500 SPECIFICATIONS

 

[tommy_mo]

Thanks for your help.

Your tips sound promising. Would you happen to know if referencing the same cell repeatedly would slow the file down? For example using the following formula in consecutive rows:

=$AF$10+TIME(11,30,0)
=$AF$10+TIME(11,40,0)
=$AF$10+TIME(11,50,0)

The same cell is referenced for almost all of the formulas. 2 Columns reference the cell mentioned above 100 times.

Would it be faster to "piggy back" the formulas so that the cell "AF10" is referenced one time at the beginning of the column, and then add time to each consecutive cell?

Cheers for any help! -Tom