perlopendircwd

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


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:

enter image description here

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;

Solution

  • 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;