minor but irritating problem when generating Excel files with Perl

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

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

"EJF" <[email protected]> wrote in message
news:[email protected]...
>
> 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
>
>
> --
> EJF
> ------------------------------------------------------------------------
> EJF's Profile:
http://www.excelforum.com/member.php...o&userid=32065
> View this thread: http://www.excelforum.com/showthread...hreadid=518842
>

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

$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;
}

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 [email protected] hinted at in his/her post.

-Peter


"EJF" <[email protected]> wrote in message
news:[email protected]...
>
> 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
>
>
> --
> EJF
> ------------------------------------------------------------------------
> EJF's Profile:
http://www.excelforum.com/member.php...o&userid=32065
> View this thread: http://www.excelforum.com/showthread...hreadid=518842
>

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...11898&lngWId=1

NickHK


"Peter" <peterguy -at- hotmail -dot- com> wrote in message
news:[email protected]...
> 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 [email protected] hinted at in his/her post.
>
> -Peter
>
>
> "EJF" <[email protected]> wrote in message
> news:[email protected]...
> >
> > 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
> >
> >
> > --
> > EJF
> > ------------------------------------------------------------------------
> > EJF's Profile:
> http://www.excelforum.com/member.php...o&userid=32065
> > View this thread:
http://www.excelforum.com/showthread...hreadid=518842
> >
>