Beefy Boxes and Bandwidth Generously Provided by pair Networks
more useful options
 
PerlMonks  

Re^4: Perl r/w Excel with OLE

by Sinistral (Prior)
on Jun 29, 2012 at 16:26 UTC ( #979147=note: print w/ replies, xml ) Need Help??


in reply to Re^3: Perl r/w Excel with OLE
in thread Perl r/w Excel with OLE

As said in response to dasgar, these are good points. I am not the original author, but it seemed reasonable to include code provided by the authors of Win32::OLE. It's good to point out to future viewers the possible pitfalls of using such code, including the possibility of "hijacking" an existing Excel instance doing other things. As I said, my mental model of Win32::OLE automation has always been a dedicated single instance of Excel running without intervention (or possibility of intervention, in the case of a Server). If I were to do simple macro type automation, then I'd record it within Excel or use the existing VBA and save myself the pain of OLE.

As for this specific example, if you had an open file already loaded in the Excel instance, what would happen is that you'd get a new sheet added, there'd be data in that, and the result would be saved in test.xls. So if you had a complex workbook already open, those changes could be brought back. It's a fair point, though, you wouldn't want to have any open instances before running such a script.


Comment on Re^4: Perl r/w Excel with OLE
Re^5: Perl r/w Excel with OLE
by davies (Vicar) on Jun 29, 2012 at 16:52 UTC

    Unless there's something going on that I don't understand (in which case I'd love to know), it won't work as you describe. Consider

    # get a new workbook $book = $ex->Workbooks->Add;

    This creates a new book, i.e. a new file, in the Excel instance. Then, when the code saves test.xls, it's only that book that gets saved. No sheets are added. $sheet is created by the line

    $sheet = $book->Worksheets(1);

    Again, this is in $book, not in anything that might have been open in the same instance. This can't fail, as the creation of $book will have the default number of sheets from SheetsInNewWorkbook, which can never be less than 1.

    Now, I accept that the code given,

    # save and exit $book->SaveAs( 'test.xls' ); undef $book; undef $ex;

    doesn't actually close the instance of Excel, but merely undefines it in Perl. But this thread is, to a large extent, about closing instances of Excel, so my fear is undiminished.

    If you don't have any instances of Excel open, there's no point even trying to take control of an open instance. And if you do, we return to the dangers on which we agree. If the code creates its own instance without trying to take control of an existing instance, you are safe either way.

    Regards,

    John Davies

Re^5: Perl r/w Excel with OLE
by dasgar (Deacon) on Jun 29, 2012 at 18:21 UTC
    If I were to do simple macro type automation, then I'd record it within Excel or use the existing VBA and save myself the pain of OLE.

    I don't want to sound overcritical, but wanted to point out two potential problems with having Perl calling macros in Excel.

    First, Excel 2010 defaults to not enable macros. That means you'll probably have to add code to enable the execution of macros. Since I don't have Perl calling macros, I personally have not encountered this issue myself. That default behavior might be changeable, but I personally think that it's not a good idea to make assumptions about what settings are for a program like Excel when developing code and manipulates that program.

    The second problem is related to something actually happened at work. My first dive into learning Perl was to generate Excel charts. A coworker previously had a script that did this and his called macros. My coworkers were emphatically telling me to not use macros when they found out what I was working on. What happened was my coworker's template Excel file got corrupted with a virus. So when folks used his script, the macros was deleting files, which wreaked havoc on projects. Therefore, I made sure that my code was not using macros and was not relying on a template Excel file.

    Again, it's a matter of personal preference. However, I would strongly urge folks to not call macros from Perl, but rather convert the macros into Perl code for your Perl program to use. I realize that doing so means more work, but I personally feel the benefits of doing so are well worth it.

      No, you misunderstood my point. I didn't mean "use macros that are invoked by Perl using Win32::OLE". I meant "Use the macro recorder and VBA instead of using Perl". VBA and the macro recorder (which ultimately is a shiny wrapper on the underlying VBA) are the tools explicitly designed by Microsoft for automating Excel. It's just that with the power of Perl, you can do many other things that are difficult or impossible when using VBA.

      After Davies explained, I now see how reusing a single instance of Excel could lead to data loss. Perhaps the authors of Win32::OLE should be contacted so that the module examples have caveats about what it really means for the system when you reuse a running Excel instance.

        Hmmm....ok. Perhaps I misunderstood you when you said:

        If I were to do simple macro type automation, then I'd record it within Excel or use the existing VBA and save myself the pain of OLE.

        That sounded like you plan to use macros for your "automation". Whether you are manually opening Excel and running the macros or using Perl to call the macros, I personally believe that the issues that I pointed out are still relevant.

        Also, by using VBA instead of Perl does not mean you escape from OLE. Any VBA code (external to Excel or inside Excel as macros) that does any Excel automation will be using OLE. The Win32::OLE module lets you access the OLE API from Perl. In other words, if you're automating Excel with VBA, macros, or Perl, you're going to use OLE in some way as far as I know.

        If I'm still misunderstanding your point, my apologies.

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://979147]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others lurking in the Monastery: (12)
As of 2014-07-31 22:03 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    My favorite superfluous repetitious redundant duplicative phrase is:









    Results (253 votes), past polls