How to open an existing excel file using Win32::Ole 3

[dmazzini]

Hey experts

I am trying to open an excel file using the Win32::Module, unfortunetaly I was unable to do it.

I have been able to create new excel files, write on it and open it..It works ok.

The main problem happens when I want to open an existing excel file.

I have been using this module for opening XML files using the IE, it worked fine...I have attached the example:

sub open_ie {

$ie = CreateObject OLE "InternetExplorer.Application.1" ||
die "CreateObject: $!";
$ie->{Visible} = 1;
$ie->Navigate($filename_created);

}


I want to do the same opening an excel file in Microsoft Excel. I have the excel file in a variable called $excel_filename.

Many thanks for your prompt answer.

Regards

 

[greadey]

Well from your given example, I can't see what your problem is.

What format is your filename in?

What errors (if any do you get?)

 

[dmazzini]

Hi

That's the case:

File: Excel File

Problem: I want to open an existing File (Excel File), saved in my PC, using Win32::Ole Perl Module.

The case is that:

"I can open new excel files", but I can't open it if is was already created.

It is the code:


use Win32::OLE;
$file = shift || "C:\\temp\\MyTest.xls";
$excel = Win32::OLE->GetActiveObject('Excel.Application');

unless($excel)
{
$excel = new Win32::OLE('Excel.Application', \&QuitApp)
or die "Could not create Excel Application object";
}

$excel->{Visible} = 1;
$excel->{SheetsInNewWorkBook} = 1;
$workbook = $excel->Workbooks->Add();



sub QuitApp
{
my ($object) = @_;
$object->Quit();
}




SOMEBODY HELP ME!

 

[sloppyhack]

Did you ever get a resolution on this???? I'm having the same problem. I can open and write to existing files using a different module but it doesn't keep the file intact. Let me know.

Thanks

Cheers,

Sloppyhack

 

[sloppyhack]

I figured it out. Here's an example.......


$filename = 'c:\SupplierItemInquiryTemplate.xls';
my $book = $Excel->Workbooks->Open($filename);

If you want to use a path without the drive designation....this format works.

$filename = "\\\\ghx-fswest\\Content\\Rekon\\ProviderItemMasterData\\SupplierItemInquiryTemplate.xls";

Cheers,

Sloppyhack

 

[dmazzini]

sloppyhack!

Unfortunetaly I didn't fix it!

I was looking in the network some examples but any of them works good.

Cheers!

I hope you'll get the solution!

 

[sloppyhack]

Check out my next post. I found the solution. Works great. I have an Excel file with LOTS of formating. I am able to open it and add data really easily.

Cheers,

Sloppyhack

 

[dmazzini]

Sloppyhack

You are good mate!

Just let me know how you did it as soon as you can. I would like to implement it in some scripts that I did in the past where the result are excel files.
Thus I could show the results...

Cheers and thanks!

 

[dmazzini]

Regarding to:
sloppyhack (TechnicalUser) May 21, 2004
Check out my next post. I found the solution. Works great. I have an Excel file with LOTS of formating. I am able to open it and add data really easily.
Cheers,

Sloppyhack


Hi Sloppyhack

Kindly, Could you tell me how do you open an existing excel file?

Cheers!

 

[terryISO]

Hi.

As sloppyhack never responded, I thought you might like the following example, which opens a Word document, then saves it in text format. I'm not sure from your posts if you have installed Win32-ole module, but you will need to do so first.

Call it from your main routine like this:


use Win32::OLE;
&convert_to_text("C:/myfolder/myfilename.doc","C:/someotherfolder/otherfilename.txt");

# [other stuff]
# --- end of main ---

sub covert_to_text{
my $fileinname=$_[0];
my $fileoutname=$_[1];

# now open word if not running already,
# or else start it from fresh. Use "Quit" to terminate
my $Word = Win32::OLE->GetActiveObject('Word.Application') || Win32::OLE->new('Word.Application', 'Quit');

my($doc) = $Word->Documents->Open($fileinname) || die("Unable to open document ", Win32::OLE->LastError());
print "wdFormatText = wdFormatText\n" , wdFormatDOSTextLineBreaks , "\n";
$Word->Documents->FileName($fileoutname);
$Word->Documents->FileFormat(1);
$Word->Documents->AddToRecentFiles(0);
$Word->Documents->SaveAs({FileName->$fileoutname,});
$Word->Documents->Close;
$Word->Documents->Quit;
}

 

[dmazzini]

I have installed Win32 Module..I'll test it and try to test with excel.

Cheers

 

[terryISO]

Sorry, I gave you a Word example by mistake. It's a similar process using Excel.
To open an Excel file, try using this in your main:

use Win32::OLE qw(in with);
use Win32::OLE::Const 'Microsoft Excel';

&open_excel("C:/myfolder/myfilename.doc");

# [other stuff]
# --- end of main ---


sub open_excel{

my $filename=$_[0];

$Win32::OLE::Warn = 3; # die on errors...
my $Excel = Win32::OLE->GetActiveObject('Excel.Application') || Win32::OLE->new('Excel.Application', 'Quit');
# If Excel is already active, use that, or else start Excel. Use "Quit" to terminate

my $Book = $Excel->Workbooks->Open($filename); # open Excel file
my $Sheet = $Book->Worksheets(1); # select worksheet number 1

}

 

[dmazzini]

A long time ago I was trying to open (shows) an existing excel file and unfortunetaly I was not succesfull. About a month ago, I was playing again with the win32 module an I found how to do it. Here the code..

#!/opt/nokianms/bin/perl
# Finally I have got how to open a excel file from Perl
# A good one !

$excel_filename="C:\\Documents and Settings\\dmazzini\\Desktop\\Today\\NY_32K_DATA_DEC13.xls";

open_excel();

sub open_excel {

use Win32::OLE;
use Win32::OLE qw(in with);
use Win32::OLE::Variant;
use Win32::OLE::Const 'Microsoft Excel';
$Excel = Win32::OLE->GetActiveObject('Excel.Application') ||
Win32::OLE->new('Excel.Application');
$Excel->{'Visible'} = 1; #0 is hidden, 1 is visible
$Excel->{DisplayAlerts}=0; #0 is hide alerts

$Book_Data = $Excel->Workbooks->Open($excel_filename); # open Excel file
undef $Book_Data;
undef $Excel;

}

 

[missbarbell]

I am really surprised no-one has pointed you at the following great modules:

1) Spreadsheet:arseExcel
2) Spreadsheet::TieExcel
3) Spreadsheet::WriteExcel

These are much easier to use than relying on the Win32::OLE module. The latter is actually used by Vodafone to create Excel binaries on Linux boxes!

Barbie
Leader of Birmingham Perl Mongers

http://birmingham.pm.org

 

[terryISO]

I use the WriteExcel module, but there is a distinct disadvantage. It writes in Excel 97 format. If you use the Win32::OLE module, it writes using the version that is installed on your system.

Later versions of Excel have features that you may want toi use that are not available using WriteExcel.

I have to say that Write Excel is very good within its limitations.

But, the Win32:OLE module is very easy to use, also. It can't be used if you don't have Excel installed, whereas you can use WriteExcel in its own right. Horses for courses...

Also the person who started this thread, a year and a day ago, did specifically ask about Win32::OLE.

 

[missbarbell]

The original poster was also the one that posted today too ;-)

Just because someone mentions they are having trouble with a particular module, doesn't mean they can't be told another way. Especially if it gives them another avenue to think about in the future.

The code he posted today works for him, and thats great, but if you wanted a more cross-platform solution, then Win32::OLE is not the way to go. I read Excel files on Linux using Spreadsheet:arseExcel, without any need for a Windows environment.

Barbie
Leader of Birmingham Perl Mongers

http://birmingham.pm.org

 

[PaulTEG]

Barbie,

good point... * 4 that

--Paul

cigless ...

 

[dmazzini]

Hey guys!

I have been working with SpreadSheet:arseExcel, SpreadSheet::WriteExcel and Win32::OLE the last year.

Some comments comparing Spreadsheet vs Win32::OLE.

My point of view:

What I Like from Spreadsheet::WriteExcel and Spreadsheet :arseexcel

1. Spreadsheet::WriteExcel and Spreadsheet :arseexcel are very powerful if you want to manipulate format of cells, colours, fonts, reports. The module is very well documented and there are good examples. In the net you will get very good examples

2. Working with, Spreadsheet::WriteExcel and Spreadsheet ParseExcel, You don't need to be working in a WIN32 environment to use it, I was able to read excel file in UNIX HP System., also produce excel files, btw with very nice format.It works great. I loved it.


What I don't like from Spreadsheet:arseExcel:

1.If you work with excel files that contains a lot of colours, diferent fonts or big data, it is slow, Take too much time analyzing the excel file.

2.If you have password protected excel files, like Workbook protected or shared workbook and protected, you need to pass the password during execution time, otherwise you won't be able to read the data. Spreadsheet:arseExcel doesn't support it.

So in this case you have to simulate excel vba code using win32::OLe. Like this:

# ###################################################################
# 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("password");
$Book->Save;

}

After that protecting it again:

# ###################################################################
# Protecting Excel File
# ###################################################################

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=>"password"});
$Book->Save;

}

3. Spreadsheet:arseExcel doesn't support formulas.

4. Spreadsheet:arseExcel doesn't support range functions.

for instance, use win32 module you can get the range from the excel file at once:

# Find Last Column and Row

my $LastRow = $Sheet_cnumber->UsedRange->Find({What=>"*",
SearchDirection=>xlPrevious,
SearchOrder=>xlByRows})->{Row};

my $LastCol = $Sheet_cnumber->UsedRange->Find({What=>"*",
SearchDirection=>xlPrevious,
SearchOrder=>xlByColumns})->{Column};

# Range Data

$firstcell="A".2;
$lastcell= "IV".$LastRow; #(Last Column Excel File. Last Row with Data)

my $array = $Sheet_cnumber->Range("$firstcell:$lastcell")->{'Value'};

get_data($array);

sub get_data{
my($array)= @_;
foreach my $ref_array (@$array) {
$var1_col1= @$ref_array[0];
$var_col2=@$ref_array[1];
}
}


Using Spreadsheet:arseExcel you could use:

my $workbook = Spreadsheet:arseExcel::Simple->read('myfile.xls');
my $worksheet = ($workbook->sheets())[0];

while ($worksheet->has_data()) {
my @fields_table = $worksheet->next_row();
@fields_table= trim(@fields_table);
$var_col1="$fields_table[0]";
$var_col2= "$fields_table[1]";
} # Closing While

But I seems that win32 module does it faster than the other one...Maybe the communication with excel???

To be honest, both modules are great. The authors did a very good job.

Anyway, how do you show up an existing excel file using Spreadsheets Modules? I don;t know if it is possible...I think is a limitation. I am able to open a new excel using writeexcel spreadsheet..but no existing ones...

Anyway, I am feel very well working with OLE or Spreadsheet, just depends what I need to do I choose one of them.....

Thanks for your great answers!












dmazzini
GSM System and Telecomm Consultant

 

[missbarbell]

dmazzini, you point out some very good reasons for and against, for which you get a *.

However, the Spreadsheet modules were never written to complement the full Excel experience. If you wanted to be able to read fomulas, then you either use Excel, or rewrite the VBA engine (which isn't really an option). They were written to be cross-platform, and for the majority of uses they can cope with most .xls files.

As for handling ranges, perhaps that might be my next module patch

Barbie
Leader of Birmingham Perl Mongers

http://birmingham.pm.org

 

[kiperez]

missbarbell. I can imagine many instances where a cross-platform environment would wish to not just create, but also to open an existing spreadsheet (to preserve those formulas, of course), modify some cell values and save it off to a different filename. as of yet, there is only one way to do this that I can decipher and that is with the Win32::OLE module. I'd flip my code over in heart beat to those obviously beloved platform-independent modules you suggest so vehemently (ParseExcel and WriteExcel), if only they'd provide for this basic and all too often necessity.

Yours truly,
Patiently awaiting a version of WriteExcel that can actually save off a copy of an existing XLS file.