Add all cwd directories to an array, iterate directory by directory and open-save only .xlsm files in there (Windows 10)

Question

Goal: Add all directories to an array, iterate directory by directory and open-save only .xlsm files in there. (when they are opened a vba macro is executed automatically)

Error: Uncaught exception from user code: Opening of directory Z:/Folder1/Projects/Templates/Abschlussbesuch failed: Invalid argument at stammdaten.pl line 26.

Directories:

stammdaten.cmd:

@echo off

perl -w stammdaten.pl

pause

Code:

# ------ Module ------ #
use strict;
use warnings;
use diagnostics;
use Cwd;
use OLE;
use Win32::OLE::Const "Microsoft Excel";
use Unicode::UTF8;
#----------------------#

# ------ HAUPTPROGRAMM ------ #
my $excel = CreateObject OLE "Excel.Application";
$excel->{Visible} = 0;
$excel->{DisplayAlerts} = 0;
$excel->{AskToUpdateLinks} = 0;

opendir(OD, cwd) or die "Kann Arbeitsverzeichnis nicht öffnen! $!";

my @verzeichnisse = grep { -d } glob cwd . "/*"; 

closedir OD;

foreach my $v(@verzeichnisse)
{
    my $dir = cwd . "/$v";
    opendir(my $verz, $dir) or die "Opening of directory $v failed: $!"; # LINE 26
    foreach my $xlsm (<*.xlsm>)
    {               
        open(FH, $xlsm) or die "Die Excel-Mappe $xlsm konnte nicht geoeffnet werden: $!";
            my $mappe = $excel->Workbooks->Open($xlsm);
            $mappe->Save;
            $mappe->Close;
            $excel->Quit;
        close FH;
    }
    closedir($verz);
}
#-----------------------------#

# ------ ENDE ------ #
exit 0;

The `glob` returns full path so directories in `@verzeichnisse` are full path, so each `$v` is when you iterate over them. So `cwd . "/$v";` doubles the `cwd` part of the path and creates a non-existent path. You can `opedir` on `$v` directly. I don't understand the error message though... — zdim, Apr 14 '20 at 07:35

Polar Bear · Accepted Answer · 2020-04-14T07:48:23.533

1

Please try following code (it should produce same result)

# ------ Module ------ #
use strict;
use warnings;
use diagnostics;
use Cwd;
use OLE;
use Win32::OLE::Const "Microsoft Excel";
use Unicode::UTF8;
#----------------------#

# ------ HAUPTPROGRAMM ------ #
my $excel = CreateObject OLE "Excel.Application";
$excel->{Visible} = 0;
$excel->{DisplayAlerts} = 0;
$excel->{AskToUpdateLinks} = 0;

my $path = cwd;

foreach my $xlsm (glob('*/*.xlsm'))
{               
    $xlsm = $path . $xlsm;
    open(FH, $xlsm) or die "Die Excel-Mappe $xlsm konnte nicht geoeffnet werden: $!";
    my $mappe = $excel->Workbooks->Open($xlsm);
    $mappe->Save;
    $mappe->Close;
    $excel->Quit;
    close FH;
}
#-----------------------------#

# ------ ENDE ------ #
exit 0;

Tip: modern perl page 139 - recommends use following form of open

open my $fh, '<', $filename
     or die "Couldn't open $filename";

....
....

close $fh;

edited Apr 14 '20 at 07:48

answered Apr 14 '20 at 06:31

Polar Bear

6,762
1
5
12

Unfortunately it does not work. I get the following error: `Can't call method "Save" on an undefined value at stammdaten.pl line 25 (#1) (F) You used the syntax of a method call, but the slot filled by the object reference or package name contains an undefined value. Something like this will reproduce the error: $BADREF = undef; process $BADREF 1,2,3; $BADREF->process(1,2,3); Uncaught exception from user code: Can't call method "Save" on an undefined value at stammdaten.pl line 25.` Excel opens but not the xlsm. Why? Abs Path required. – timunix Apr 14 '20 at 07:27
`(glob('*/*.xlsm')` does only use a relative path but I need the absolute path here.The above error occurs imho because he cannot open the xlsm file and tries to save it. Is there any way I can include `cwd` in the foreach head? – timunix Apr 14 '20 at 07:30
@timunix -- I am not familiar with `my $excel = CreateObject OLE "Excel.Application";` object. If `$excel` works only with absolute path then your statement is true, otherwise it should open file(s) relative to current directory. I run a quick test with perl files instead `excel` files and everything was Ok. – Polar Bear Apr 14 '20 at 07:38
1

@timunix -- I've added into code snippet couple lines to produce full path to the file. Although I suspicion that problem related to usage of `Execel` object. – Polar Bear Apr 14 '20 at 07:49
Now it is opening the excel files but with each opened file trying to save it, it says: "File is currently edited/open from another user. Do you want to save the file?" And when I click YES it gives me the same message again. If I click NO, it goes to the next excel file and asks the same dialogue question.I checked my EXCEL.EXE processes and closed them all before running the script. Still the same dialogue showing up. Strange... – timunix Apr 14 '20 at 08:06
The problem was that the excel macros did not close the EXCEL.EXE processes. Thus, when opening the excel file, other files were still open that should have been closed before. The consequence was that multiple excel files were opened aside from the one being treated with the perl script. For this reason, excel complains that the file is already open by someone. I changed the VBA script accordingly and boom, it works! Thanks for your help Polar Bear! – timunix Apr 14 '20 at 14:01

Add all cwd directories to an array, iterate directory by directory and open-save only .xlsm files in there (Windows 10)

1 Answers1