Reading from Excel

[Hrvoje]

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();

or

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?

[vek]

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 --

[dimmesdale]

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.

[Hrvoje]

Thanks, but I've tried that, it's the same as Value()...

[dimmesdale]

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.

/update

[cacharbe]

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.

C-.

---
Flex the Geek

[migs]

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
migs

[cacharbe]

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.

C-.

---
Flex the Geek