Perl & Excel - how can I write and run Excel macros in Perl?

This page only explains how to run macros that are already exist in the sheet.

Hi Nivo

U can do precisely as CobolDotNet has suggested. I think, thats the ONLY way. For more clear understanding it would be helpful if u tell us what does that pivot macro will do.

Cheers

HI,

It soesn't work for me, maybe because I'm trying it from CGI?

I start the Excel like this:

   use Spreadsheet::WriteExcel;

    print "Content-type: application/vnd.ms-excel\n\n";

    my $workbook = Spreadsheet::WriteExcel->new("-");

Then I wrote:

    my $xlmodule = $worksheet->VBProject->VBComponents->Add(1); # vbext_ct_StdModule is the default

    #Make the module code
    my $Code = "sub MyMacro\n\tMsgBox(\"Inside generated macro!!!\nWith Perl No Less!!!!\"\nEnd Sub";

    #Put in the code
    $xlmodule->CodeModule->AddFromString = $Code;

      # Run the macro
      $Excel->Run('MyMacro');

A CGI context shouldn't have anything to do with it; using Perl for CGI scripts generally works fine.

Also, the module object should be created from the workbook object, not the worksheet.

I did make one error:
    $xlmodule->CodeModule->AddFromString = $Code; should read
    $xlmodule->CodeModule->AddFromString ($Code);

What errors, if any, are you getting?  Can you explain a little more what you are trying?  I assume that it is a web page since you mention CGI.  Lastly, can you post more of your script so that we can better see what is happening code wise (e.g. the macro being installed, setting up the Excel workbook, etc.)?

HI,

Here is the full script, actually the interesting lines appear in my previous post, but here it is.
The script is invoked from a hyperlink on a web page. After the Excel opens I open the VB Editor and I cannot find the macro.

# EXCEL SETTINGS
    use Spreadsheet::WriteExcel;

    print "Content-type: application/vnd.ms-excel\n\n";

    my $workbook = Spreadsheet::WriteExcel->new("-");

# Add a worksheet
    $worksheet = $workbook->addworksheet('Data');

# Add and define a format - heading
    $formathead = $workbook->addformat(); # Add a format
    $formathead->set_bold();
    $formathead->set_color('blue');
    $formathead->set_align('left');

# Add and define a format - aligned to left
    $formatleft = $workbook->addformat(); # Add format1
    $formatleft->set_align('left');

# Add and define a format - heading
    $formatbold = $workbook->addformat(); # Add a format
    $formatbold->set_bold();

$worksheet->set_column(3, 3,  10);

# END OF EXCEL SETTINGS    

#=========================================================
# Get records data from file
#=========================================================
   
          # Open app_data.txt file      
                  if (open(FILE,"$datafile"))
                  {
                        @ALL_RECORDS = <FILE>;
                        close(FILE);
                  }
                  else
                  {
                        $error="Failed to open file: $datafile";
                        &error_msg;
            }


#=========================================================
# Print table header
#=========================================================
 
  # Print the table header

    $count_col = 0;
    $count_row = 0;

      $HEADER[0] = "ID";
      $HEADER[1] = "User";
      $HEADER[2] = "Date";
      $HEADER[3] = "Status";
      $HEADER[4] = "Priority";
      $HEADER[5] = "Locked";
      $HEADER[6] = "Date Completed";
      $HEADER[8] = "Subject";
      $HEADER[7] = "Locked By";
      
    for ($j=0 ;$j<@HEADER; $j++)
    {
            $worksheet->write($count_row, $count_col, "$HEADER[$j]", $formathead);
            $count_col++;
    }

#=========================================================
# Print table content
#=========================================================

      for($r=0; $r<@ALL_RECORDS; $r++)
      {
            $count_row++;
            @FP=split(/\|/, $ALL_RECORDS[$r]);

            $worksheet->write($count_row, 0, "$FP[0]", $formatleft);
            $worksheet->write($count_row, 1, "$FP[1]", $formatleft);
            $worksheet->write($count_row, 2, "$FP[2]", $formatleft);
            $worksheet->write($count_row, 3, "$FP[3]", $formatleft);
            $worksheet->write($count_row, 4, "$FP[4]", $formatleft);
            $worksheet->write($count_row, 5, "$FP[5]", $formatleft);
            $worksheet->write($count_row, 6, "$FP[6]", $formatleft);
            $worksheet->write($count_row, 8, "$FP[7]", $formatleft);
            $worksheet->write($count_row, 7, "$FP[8]", $formatleft);
      }

### Here I try to write and run the macro

    $xlmodule = $workbook->VBProject->VBComponents->Add(1); # vbext_ct_StdModule is the default

#Make the module code
    $Code = "sub MyMacro\n\tMsgBox(\"Inside generated macro!!!\nWith Perl No Less!!!!\"\nEnd Sub";

#Put in the code
    $xlmodule->CodeModule->AddFromString($Code);

# Run the macro
  $worksheet->Run('MyMacro');

As far as I can tell, inserting in VBA modules is going to require Win32::OLE in Perl since you need the functionality from the VBA extensibility library.  Looking over the documentation in Spreadsheet::Excel, I don't see where that can be done.

As a crufty solution to not rewrite the entire script: Rather than direct to STDOUT with the "-" special name in the new constructor, pass a file name.  Let WriteExcel do its work.  Then call the close statement.

Now we have a file on the server, so we use OLE to do something like:
    my $Excel = Win32::OLE->new('Excel.Application', 'Quit');
    my $Book = $Excel->Workbooks->Open("C:\MyTempDir\MyExcelFile.xls");
    my $xlmodule = $Book->VBProject->VBComponents->Add(1);
    my $Code = "sub MyMacro\n\tMsgBox(\"Inside generated macro!!! With Perl No Less!!!!\"\nEnd Sub";
    $xlmodule->CodeModule->AddFromString($Code);
    $Book->Close;

That should be able to put in your macro.  Last we put it to the browser by putting the file in binmode, reading it to a buffer, printing the buffer to STDOUT (which will be the browser).

I gave up, it doesn't work for me.
I'll try figuring it later.

Thank you all for your effort.