Problem with "_ExcelWriteFormula"

[CongTin 0]

Dear all,

I am a Newbie,

Please support me in this issue.

My problem is:

I have a Excel file with this formula: =IF(LEFT(B2,1)="1",RIGHT(B2,6)&"/"&RIGHT(B4,19),"")

I type this formula to Excel directly, it can work excactly.

I would like to use AutoIT to put this formula to my Excel file but I cannot write the correct code.

I have tried with:

1./ _ExcelWriteCell($nmc,"=IF(LEFT(B2,1)="1",RIGHT(B2,6)&"/"&RIGHT(B4,19),"")","C2") (ERROR: syntax error)

2./ _ExcelWriteCell($nmc,"=IF(LEFT(B2,1)=""1"",RIGHT(B2,6)&""/""&RIGHT(B4,19),"")","C2") (Cannot work)

3./ _ExcelWriteFormula($nmc,"=IF(LEFT(B2,1)="1",RIGHT(B2,6)&"/"&RIGHT(B4,19),"")","C2") (ERROR: syntax error)

4./ _ExcelWriteFormula($nmc,"=IF(LEFT(B2,1)=""1"",RIGHT(B2,6)&""/""&RIGHT(B4,19),"")","C2") (Cannot work)

Please support me to figure out this one.

Thanks for your time,

Regards,

[l3ill 70]

Hi Congtin & Welcome,

your quotes seem to be causing the syntax error...besides that I tested your formula in 2007 and it is also giving me an error.

So, 2 problems:

• have another look at your formula
• use single quotes in your formula

example:

_ExcelWriteFormula($oExcel, "=IF(LEFT(B2,1)='1',RIGHT(B2,6)&'/'&RIGHT(B4,19),"")", 1, 2)

[picaxe 2]

This works on Excel2003

#include <Excel.au3>
Local $oExcel = _ExcelBookNew() ;Create new book, make it visible

_ExcelWriteCell($oExcel, 1234567, 2, 2) ; B2
_ExcelWriteCell($oExcel, 234567, 4, 2)  ; B4
_ExcelWriteFormula($oExcel, '=IF(LEFT(R2C2,1)="1",RIGHT(R2C2,6)/RIGHT(R4C2,19),"")', 2, 3) ; C2

[CongTin 0]

Hi Congtin & Welcome,

your quotes seem to be causing the syntax error...besides that I tested your formula in 2007 and it is also giving me an error.

So, 2 problems:

• have another look at your formula
• use single quotes in your formula

example:

_ExcelWriteFormula($oExcel, "=IF(LEFT(B2,1)='1',RIGHT(B2,6)&'/'&RIGHT(B4,19),"")", 1, 2)

Hi Billo,

Thank you very much for your support. Yes, I am using office 2007.

I will test it according to your advice with sigle quotes and inform you the result if it is ok.

Thank you again,

Warm regards,

[CongTin 0]

This works on Excel2003

#include <Excel.au3>
Local $oExcel = _ExcelBookNew() ;Create new book, make it visible

_ExcelWriteCell($oExcel, 1234567, 2, 2) ; B2
_ExcelWriteCell($oExcel, 234567, 4, 2)  ; B4
_ExcelWriteFormula($oExcel, '=IF(LEFT(R2C2,1)="1",RIGHT(R2C2,6)/RIGHT(R4C2,19),"")', 2, 3) ; C2

Dear Picaxe,

Thanks for your information.

My office is 2007 and maybe this is the reason of problem. I will try testing with Excel 2003.

Hope to continuously received your support in next time,

Warm regards,

[CongTin 0]

Hi Billo,

Thank you very much for your support. Yes, I am using office 2007.

I will test it according to your advice with sigle quotes and inform you the result if it is ok.

Thank you again,

Warm regards,

Dear all,

I test with this one ok.

_ExcelWriteCell($nmc,'=IF(LEFT(B2,1)="1",RIGHT(B2,6)&"/"&RIGHT(B4,19),"")', 2, 3)

So, We have to care to sigle quotes (') and double quotes(") in AutoIT.

Thank all for your attention.

Regards,