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;
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] |
use strict;
use warnings;
#use Storable;
#use Cwd;
use Win32::OLE;
#use Win32::OLE qw(in with);
#my $file = cwd.'\\'."myexcel.xlsm";
my $file = 'X:\Data\Perl\1131763\example.xls';
my $Excel = Win32::OLE->new("Excel.Application");
$Excel->{Visible} = 1;
my $workbook = $Excel->Workbooks->Open($file);
die "Failed to define Excel workbook" unless (defined $workbook);
system("attrib -r $file");
$Excel->{DisplayAlerts} = 0;
$workbook->Save;
My code is indented. The crucial lines are 13 & 14, but let's consider the rest (someone else may have a similar problem, so even if it's no use to you, it may be useful). Note that I have used code tags. It's much easier for everyone. Also, you have changed your original post without identifying what has changed. Changes are fine, but please mark them as such, otherwise people new to the thread may not understand replies made before the changes.
1: Never leave this out unless you REALLY know what you're doing.
3 & 4: I'm not using them, so they don't add to the solution.
6: You aren't using this in the code given. Unless you really need this functionality, it is best left out.
7 & 8: Just my way of doing it on my machine.
10: Use throughout development. For production code, it can be left out if you are sure you are closing all instances of Excel. Otherwise, you'll get orphaned instances eating memory (effectively a memory leak).
13: This clears the read-only attribute on the file (assuming that there are the necessary permissions and you aren't doing something improper on someone else's network).
14: This prevents the dialogue box you mention appearing. Note that there are two issues here: the read-only problem that you mentioned in your OP but not in the post I'm replying to and the problem of the dialogue box that you didn't mention in your unamended OP.
Regards,
John Davies | [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");
| [reply]
[d/l] |