Hello,

I have a VB application that I have written that generates
charts and tables in Excel, and pastes them into
PowerPoint. I have found that the largest performance
bottleneck seems to be with the chart and with the table
rendering in excel.

Does anyone have any performance tips for automating excel?

Thanks
Matt

Matt,

standard bottlenecks appear to be screenupdating and pagebreak
visibility.

BUT a lot depends on how you insert your data.. using variables is
generally a lot faster then just working with the selection and
activecell.

you can also fill an entire range from an array instead of cell by cell
(that's the real winner)

I didn't time for charts but i suspect the ScreenUpdating will be a
major factor.


timings to fill a range of 5000 cells

5000 5000 5000 5000 5000 4999
11,847 11,166 11,287 2,163 1,291 0,041


1: SELECT SU=TRUE, PB=TRUE
2: SELECT SU=TRUE, PB=FALSE
3: SELECT SU=FALSE, PB=TRUE
4: SELECT SU=FALSE, PB=FALSE
5: RNG SU=FALSE, PB=FALSE
6: ARRAY SU=FALSE, PB=FALSE

Code for 4, 5 and 6:

Application.ScreenUpdating = False
ActiveSheet.DisplayPageBreaks = False

m = 5000
t = Timer
[d1].Select
For r = 1 To m
ActiveCell = r
ActiveCell.Offset(1).Select
Next
Cells(r, 4) = Timer - t

t = Timer
For r = 1 To m
Cells(r, 5) = r
Next
Cells(r, 5) = Timer - t

t = Timer
ReDim arr(m) As Long
For r = 1 To m
arr(r) = r
Next
Cells(1, 6).Resize(r - 1) = WorksheetFunction.Transpose(arr)
Cells(r, 6) = Timer - t

if there's a lot of data or a lot of calculations
then it may be worth checking out the efficiency of your
worksheetfunctions.


suc6.

if u want, end me your xls & ppt and i can have a look.
address below.


keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


"Matt Tessar" wrote:

Hello,

I have a VB application that I have written that generates
charts and tables in Excel, and pastes them into
PowerPoint. I have found that the largest performance
bottleneck seems to be with the chart and with the table
rendering in excel.

Does anyone have any performance tips for automating excel?

Thanks
Matt

Matt:

Are you pasting your charts into PowerPoint as Links? If so, HOW? I also
wrote a table and chart exporter from Excel to PPT, but I couldn't find a
way to PasteSpecial a chart as a link. Someone please tell me if it's
impossible.

Bruce Cooley


"Matt Tessar" wrote in message
...
: Hello,
:
: I have a VB application that I have written that generates
: charts and tables in Excel, and pastes them into
: PowerPoint. I have found that the largest performance
: bottleneck seems to be with the chart and with the table
: rendering in excel.
:
: Does anyone have any performance tips for automating excel?
:
: Thanks
: Matt
:

Bruce,

I am pasting them using as an OLE Object using paste
special. Sorry, but I never tried pasting them as links.
Here is the line of code that I use to paste them as an
OLE Object.

slide.Shapes.PasteSpecial ppPasteOLEObject,
msoFalse, , , , msoFalse


Thanks
Matt


-----Original Message-----
Matt:

Are you pasting your charts into PowerPoint as Links? If
so, HOW? I also
wrote a table and chart exporter from Excel to PPT, but I
couldn't find a
way to PasteSpecial a chart as a link. Someone please
tell me if it's
impossible.

Bruce Cooley


"Matt Tessar" wrote in message
...
: Hello,
:
: I have a VB application that I have written that
generates
: charts and tables in Excel, and pastes them into
: PowerPoint. I have found that the largest performance
: bottleneck seems to be with the chart and with the table
: rendering in excel.
:
: Does anyone have any performance tips for automating
excel?
:
: Thanks
: Matt
:


.

Hello,

I have the screenupdating set to false already. I will
try setting the page break visibility.

My real bottlenecks seems to be with changing the
appearance of the chart via the API, and setting up the
table formatting for excel ranges that I paste into
powerpoint.

I already conquered the cell updating performance problem
by filling a range as an array. I will check my use of
the Active* properties. I suppose that when you make a
call to the Active* it might using late binding to make
the function calls which may explain some of my
performance issues.

Thanks,
Matt



-----Original Message-----
Matt,

standard bottlenecks appear to be screenupdating and
pagebreak
visibility.

BUT a lot depends on how you insert your data.. using
variables is
generally a lot faster then just working with the
selection and
activecell.

you can also fill an entire range from an array instead
of cell by cell
(that's the real winner)

I didn't time for charts but i suspect the ScreenUpdating
will be a
major factor.


timings to fill a range of 5000 cells

5000 5000 5000 5000 5000 4999
11,847 11,166 11,287 2,163 1,291 0,041


1: SELECT SU=TRUE, PB=TRUE
2: SELECT SU=TRUE, PB=FALSE
3: SELECT SU=FALSE, PB=TRUE
4: SELECT SU=FALSE, PB=FALSE
5: RNG SU=FALSE, PB=FALSE
6: ARRAY SU=FALSE, PB=FALSE

Code for 4, 5 and 6:

Application.ScreenUpdating = False
ActiveSheet.DisplayPageBreaks = False

m = 5000
t = Timer
[d1].Select
For r = 1 To m
ActiveCell = r
ActiveCell.Offset(1).Select
Next
Cells(r, 4) = Timer - t

t = Timer
For r = 1 To m
Cells(r, 5) = r
Next
Cells(r, 5) = Timer - t

t = Timer
ReDim arr(m) As Long
For r = 1 To m
arr(r) = r
Next
Cells(1, 6).Resize(r - 1) = WorksheetFunction.Transpose
(arr)
Cells(r, 6) = Timer - t

if there's a lot of data or a lot of calculations
then it may be worth checking out the efficiency of your
worksheetfunctions.


suc6.

if u want, end me your xls & ppt and i can have a look.
address below.


keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


"Matt Tessar" wrote:

Hello,

I have a VB application that I have written that
generates
charts and tables in Excel, and pastes them into
PowerPoint. I have found that the largest performance
bottleneck seems to be with the chart and with the
table
rendering in excel.

Does anyone have any performance tips for automating
excel?

Thanks
Matt



.