ShashankSC has asked for the wisdom of the Perl Monks concerning the following question:

Hi Monks, how do I save a read-only Excel file? When the command 'Save' is encountered, it prompts a 'Save as a different file' message. I am using Win32::OLE. Update: This is the code I am using 
use warnings ;
use Storable ;
use Cwd;
use Win32::OLE ;
use Win32::OLE  qw(in with) ;

my $file = cwd.'\\'."myexcel.xlsm" ;
my $Excel = Win32::OLE->new("Excel.Application") ;
my $workbook = $Excel->Workbooks->Open($file) ;
die "Failed to define Excel workbook" unless (defined $workbook) ;

$workbook->Save;

[download]
Pop-up message prompt: A file named 'myexcel.xlsm' already exists in this location. Do you want to replace it?

Replies are listed 'Best First'.
Re: save read-only Excel
by stevieb (Canon) on Jun 23, 2015 at 13:45 UTC

    Welcome to PerlMonks!

    It's customary when asking a question here to post the snippet of code with enough context to describe the problem.

    In this case, it seems as though this is more a Windows problem, as when you try to overwrite a read-only file in Windows without using Perl, you'll get the same prompt.

    EDIT: Thanks Athanasius for pointing out that chmod() doesn't do what I thought it did on Windows (although it did reset the ro bit on a shared drive I was testing on). I then tried with Win32::File::SetAttributes() to no avail. However, I did finally come across a way.

    # unset the RO bit on a file
system("attrib -r filename.csv");

#... do writing to file

# change file back to RO
system("attrib +r filename.csv");

    [download]

    I have serious doubts if that will have any effect if it's NTFS permissions preventing writing though...

    If Win32::OLE doesn't have the capability to change a file from read-only, you can use the built-in chmod() function:

    chmod 0777, $file;
# do stuff that writes to file

    [download]

    The 0777 mask will make the file read, write and executable (r, w, x) by anyone who has access to the system. 0755 will make it r/w/x the owner (which might not be you), and only readable for everyone else.

    -stevieb

[reply]
[d/l]
[select]
[reply]
      system("attrib -r filename.csv");

      If the filename contains spaces or other interesting characters, see e.g. Win32::ShellQuote.

[reply]
[d/l]
      Thanks stevieb, but the use of chmod is not making any difference. the problem still persists. Cant I change my Excel file into a read-write file during 'Open' operation itself?
[reply]

        I'm almost certain you're barking up the wrong tree, and that the file is RW. But until I see your code, I can't be sure as there are dozens of possible explanations. I'm not going to try to guess which one it is only to be told each time it "is not making any difference". I'd be very surprised (and very interested) if I couldn't solve this for you, but I need to see the code and the failure. If you can post a SSCCE (http://sscce.org/ https://web.archive.org/web/20160926072757/http://sscce.org), you will probably have a solution very quickly.

        Regards,

        John Davies

[reply]

        I updated my original post. You can try the following (note that I doubt it'll work if it's NTFS permissions preventing you from writing though):

        # unset the RO bit on a file

system("attrib -r filename.csv");

#... do writing to file

# change file back to RO

system("attrib +r filename.csv");

        [download]
[reply]
[d/l]
Re: save read-only Excel
by Your Mother (Archbishop) on Jun 27, 2015 at 01:59 UTC

    Maybe something like this is all you need now?

    my $file = cwd.'\\'."myexcel.xlsm" ;
unlink $file if -e $file;
die "$file already exists!\n" if -e $file;

    [download]
[reply]
[d/l]
Re: save read-only Excel
by ShashankSC (Initiate) on Jun 24, 2015 at 08:19 UTC
    Kindly find my updated query. Thanks
[reply]
Re: save read-only Excel
by locked_user sundialsvc4 (Abbot) on Jun 23, 2015 at 18:37 UTC

    What am I missing here?   If the Excel file is “read only,” then naturally Windows will not allow it to be replaced.   It will insist that the file must be saved under a different name.   And, yes, it just might “pop up a user prompt” in that case.   Your program, therefore (and regardless of language) must detect that the file is read-only ... OLE will tell you ... and “save as” in that case, thereby avoiding any prompt.

Back to Seekers of Perl Wisdom