Verifying Worksheet and moving file to diff. dir.

by jon_mull at 2012-10-22 13:01:53

Im working on script right now, to verify that an excel workbook has a particular worksheet. Upon completion and verification it has a certain worksheet i need the file moved to a different directory, and a subfolder made with the files name for a second script to be run on that directory after the first PS script is complete, thus far i have gotten the following script written (i know its not complete yet but im still going through)

"$excel = new-object -comobject Excel.Application
$excel.Visible = $false
$excel.displayAlerts = $false # don’t prompt the user

$sourceFolder = ‘C:\Users\Jon Mull\Documents\Test’

Get-ChildItem $sourceFolder -Recurse -Filter *.xls | Sort-Object -Property DirectoryName -Descending |
Foreach-Object -Process{

#init the working (destination) workbook
$wrkWorkbook= $excel.Workbooks.Add()
$wrkWorksheet = $wrkWorkbook.worksheets.item(1)
$wrkWorksheet.Name = “SOW”



}
$excel.quit()
Stop-Process -Name "excel"



Im having a problem because rather than skipping and moving to the next file it throws an argument up when it doesnt locate the correct worksheet. In an ideal scenario it would do the following

DIR: c:\example\documents\test

1st file: example_workbook.xls

locate -> "Worksheet name"

close do not save

move to C:\example\documents\test_ready\Ready for 2nd script\parent_file_name\example_workbook.xls

but if the “Worksheet_name”=false move to next file.

Sorry for all the noob questions just getting my feet wet and hoping someone can help shed light on this.
by DonJ at 2012-10-22 13:15:33
Everyone thinks every question is a noob question. This isn’t.

Consider wrapping the offending code in a Try…Catch block. Thatw ay, if the worksheet location fails, you can catch it and do something - like skip the bit where you try to move it.
by jon_mull at 2012-10-22 13:25:07
thanks, ill give that a go and see where it leads