CODEWITHSUNDEEP
excelpowershell
Sev09
Sev09

Reputation: 883

Using Powershell to loop through Excel files and check if Spreadsheet name exists

I'm trying to write a powershell script that will loop through each excel file in the given directory, check the file for a specifically named worksheet, and then copy that file to another location if it's a match.

Please see below for what I've 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 returns no errors in PowerShell, but just sits there without writing anything or copying any files. Any ideas?

EDIT: Here's my final script that is now running 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;

Upvotes: 4

Views: 17730

Answers (2)

Sev09
Sev09

Reputation: 883

There were several issues with my script's logic. The following script ran successfully! It took 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;

Upvotes: 4

nimizen
nimizen

Reputation: 3419

You don't need this line:

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

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

I don't think you're referencing the full path to your Excel files. Try modifying this line:

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

Amend to:

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

Additionally, consider adding a filter to your Get-ChildItem command, if there are sub-directories or non-Excel files, they will cause errors:

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

Upvotes: 1

Related Questions