Using Powershell to process Excel files and check for the presence of a table name.

Question

I am trying to write a powershell script file that will scroll through each excel file in a given directory, check the file for a specially named sheet and copy it to another location if it matches.

Please see below what I have already tried:

[void][reflection.assembly]::Loadwithpartialname("microsoft.office.excel") $Excel = New-Object -ComObject Excel.Application $tempLocation = "C:\Test\" # Path to read files $files = Get-ChildItem C:\Test ForEach ($file in $files) { #Check for Worksheet named TestSheet $WorkBook = $Excel.Workbooks.Open($file) $WorkSheets = $WorkBook.WorkSheets foreach ($WorkSheet in $Workbook.Worksheets) { If ($WorkSheet.Name -eq "TestSheet") {$path = $tempLocation + "\" + $file Write "Saving $path" Copy-Item c:\Test\$file c:\Confirmed} Else {Write "$path does not contain TestSheet"} $WorkBook.Close() } } 

This script does not return errors in PowerShell, but just sits there without writing anything or copying any files. Any ideas?

EDIT: Here is my last script that now works successfully

 $ErrorActionPreference= 'silentlycontinue' $tempLocation = "C:\Source" # Path to read files $targetlocation = "C:\Target" Write "Loading Files..." $files = Get-ChildItem C:\Source Write "Files Loaded." ForEach ($file in $files) { #Check for Worksheet named TestSheet $Excel = New-Object -ComObject Excel.Application $Excel.visible = $false $Excel.DisplayAlerts = $false $WorkBook = $Excel.Workbooks.Open($file.Fullname) $WorkSheets = $WorkBook.WorkSheets | where {$_.name -eq "TestSheet"} if($WorkSheets) { $path = $tempLocation + "\" + $file $dest = $targetlocation + "\" + $file Write "Saving $path" $WorkBook.SaveAs($dest) } $Excel.Quit() Stop-Process -processname EXCEL } Read-host -prompt "The Scan has completed. Press ENTER to close..." clear-host; 

Answer 1

There were a few problems with my script logic. The following script will work successfully! It took several hours of research ...

 $ErrorActionPreference= 'silentlycontinue' $tempLocation = "C:\Source" # Path to read files $targetlocation = "C:\Target" Write "Loading Files..." $files = Get-ChildItem C:\Source Write "Files Loaded." ForEach ($file in $files) { #Check for Worksheet named TestSheet $Excel = New-Object -ComObject Excel.Application $Excel.visible = $false $Excel.DisplayAlerts = $false $WorkBook = $Excel.Workbooks.Open($file.Fullname) $WorkSheets = $WorkBook.WorkSheets | where {$_.name -eq "TestSheet"} if($WorkSheets) { $path = $tempLocation + "\" + $file $dest = $targetlocation + "\" + $file Write "Saving $path" $WorkBook.SaveAs($dest) } $Excel.Quit() Stop-Process -processname EXCEL } Read-host -prompt "The Scan has completed. Press ENTER to close..." clear-host; 

Answer 2

You do not need this line:

 [void][reflection.assembly]::Loadwithpartialname("microsoft.office.excel") 

($ Excel = New-Object -ComObject Excel.Application is enough here)

I do not think that you are referencing the full path to your Excel files. Try changing this line:

 $WorkBook = $Excel.Workbooks.Open($file) 

Change to:

 $WorkBook = $Excel.Workbooks.Open($file.Fullname) 

Also, consider adding a filter to the Get-ChildItem command; if there are subdirectories or files without Excel, they will cause errors:

 $files = Get-ChildItem C:\Test -filter "*.xls"