PowerShell open, save and close files with same filename at network storage

0
votes

I already made few steps forward..

On opening, saving and closing specified excel file at network directory.

$dir="\\10.100.0.10\folder\user.name\2015\file.xlsx"
$excl=New-Object -ComObject "Excel.Application"
$wrkb=$excl.Workbooks.Open($dir)
$excl.DisplayAlerts = $FALSE
$wrkb.Save()
$wrkb.Close()
$excl.Quit()

How can i insert loop so it could search files with same filename

file.xlsx

at this shared location thru all users for example

\\10.100.0.10\folder\

Finding file, opening it saving and closing?

Thanx.

1
excelpowershellforeach

1 Answers

1
votes

First search the \\10.100.0.0\folder (and subfolders) for files with the name file.xlsx

Add the FullPath of each file found to a variable ($Files)

Use foreach to add each file and process it, like this:

$Files = Dir '\\10.100.0.10\folder' -Recurse | ? {$_.Name -eq "file.xlsx"} | Select -ExpandProperty FullName
$excl=New-Object -ComObject "Excel.Application"
foreach ($file in $Files)
{
$wrkb=$excl.Workbooks.Open($file)
$excl.DisplayAlerts = $FALSE
$wrkb.Save()
$wrkb.Close()
}
$excl.Quit()