ParseExcel picking up cell value from wrong worksheet 1

[TommyIndigo]

I am using Spreadsheet-ParseExcel, and it works well normally...however I am using it now with a rather complex Excel workbook that contains many macros and graphics. I can get the value for the correct cell, however it comes from the wrong worksheet. I played around with the target cells/worksheets. It usually the corresponding cell, but is 2 or 3 tabs away! If I use the EXACT SAME code to target a "dummy", simpler workbook I created, it works fine. Is there any workaround known for using ParseExcel with a complex workbook? Unfortunately, I don't have the flexibility of changing the source workbooks.

 

[travs69]

Do you have any code or examples you can post?

 

[TommyIndigo]

Thansk for the reply Trvs, however unfortunately the workbooks have sensitive info that would take a long time to scrub.

Maybe my question should have been more general: Are there any known issues with ParseExcel when features such as macros, charts or protection are used?

 

[TommyIndigo]

By the way, I'm using Excel 2003. Thanks!

 

[TommyIndigo]

I've poured through documentation and message boards, and still can't find an answer.

Would there be significant issues or performance hits if I used Win32::OLE to read my worksheets? I have Excel installed on the server.

 

[dmazzini]

There are some limitations with Spreadsheet:arseExcel
From Documentation:

TODO
- Spreadsheet:arseExcel : Password protected data, Formulas support, HyperLink support, Named Range support

- Spreadsheet:arseExcel::SaveParser : Catch up Spreadsheet::WriteExce feature, Create new Excel fle


To be able to read share protected excel files, you could use OLE.

Win32 OLE is so fast, it wont affect the performance.

use OLE;

# ###################################################################
# Unprotecting Excel File
# ###################################################################  

 sub unprotecting_excel_file{

    my $Excel = Win32::OLE->GetActiveObject('Excel.Application')
    || Win32::OLE->new('Excel.Application', 'Quit');

    $Excel->{DisplayAlerts}=0; 
    #$Excel->{Visible} = 1;
    my $Book = $Excel->Workbooks->Open("$filename_datafill"); 
    $Book->UnprotectSharing("Yourpassword");  
    $Book->Save;  
         
} 




 sub protecting_excel_file{
    
    my $Excel = Win32::OLE->GetActiveObject('Excel.Application')
    || Win32::OLE->new('Excel.Application', 'Quit');  
    $Excel->{DisplayAlerts}=0; 
    #$Excel->{Visible} = 1;
    my $Book = $Excel->Workbooks->Open("$filename_datafill");
    $Book->ProtectSharing({Filename=>$filename_datafill,SharingPassword=>"Yourpassword"});     
    $Book->Save;
     
 }

dmazzini
GSM System and Telecomm Consultant