in reply to save read-only Excel

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

Replies are listed 'Best First'.
Re^2: save read-only Excel
by Athanasius (Archbishop) on Jun 23, 2015 at 13:57 UTC
[reply]
Re^2: save read-only Excel
by Anonymous Monk on Jun 23, 2015 at 14:42 UTC
    system("attrib -r filename.csv");

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

[reply]
[d/l]
Re^2: save read-only Excel
by ShashankSC (Initiate) on Jun 23, 2015 at 14:16 UTC
    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]
        Hi John, here is my code: 
        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 prompt message: "A file named 'myexcel.xlsm' already exists in this location. Do you want to replace it? " The above should work irrespective of the Excel file 'myexcel.xlsm' being RO or RW. Thanks
[reply]
[d/l]

      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]

In Section Seekers of Perl Wisdom