Solved

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

Posted on 2004-10-12
9
3,274 Views
Last Modified: 2012-08-14
HI,

I have a perl script that generates an Excel sheet with data.
I wont to run a macro on that data, for example, a Pivot macro.
The problem is that the Excel sheet that I've just created doesn't include the macro.
Is there a way to write the macro from Perl and then run it?

Thanks,

Nivo
0
Comment
Question by:nivo_Z
9 Comments
 
LVL 3

Assisted Solution

by:alskdj80
alskdj80 earned 50 total points
Comment Utility
there might be some info in this website that might help you: http://perlmonks.org/?node_id=153486
0
 

Author Comment

by:nivo_Z
Comment Utility
This page only explains how to run macros that are already exist in the sheet.
0
 
LVL 1

Accepted Solution

by:
CobolDotNet earned 200 total points
Comment Utility
What a wonderful idea:  Writing code to write more code in another application!

What we want is to put a macro in an Excel workbook from a program.  We can do this by having the Perl code use OLE as alskdj80 pointed out.

I found this: http://support.microsoft.com/default.aspx?scid=kb;EN-US;303871  (VB.NET)
and this: http://support.microsoft.com/kb/194611 (VB 6)

Although that is for VB, the same applies to making the macro code in the target Excel workbook from perl.    The relevant lines from the second link above are this:
<quote>
       ' Add a module
       Dim xlmodule As Object 'VBComponent
       Set xlmodule = xlbook.VBProject.VBComponents.Add(1)  'vbext_ct_StdModule

       ' Add a macro to the module...
       Dim strCode As String
       strCode = _
          "sub MyMacro()" & vbCr & _
          "   msgbox ""Inside generated macro!!!"" " & vbCr & _
          "end sub"
       xlmodule.CodeModule.AddFromString strCode
<end quote>

The module object is referenced from Microsoft Visual Basic for Applications Extensibility Library (VBE6EXT.OLB).  So in our Perl code, we have to get the right reference, set up the module object, put the macro in a string, and then pass along the string to the module.  Sounds confusing enough.

[Aside: I ran the code in the VB6 link on a Win2K machine, and did not have to set the explicit reference to the Extensibility Library despite the instructions from the KB article.  Your results may vary.]

In Perl, it should run something similar to this:
    #! usr/bin/perl

    #get the perl modules
    use Win32::OLE qw(in with);
    use Win32::OLE::Const 'Microsoft Excel';

    #Make the objects
    my $Excel = Win32::OLE->new('Excel.Application', 'Quit');
    my $Book = $Excel->Workbooks->Add();
    my $xlmodule = $Book->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;

Hope that helps.

0
 
LVL 4

Expert Comment

by:vi_srikanth
Comment Utility
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
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:nivo_Z
Comment Utility
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');
0
 
LVL 1

Expert Comment

by:CobolDotNet
Comment Utility
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.)?
0
 

Author Comment

by:nivo_Z
Comment Utility
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');
0
 
LVL 1

Expert Comment

by:CobolDotNet
Comment Utility
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).
0
 

Author Comment

by:nivo_Z
Comment Utility
I gave up, it doesn't work for me.
I'll try figuring it later.

Thank you all for your effort.
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
perl exit code 2 243
Perl efficient DB Call 8 75
ppm conversion to curl on a module install 8 73
Writing a parser for java language 4 59
A year or so back I was asked to have a play with MongoDB; within half an hour I had downloaded (http://www.mongodb.org/downloads),  installed and started the daemon, and had a console window open. After an hour or two of playing at the command …
There are many situations when we need to display the data in sorted order. For example: Student details by name or by rank or by total marks etc. If you are working on data driven based projects then you will use sorting techniques very frequently.…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now