Have you tried the usual things to speed up code working on sheet ranges
such as turning off automatic calculation:

Application.Calculation = xlManual

'run all the sheet code here

ActiveSheet.Calculate

Application.Calculation = xlAutomatic

Would it make a difference just to copy values and do the formatting at the
end when all ranges are filled, so the formatting
can be done on larger ranges?

Not sure if you are doing this already, but could you manipulate Excel more
directly with the Excel API?
This is one for the SDK forum.

Can't think of anything else now, but I am not much into sheet work and I am
sure somebody will come up with some better ideas.


RBS

"Tas" wrote in message
...
Yes it is a C++ "approach"... (I cannot use VB in this project ...) and I
couldn't find more information about this. I could't find also a more
"active" forum for c++ for this that is why I posted it here... The
purpose
is to find a fast way for formating cells with color and Border without
each
time getting the Interior and the Border of the cell (as I saw this is
time
consuming). Imagine that you have a "page" of an normal "report" (A4) with
rows and columns (lets say 50 rows and 10 columns). Some cells from this
page
must pe formated with a special color to notify the user about the
"importance" of the content on that cell. And imagine that you can "paste"
the formated "block" in an excel sheed (not one per sheet but let say
10000
per sheet, one after another). How can you do this faster? As I searched
over
the doc and the forums I have come up with this solution which is less
time
consumig: To have only once define this "Formated" block and then using
the
Copy method I'm modifying the format of all my other "ranges" (which
allways
will have the same size (but different positioning in the sheet)) and the
fill the range with the data from an Array (wich is faster than filling
each
cell). I hope you understand what I want to do...

Regards,
T.
"RB Smissaert" wrote:

Is this C++? I don't know enough (nearly nil) of that to help.
Maybe you could post in the microsoft.public.excel.sdk group, although it
seems a very quiet group.
Or maybe you could explain what the purpose of the whole exercise is and
there might be a better way
to do it.

RBS

"Tas" wrote in message
...
Excel_generator is the main class that will initialize the COM
conecxion.
I have a "sheet" member which at the calling of the following method
will
be
available and pointing to the current active sheet.

void Excel_generator::add_some_data
( int staring_row,
int no_blocks,
int block_row_length,
int block_col_length
)
{
for (int i=0;i<no_blocks;i++)
{
Excel_block blk
(block_row_length,
block_col_length,
Excel_border::THICK,
Excel_coordinate((staring_row+i-1)*block_row_length*2,1));

for (int j=0;j<block_row_length;j++)
{
for (int k=0;k<block_col_length;k++)
{
blk.add_content(j, k, L" Something");
}
}

blk.write_to_sheet(sheet);
}
}

My Excel_block class has as variables(among others) one COleSafeArray
content_array which will store the "data" and a static Range object
generated
only once in the actual sheet and than copied by the write_to_sheet
method:
void Excel_block::write_to_sheet(CWorksheet &sheet)
{
Excel_coordinate end_coordinate =
Excel_coordinate(start_coordinate.get_row()+get_ro ws()-1,
start_coordinate.get_col()+get_columns()-1);

Excel_coordinate start_coordinate2 =
Excel_coordinate(start_coordinate.get_row()+2*(get _rows()-1)-1,
start_coordinate.get_col());

char *range1=new char[10];
start_coordinate.get_excel_coordinate(range1);
char *range2=new char[10];
end_coordinate.get_excel_coordinate(range2);

CRange range = sheet.get_Range(COleVariant(range1),
COleVariant(range2));

if (block_format)//this is my static range object which is already
generated
{
VARIANT copyTo;
V_VT(©To) = VT_DISPATCH;
V_DISPATCH(©To) = range;
block_format.Copy(copyTo); // Here I copy the block to my range!!
}

range.put_Value2(COleVariant(content_array));//and then put my values
content_array.Detach();

delete range1;
delete range2;
}

"RB Smissaert" wrote:

Post the code and we can see if it could be done faster.

RBS

"Tas" wrote in message
...
Hello there,

I have some questions regarding this Excel Automation (I'm not an
expert
on
this so please "excuse" if the questions are too trivial)...

I try to build a small application that will generate a very large
number
of
columns and rows into Excel sheets (lets say about 200 sheets each
one
with
50 000 rows and 100 columns). There is a way to store "Cell" objects
in
arrays so I could lets say "create" them into this array and than
add
the
array to a specific range (similar to the "Text", double, short
storage
into
an array...)? Why I'm asking this is because my Cells must have a
specific
format (they are somehow structured as "BLOCKS" and this block will
repeat
itself for many times) and I need a fast way to "copy" this
"block"...
I
already did a sample where I'm generatig arround 10 000 "blocks" on
a
sheet,
each block containing 3 rows and 15 colums, having each cell with a
different
color and a random border. The time until it was finished was
arround
31
seconds... Can I do this faster? Any ideas are more than wellcome.

Thanks,
Tas