Open all Excel files in a folder and save using PowerShell-CodePudding

There are many small excels tables references a main table, but users don't have permission for for the main table. The owner is running the script so the tables are updated then users see the fresh data. Everyone only sees their own table (GDPR data).

I can't set permissions on the main table, password to the main table is not a solution, it asks users when updating data.

I found no other solution, so i want to use a script.

I want to open all Excel files (.xlsx) from one folder and save it using PowerShell.

The following solution works fine to a file:

$dir="C:\Temp\IPK_XY.xlsx"
$excl=New-Object -ComObject "Excel.Application"
$wrkb=$excl.Workbooks.Open($dir)
$excl.DisplayAlerts = $FALSE
$wrkb.Save()
$wrkb.Close()
$excl.Quit()

But how can all files be opened and saved in a similar way?

Thank you (and sorry for my English)!

Regards, T

CodePudding user response：

Continuing from my comment, you need a loop to open and save the xlsx files like this:

$allFiles = Get-ChildItem -Path 'X:\where\the\files\are' -Filter 'IPK_*.xlsx' -File
$excl     = New-Object -ComObject "Excel.Application"
$excl.DisplayAlerts = $false

foreach ($file in $allFiles) {
    $wrkb = $excl.Workbooks.Open($file.FullName)
    $wrkb.Save()   # or use just $wrkb.Close($true) to save and close
    $wrkb.Close()
}
# now quit excel and clean-up the used COM objects
$excl.Quit()
$null = [System.Runtime.Interopservices.Marshal]::ReleaseComObject($wrkb)
$null = [System.Runtime.Interopservices.Marshal]::ReleaseComObject($excl)
[System.GC]::Collect()
[System.GC]::WaitForPendingFinalizers()

CodePudding user response：

You want to get all the files in the folder, and then run a loop against each item. The loop will repeat all the actions you want to perform

# this is the path of the folder with all your files
# get all the files in this folder
$folderPath = "C:\Users\Scott\Desktop\gnl pbi\asdasd"    
$fileList = Get-ChildItem $folderPath

$excl = New-Object -ComObject "Excel.Application"
$excl.DisplayAlerts = $FALSE

# this is the loop with your desired actions
foreach ($fileName in $fileList)
{
    $filePath = Join-Path -Path $folderPath -ChildPath $fileName

    $wrkb = $excl.Workbooks.Open($filePath)
    $wrkb.Save()
    $wrkb.Close()
}

$excl.Quit()