Forcing calculation through automation

[Nirmal Singh]

I have the following code:

xlSheet.Range['AJ1', EmptyParam].Value:='Days Lost';

xlSheet.Range['AJ2',EmptyParam].Formula:='=NETWORKDAYS(AH2,AI2,BankHols!$A$2:$A$500)';

xlSheet.Range['AJ2',EmptyParam].AutoFill(xlSheet.Range['AJ2:AJ'+IntToStr(kount),EmptyParam],xlFillCopy);

The third line above is very quick to execute but Excel then takes a
long time to do the calculation. How can I make sure that the entire
worksheet has been calculated before I move onto the next step?

Nirmal

 

[Guest]

Couldn't you use Application.Calculate to do this?

 

[Nirmal Singh]

Couldn't you use Application.Calculate to do this?

That doesn't seem to work either. I've tried to use this code:

xlSheet.Range['AJ1', EmptyParam].Value:='Days Lost';
xlSheet.Range['AJ2',EmptyParam].Formula:='=NETWORKDAYS(AH2,AI2,BankHols!$A$2:$A$500)';
xlSheet.Range['AJ2',EmptyParam].AutoFill(xlSheet.Range['AJ2:AJ'+IntToStr(kount),EmptyParam],xlFillCopy);
xlApp.Calculate;

xlSheet.Range['AJ2:AJ'+IntToStr(kount),EmptyParam].SpecialCells(xlFormulas,xlErrors).Value:=0;
xlSheet.Columns.Item['AJ:AJ', EmptyParam].Copy;
xlSheet.Columns.Item['AJ:AJ', EmptyParam].PasteSpecial(xlValues,xlPasteSpecialOperationNone, False, False);

This should calculate the NetWorkDays in column AJ, do a full calcualation and then copy and paste the values
into column AJ. However, when it has finished column AJ contains all zeros.


Nirmal