Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Delphi Excel Automation getting rid of .select for speed

Status
Not open for further replies.

Smithy1001

Programmer
Jun 9, 2006
8
GB
Hi,
I use delphi to manipulate excel and frequently format cells iteratively. I am told that using say range['A1'].select and then excel.selection.value:= etc is time consuming and the best way is to put just range['A1'].value:= as the select process takes time. So, I have the following code which I wish to rif of the .select

for i := 0 to NumCodesMPSDetail-1 do
begin
Num1 := (13*i)+10;
WS.range['B'+inttostr(Num1+1)+':M'+inttostr(Num1+1)].select;
Excel.Selection.FormatConditions.Delete;
Excel.Selection.FormatConditions.Add(XLCellValue,XLLess,'=b$'+inttostr(Num1-4));
Excel.Selection.FormatConditions[1].Interior.ColorIndex := 7;
Excel.Selection.FormatConditions.Add(XLCellValue,XLGreater,'=b$'+inttostr(Num1-3));
Excel.Selection.FormatConditions[2].Interior.ColorIndex := 3;
end;

I have put
for i := 0 to NumCodesMPSDetail-1 do
begin
Num1 := (13*i)+10;
RangeString := 'B'+inttostr(Num1+1)+':M'+inttostr(Num1+1);
Ranger := ws.range[rangestring];
ranger.FormatConditions.Delete;
ranger.FormatConditions.Add(XLCellValue,XLLess,'=b$'+inttostr(Num1-4)); //note put a but gives b...!
ranger.FormatConditions[1].Interior.ColorIndex := 7;
ranger.FormatConditions.Add(XLCellValue,XLGreater,'=b$'+inttostr(Num1-3));
ranger.FormatConditions[2].Interior.ColorIndex := 3;
end;

where ranger is a variant. This works of sorts. Where I have put '=b$', my formatcondition actually comes out as '=a$' within the cells in excel. So I have 2 questions:
1. Why does the column reference b get transposed to a when I get rid of the select?
2. How can I use the with statement instead of having to write ranger. each time. ie. with ranger do begin formatconditions.delete; etc end;
Thanks
 
Everything looks fine without running the actual code.

Are you early or late binding? Have you tried selecting the whole range of cells and then apply the formatting to them so you're down to a few 'select' calls?

Have you turned off screen updates in Excel while you're processing the document?
 
Hi,

I am late binding. I have not turned off screen updates but I will. I cannot select the whole range of cells as it's only every 13th row of 5000 rows that I want the 2 conditional formatting lines on, so it's row 10,23,36 etc (ie 10+x*i).
Also I cannot use the following scenario. I have even tried putting ws.pagesetup as a variant.
ws.pagesetup.centerheader := 'This works';
with ws.pagesetup do
begin
CenterHeader := 'This does not';
end;
I will put this as a separate thread.
Thanks
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top