Powershell: ForEach loop error, not doing what i want

Question

With my code I am trying to create a folder, then open a certain Excel file, edit it, and save it to the location that has been made via the function Dagcontrole_folders_maken . Currently i am using this ForEach in a ForEach loop. But it is not working. This is a simplified version of the complete code. The setup of the functions with the variables are neccesary.

Code to demonstrate problem:

$path_dagelijkse_controle = "C:\Users\Nick\Desktop\Test1",
                            "C:\Users\Nick\Desktop\Test2", 
                            "C:\Users\Nick\Desktop\Test3"


$list_excels = 'C:\Users\nick\Desktop\Test1\kek3',    #pad waar het excel bestand staat die geopend moet worden
               'C:\Users\nick\Desktop\Test1\kek4',
               'C:\Users\nick\Desktop\Test1\kek5'



function Dagcontrole_folders_maken ($huidige_folder) {
md -Path "$huidige_folder\2020" -Force     # Makes all the neccessary folders
}


function Aanpassen_excel_dagcontrole ($path, $huidige_folder) {

$Excel = New-Object -ComObject excel.application
$Excel.visible = $True
$date_today= (get-date).DayOfWeek
$Workbook = $excel.Workbooks.open($path)                            
$workbook.SaveAs("$huidige_folder\$date_today") 
$Excel.Quit()
Remove-Variable -Name excel
[gc]::collect()
[gc]::WaitForPendingFinalizers()

}

   
Foreach ($i in $path_dagelijkse_controle) {
    Dagcontrole_folders_maken $i
    Foreach ($a in $list_excels) {
        Aanpassen_excel_dagcontrole $a $i
    }
}

I'm getting errors on the following part: $workbook.SaveAs("$huidige_folder\$date_today"). Telling it does not have access to the file.

What I am trying to do is save the excel file in the directory that has just been made by the function Dagcontrole_folders_maken. I try this in the second loop with the data that comes from the $path_dagelijkse_controle list

The loop should do the following:

Dagcontrole_folder_maken
makes folder with location: "C:\Users\Nick\Desktop\Test1\2020"

Aanpassen_excel_dagcontrole 
$Workbook = $excel.Workbooks.open('C:\Users\nick\Desktop\Test1\kek3')
$workbook.SaveAs('C:\Users\Nick\Desktop\Test1"\2020\20200806')

And after that is should do:

Dagcontrole_folder_maken
makes folder with location: "C:\Users\Nick\Desktop\Test2\2020"

Aanpassen_excel_dagcontrole 
$Workbook = $excel.Workbooks.open('C:\Users\nick\Desktop\Test2\kek4')
$workbook.SaveAs('C:\Users\Nick\Desktop\Test2"\2020\20200806')

And then rest of the list

The complete code for reference:

$path_dagelijkse_controle = "C:\Users\Nick\Desktop\Test1",
                            "C:\Users\Nick\Desktop\Test2", 
                            "C:\Users\Nick\Desktop\Test3"

$list_excels = 'C:\Users\nick\Desktop\Test1\kek3',    #pad waar het excel bestand staat die geopend moet worden
               'C:\Users\nick\Desktop\Test1\kek4',
               'C:\Users\nick\Desktop\Test1\kek5'





function Dagcontrole_folders_maken ($huidige_folder) {

$Dagelijkse_controle = "Dagelijkse controle"
$datum_vandaag = $(Get-Date).toString('yyyy-MM-dd')
$jaar = $datum_vandaag.Substring(0,4)
$maand = $datum_vandaag.substring(5, 2)
$dag = (get-date).DayOfWeek
$folder_maand = Get-Date -UFormat "%m - %B"

md -Path "$huidige_folder\$jaar\$folder_maand\Dagelijks\$datum_vandaag" -Force     # Makes all the neccessary folders
}


function Aanpassen_excel_dagcontrole ($path, $huidige_folder) {

#editing excel file
$Controle_mailbox_vrijdag = "Nora Remeeus"
$weekcontrole1 = "Maandag"
$weekcontrole2 = "Dinsdag"
$partimedag = "Woensdag"
$dagcontroleur_parttimedag = "Victor Wong"
$weekcontrole_persoon = "Nick Siegert"
$afwezig_mailboxcontrole = "Vrijdag"


$Excel = New-Object -ComObject excel.application
$Excel.visible = $False
$Workbook = $excel.Workbooks.open($path)                            
$Worksheet = $Workbook.WorkSheets.item("Uit te voeren werkzaamheden")
$worksheet.activate()  

$workbook.ActiveSheet.Cells.Item(3,3) = Date

if ($dag -eq $partimedag) {
    $workbook.ActiveSheet.Cells.Item(9,3) = $dagcontroleur_parttimedag
    $workbook.ActiveSheet.Cells.Item(10,3) = $dagcontroleur_parttimedag
    $workbook.ActiveSheet.Cells.Item(12,3) = $dagcontroleur_parttimedag
    }

if (($dag -eq $weekcontrole1) -or ($dag -eq $weekcontrole2)) {
    $workbook.ActiveSheet.Cells.Item(13,3) = $weekcontrole_persoon
    }

if ($dag -eq $afwezig_mailboxcontrole) {
    $workbook.ActiveSheet.Cells.Item(11,3) = $Controle_mailbox_vrijdag
    }

$workbook.SaveAs("$huidige_folder\$jaar\$folder_maand\Dagelijks\$datum_vandaag\$Dagelijkse_controle $datum_vandaag") #Edit to save with Dagelijkse controle + datum_vandaag   Hardcoded $huidige folder (eerste deel) oud: "$huidige_folder\$jaar\$folder_maand\Dagelijks\$datum_vandaag\$Dagelijkse_controle $datum_vandaag"
$Excel.Quit()
Remove-Variable -Name excel
[gc]::collect()
[gc]::WaitForPendingFinalizers()

}



Foreach ($i in $path_dagelijkse_controle) {
    Dagcontrole_folders_maken $i
    Foreach ($a in $list_excels) {
        Aanpassen_excel_dagcontrole $a $i
    }
}

Please specify what it is that is not working. And try to trim down the supplied code to the relevant portion. I think what you are looking for is a Hashtable. See learn.microsoft.com/en-us/powershell/module/… — Rno
– Rno, Commented Aug 6, 2020 at 20:18
When you open the workbook, are you sure you are opening a workbook? Looks like you are opening a folder to me. — Rno
– Rno, Commented Aug 6, 2020 at 21:07
(btw, you may want to look into ImportExcel. See: powershellgallery.com/packages/ImportExcel) — Rno
– Rno, Commented Aug 6, 2020 at 21:50

postanote · Accepted Answer · 2020-08-07 17:56:17Z

You've got what appears to be a logic and syntax errors in your code.

If you set Excel to not viable, then you cannot set it as the active Window.

So, this...

$Excel.visible = $False
....
$worksheet.activate()

... should be this...

$Excel.visible = $True

You are running this function ...

Aanpassen_excel_dagcontrole

... in a Loop, but this function is opening and closing MSExcel.exe on each pass. This will cause issues because the startup and shutdown of MSOffice products take time, but your loop is not waiting for that.

You should only an Office app once, open, process, close files, and after all are processed, then close the MSOffice app used.

I'd suggest you remove this ...

$Excel.Quit()
Remove-Variable -Name excel
[gc]::collect()
[gc]::WaitForPendingFinalizers()

... from that function and make it your last entry in the script. So, this:

# Start MSExcel only once
$Excel         = New-Object -ComObject excel.application
$Excel.visible = $True

function Aanpassen_excel_dagcontrole 
{
    [cmdletbinding(SupportsShouldProcess)]
    Param 
    (
        $path, $huidige_folder
    ) 

    # editing excel file
    $Controle_mailbox_vrijdag             = 'Nora Remeeus'
    $weekcontrole1                        = 'Maandag'
    $weekcontrole2                        = 'Dinsdag'
    $partimedag                           = 'Woensdag'
    $dagcontroleur_parttimedag            = 'Victor Wong'
    $weekcontrole_persoon                 = 'Nick Siegert'
    $afwezig_mailboxcontrole              = 'Vrijdag'

    $Workbook                             = $excel.Workbooks.open($path)                            
    $Worksheet                            = $Workbook.WorkSheets.item('Uit te voeren werkzaamheden')
    $worksheet.activate()  
    $workbook.ActiveSheet.Cells.Item(3,3) = Date


    if ($dag -eq $partimedag)
     {
        $workbook.ActiveSheet.Cells.Item(9,3)  = $dagcontroleur_parttimedag
        $workbook.ActiveSheet.Cells.Item(10,3) = $dagcontroleur_parttimedag
        $workbook.ActiveSheet.Cells.Item(12,3) = $dagcontroleur_parttimedag
    }

    if (($dag -eq $weekcontrole1) -or ($dag -eq $weekcontrole2)) 
    {$workbook.ActiveSheet.Cells.Item(13,3)    = $weekcontrole_persoon}

    if ($dag -eq $afwezig_mailboxcontrole) 
    {$workbook.ActiveSheet.Cells.Item(11,3)    = $Controle_mailbox_vrijdag}

    $workbook.SaveAs("$huidige_folder\$jaar\$folder_maand\Dagelijks\$datum_vandaag\$Dagelijkse_controle $datum_vandaag") 
}


Foreach ($i in $path_dagelijkse_controle) 
{
    Dagcontrole_folders_maken $i
    Foreach ($a in $list_excels) 
    {Aanpassen_excel_dagcontrole $a $i}
}


# Clean-Up
$Excel.Quit()
Remove-Variable -Name excel
[gc]::collect()
[gc]::WaitForPendingFinalizers()

Making this change should eliminate your concern here...

I'm getting errors on the following part: $workbook.SaveAs("$huidige_folder$date_today"). Telling it does not have access to the file.

... because this indicated, the file is still in use. You should always check for the file open/close before processing the next. Since OS and App processing speeds impact availability. AS well as check for the existence of folder or files before you try to use them.

So, modify your code to include error handling, i.e, if/then, try/catch, test-Path, etc.

Simple example check:

# File in use check
$ExcelFilePath = 'D:\Temp\FileData.xlsx'
try {[IO.File]::OpenWrite($ExcelFilePath).close()}
catch {Write-Warning -Message "$ExcelFilePath is in use by another process"}
# Results
<#
WARNING: D:\Temp\FileData.xlsx is in use by another process
#>

Grasmat · Accepted Answer · 2020-08-07 13:15:22Z

0

I ended up doing the following instead of ForEach in Foreach:

Foreach ($i in $path_dagelijkse_controle) {
    Dagcontrole_folders_maken $i
}

foreach($i in 0..2){
    Aanpassen_excel_dagcontrole $list_excels[$i] $path_dagelijkse_controle[$i]
    Start-Sleep -s 15
}

The sleep function is there because i got a alot of random excel error, on random iterations. And this miraculouslyfixed the error.

answered Aug 7, 2020 at 13:15

Grasmat

751 silver badge6 bronze badges

Collectives™ on Stack Overflow

Powershell: ForEach loop error, not doing what i want

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related