Sign in to follow this  
Followers 0
CongTin

Problem with "_ExcelWriteFormula"

6 posts in this topic

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,

Share this post


Link to post
Share on other sites



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)

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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,

Share this post


Link to post
Share on other sites

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,

Share this post


Link to post
Share on other sites

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,

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now
Sign in to follow this  
Followers 0