时间戳

首页 > 解决方案 > Powershell：ForEach 循环错误，没有做我想做的事

arrays - Powershell：ForEach 循环错误，没有做我想做的事

问题描述

使用我的代码，我试图创建一个文件夹，然后打开某个 Excel 文件，对其进行编辑，然后将其保存到通过函数创建的位置Dagcontrole_folders_maken。目前我在 ForEach 循环中使用这个 ForEach。但它不起作用。这是完整代码的简化版本。必须使用变量设置函数。

演示问题的代码：

$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
    }
}

我在以下部分遇到错误：$workbook.SaveAs("$huidige_folder\$date_today"). 告诉它无权访问该文件。

我想要做的是将 excel 文件保存在刚刚由function Dagcontrole_folders_maken. 我在第二个循环中尝试使用来自$path_dagelijkse_controle列表的数据

循环应该执行以下操作：

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')

之后应该这样做：

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')

然后是列表的其余部分

完整代码供参考：

$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
    }
}

标签： arrayslistpowershellforeach

解决方案

您的代码中似乎存在逻辑和语法错误。

如果将 Excel 设置为不可用，则无法将其设置为活动窗口。

所以这...

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

……应该是这个……

$Excel.visible = $True

您正在运行此功能...

Aanpassen_excel_dagcontrole

...在循环中，但此函数在每次传递时打开和关闭 MSExcel.exe。这将导致问题，因为 MSOffice 产品的启动和关闭需要时间，但您的循环不会等待。

您应该只使用一次 Office 应用程序，打开、处理、关闭文件，并在处理完所有文件后，再关闭使用的 MSOffice 应用程序。

我建议你删除这个...

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

...从该函数中，并使其成为脚本中的最后一个条目。所以这：

# 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()

进行此更改应该可以消除您在这里的担忧...

我在以下部分遇到错误：$workbook.SaveAs("$huidige_folder$date_today")。告诉它无权访问该文件。

...因为这表明，该文件仍在使用中。在处理下一个文件之前，您应该始终检查文件是否打开/关闭。由于操作系统和应用程序的处理速度会影响可用性。以及在尝试使用它们之前检查文件夹或文件是否存在。

因此，修改您的代码以包含错误处理，即 if/then、try/catch、test-Path 等。

简单示例检查：

# 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
#>

推荐阅读