1

I try to insert a formula =IF(D2="";"";COUNTIF(D:D;D2)) in an Excel worksheet. Here is the code I'm trying:

$WorkSheet->Cells($Row2Use,'L')->{Formula} = "=IF(D".$Row2Use."=\"\",\"\",COUNTIF(D:D,D".$Row2Use.")"; # =IF(D2="";"";COUNTIF(D:D;D2))

I also tried using {Value} instead of {Formula} without success! What did I wrong

Laurent Zotto
  • 37
  • 1
  • 7
  • 1
    Possible duplicate of [List of Perl commands to manipulate Windows Excel with Win32::OLE module](https://stackoverflow.com/questions/27613878/list-of-perl-commands-to-manipulate-windows-excel-with-win32ole-module) – xxfelixxx Oct 20 '17 at 23:37
  • Have a look here: https://stackoverflow.com/questions/27613878/list-of-perl-commands-to-manipulate-windows-excel-with-win32ole-module – xxfelixxx Oct 20 '17 at 23:37
  • Yes, could be, and of course I saw and read this too. So th emethod I use with `{Formula}` is right, but whi is my cell empty at the end and I don't see an error like syntax error popping up! – Laurent Zotto Oct 21 '17 at 05:23
  • or mutst i Use `Range` instead of `Cell`? – Laurent Zotto Oct 21 '17 at 05:24
  • I am really not sure...I don't have a windows machine to try this out on, sorry. – xxfelixxx Oct 21 '17 at 06:23

1 Answers1

0

This following works for me. Code taken and modified from the link given by xxfelixxx.

use Cwd 'abs_path';
use Win32::OLE;
use Win32::OLE qw(in with);
use Win32::OLE::Const "Microsoft Excel";
$Win32::OLE::Warn = 3;


my $Excel = Win32::OLE->GetActiveObject('Excel.Application') || Win32::OLE->new('Excel.Application', 'Quit');

$Excel->{Visible} = 1;

my $Book = $Excel->Workbooks->Add;
my $Sheet = $Book->Activesheet;

$Sheet->Range("A1")->{Value} = 1;
$Sheet->Range("A2")->{Value} = 1;
$Sheet->Range("A3")->{Formula} = "=SUMA(R[-2]C:R[-1]C)";
jira
  • 3,890
  • 3
  • 22
  • 32
  • Ok, but this does not follow what was given: or use `Formula` with `A1`...`An` notation or use `FormulaR1C1` with relative positions of the cells – Laurent Zotto Oct 21 '17 at 10:08
  • I tried $Sheet->Range("D3")->{Formula} = '=IF(D2="";"";COUNTIF(D:D;D2))'; - seems to work – jira Oct 21 '17 at 10:21
  • If I use Cells instead of Range I get an exception. – jira Oct 21 '17 at 10:32
  • ... reading carefully your post, I realized I missed a ")" at the end of the formula. I would expect to get an error code here... – Laurent Zotto Oct 23 '17 at 15:35