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)by DonJ at 2012-10-22 13:15:33
"$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.
Everyone thinks every question is a noob question. This isn’t.by jon_mull at 2012-10-22 13:25:07
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.
thanks, ill give that a go and see where it leads