Beefy Boxes and Bandwidth Generously Provided by pair Networks
Problems? Is your data what you think it is?

Reading from Excel

by Hrvoje (Initiate)
on Aug 09, 2002 at 13:52 UTC ( #188906=perlquestion: print w/replies, xml ) Need Help??

Hrvoje has asked for the wisdom of the Perl Monks concerning the following question:

Hi, I can't get some values from some cells containing formulas, I get #NAME! or #VALUE! instead. Now, this wouldn't be a problem if I knew that those formulas don't work, but they do! I know that because I can read values representing formula results from other cells just fine (some of them containing more complicated formulas than the ones in question). Also, if I open Excel manually and look into those cells, I see "normal" values (numbers).

I'm using Win32::OLE, and the code goes something like this:
my $value = $MYWORKSHEET->Range("B35")->Value();

my $value = $MYWORKSHEET->Range("B35")->Text();

This excel is linked to another file, and I get that by setting
$MYWORKBOOK->{UpdateLinks} = 3; # 3 means update links always
(both excel files are opened anyway)

Am I missing something?

Replies are listed 'Best First'.
Re: Reading from Excel
by dimmesdale (Friar) on Aug 09, 2002 at 14:28 UTC
    my $value = $MYWORKSHEET->Range("B35")->Value();

    Hmm... I'm pretty sure that value isn't a method, but a hash. Try saying:

    my $value = $MYWORKSHEET->Range("B35")->{Value};

    If this doesn't work, maybe some more information about the problem is needed.

      Thanks, but I've tried that, it's the same as Value()...
        Perhaps include some more code then. For example, how do you define $MYWORKSHEET; what's the call to Win32::OLE look like (are you opening an existing application), etc. And of course, maybe some of the spreadsheet (a reasonable amount) might be helpful. I've done some things with this before, and know the $sheet->Range("range")->{Value} is the correct "template" for getting at a value. Maybe you have the wrong sheet that you are accessing.

        Have you debugged any to narrow the problem down?

        update Maybe you should check out Win32::OLE Tips and Tricks with Excel.


Re: Reading from Excel
by vek (Prior) on Aug 09, 2002 at 18:15 UTC
    If you're not locked into using Win32::OLE you could always try peeking at Spreadsheet::ParseExcel. I've successfully used it in the past.

    -- vek --
Re: Reading from Excel
by cacharbe (Curate) on Aug 09, 2002 at 18:34 UTC
    As I mentioned deeper in the response tree, you might need to calculate the page, especially if you have DisplayAlerts = 0 or the cell references data from another sheet.
    use strict; use Win32::OLE qw(in with); use Win32::OLE::Const 'Microsoft Excel'; my $Excel = Win32::OLE->GetActiveObject('Excel.Application') || Win32::OLE->new('Excel.Application', 'Quit'); $Excel->{DisplayAlerts}=0; my $Book = $Excel->Workbooks->Open($excelfile); my $Sheet = $Book->Worksheets("Sheet1"); $Sheet->Activate(); $Sheet->Calculate(); #The rest of your GET code here
    It's happened to me before.


    Flex the Geek

      Hi -

      I am working with hrvoje on this. Our spreadsheet does need enabling of macros. If we run the spreadsheet from MS Excel on desktop, it asks for enabling of macros in a pop-up. Is there any way that all macros in the spreadsheet can be enabled to run programmatically ??

      Thanks and regards

        You'll need set the security settings in the application to "Low" so that they will run on the machine that your script is running. You can't do this programmatically that I have found, but that just makes sense (for obvious reasons).

        You can do this by going to the toolbar and following: Tools->Marco->Security...

        Update and Caveat: This isn't a safe mode to run Excel if this machine is used for anything other than application level access to Excel. You should always implement a strong anti-virus scheme and limit access to machines that need to run in this mode. Audit early, sudit often.


        Flex the Geek

Log In?

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: perlquestion [id://188906]
Approved by vek
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others surveying the Monastery: (5)
As of 2021-11-29 00:21 GMT
Find Nodes?
    Voting Booth?

    No recent polls found