minor but irritating problem when generating Excel files with Perl

[EJF]

This may not be an appropriate question for this forum, since it doesn't
concern VBA, but I didn't know where best to post it.

I generate a fair number of Excel files using Perl. I generate the file
with Perl, I open it for the first time in Excel, I make some changes in
Excel, and then when I go to close the file (or when I hit "save" to
save changes), a window pops up and says "save file as" (with the
correct name in that window) and the "file type" window is always set
as "text tab delimited". And then each time, I change the file type to
Excel Worksheet and then hit save, and I get the "a file with this name
already exists here - do you want to replace it?" message. And of course
the file that already exists there is the same file that I'm working on.
So basically, the first time that I open a file that I generate with
Perl, Excel thinks that I have opened a brand new blank document (text
tab delimited, for some reason) and just pasted in all this stuff and
now need to decide whether or not to save this new file. Is there
something I can do to get around this?

Thanks.

Eric

 

[Peter]

How are you generating the files?

If you're just creating a tab-delimited file with an xls extension, then
Excel will open it w/out asking the usual "you're opening a text file, how
is it formatted?" questions, but when you save it, Excel notices that you
are saving a text file as an Excel file, hence the prompts.

hth,

-Peter

 

[hfs2]

$wb->{'Saved'} = $OLE_TRUE; # nix the dialog
if(!($wb->SaveAs($file_afn_summary_report)))
{
my $oleMsg = Win32::OLE->LastError();
ErMsg::erMsg($0, $bstat_rev, __FILE__, __LINE__, 'ole message',
qq(Perl controller was unable save workbook\n).
qq("$file_afn_summary_report".\n) .
qq(BEGIN_OLE_MESSAGE-->\n$oleMsg\n<--END_OLE_MESSAGE));
last ef;
}

 

[EJF]

I generate the files just like I generate any other file using Perl
except that I use the .xls extension. For example, here I do exactly
the same thing with two files, except one of them (.txt - which I never
open in Excel) behaves well and the other one (.xls, which I *do*, of
course, open in Excel) behaves poorly:

open (OUTPUT1, ">test.xls");
open (OUTPUT2, ">test.txt");

print OUTPUT1 "hello";
print OUTPUT2 "hello";

close OUTPUT1;
close OUTPUT2;

I run this program and I then see two files that it's generated - an
Excel file and a file in a simple text editor. If I double click on the
simple text file, it opens in a simple editor (TextEdit, as it happens -
I'm a Mac user). I can then make changes and save it and everything is
normal - it recognizes that the file that I double clicked is the same
one that I'm working in. However, if I double click on the Excel file
and make changes, then when I go to save it

a) it doesn't recognize that the file that already exists in this
folder is exactly the same file as I'm working with - otherwise it
would never say "a file with this name already exists - do you want to
replace it with the current file?"; and
b) it seems to think that my default choice to save the file would be
as a .txt file, even though the file itself is an Excel Worksheet file.


It sounds like a trivial little thing, and it is basically trivial
except that sometimes I'll generate 100 Excel files and then with each
one of them I have to go through the same silly thing.

Thanks for the help.

Eric

 

[Peter]

Generating text files and renaming them with a .xls extension does not mean
that Excel will treat them as "true" Excel files. It's analogous to
renaming a text file with a .mp3 extension and expecting an mp3 player to
play it.

If you want to generate true Excel files with PERL, you'll have to do it
with OLE, as (e-mail address removed) hinted at in his/her post.

-Peter

 

[NickHK]

Eric,
You can write the BIFF8 format to create a true Excel file.
As long as you do not want too much fancy stuff, look at
http://www.Planet-Source-Code.com/vb/scripts/ShowCode.asp?txtCodeId=11898&lngWId=1

NickHK

Generating text files and renaming them with a .xls extension does not mean
that Excel will treat them as "true" Excel files. It's analogous to
renaming a text file with a .mp3 extension and expecting an mp3 player to
play it.

If you want to generate true Excel files with PERL, you'll have to do it
with OLE, as (e-mail address removed) hinted at in his/her post.

-Peter