Using Powershell and Excel

by jon_mull at 2012-10-18 05:22:45

Hi all, fairly new to power shell script but feel pretty confident with what ive done in the last few days with it. I do have a couple of questions that i will get into here shortly, but below is a synopsis of what I’m attempting to do.

We (my company), have about 60 GB of excel spread sheets that we need to go in and copy a tab of the workbook and paste it to a blank spread sheet and have it saved back to its original directory, in this case the child directory as the parent is what contains all of the other sub folders and spreadsheets. I have written my script thus far to be able to search through multiple directories and it will do the copy and paste function that I’m requiring, the problem I’m running into is that the naming system tries to rename every file copied the same thing to the same directory (the directory being saved to is just for testing purposes). Can any one shed some light on where to look for finding help with the code of saving back to the sub folder the original file was pulled from and then continue onto the next. the new file name should be the same as the original with the exception of SOW at the end, and then continue on to the next directory and repeat the process. Admins please feel free to lock or delete this thread if something is incorrect or in the wrong section of the forum



Get-ChildItem "C:\Users\Jon Mull\Desktop\SOW" -Recurse -Filter *.xls | <br>Sort-Object -Property DirectoryName -Descending |
Foreach-Object -Process{
$excel = new-object -comobject Excel.Application
$workbooks = $excel.Workbooks.Add()
$worksheets = $workbooks.worksheets
$worksheet = $worksheets.Item(1)
$worksheet.Name = “SOW”
$excel.Visible = $false
$workbooks.SaveAs(“C:\Users\Jon Mull\Documents\Test\SOW.xlsx”)
$excel.quit()
$file1 = ‘C:\Users\Jon Mull\Documents\Test\test for automation’ # source’s fullpath
$file2 = ‘C:\users\jon mull\Documents\Test\SOW.xlsx’ # destination’s fullpath
$xl = new-object -c excel.application
$xl.displayAlerts = $false # don’t prompt the user
$wb2 = $xl.workbooks.open($file1, $null, $true) # open source, readonly
$wb1 = $xl.workbooks.open($file2) # open target
$sh1_wb1 = $wb1.sheets.item(2) # second sheet in destination workbook
$sheetToCopy = $wb2.sheets.item(‘SOW’) # source sheet to copy
$sheetToCopy.copy($sh1_wb1) # copy source sheet to destination workbook
$wb2.close($false) # close source workbook w/o saving
$wb1.close($true) # close and save destination workbook
$xl.quit()
spps -n excel}



Im going to continue working on this and seeing if i cant find the resolution to my needs but any help would be much appreciated.
by nohandle at 2012-10-18 06:46:50
refined a bit, hope i didnt brake something in the process, my office is not working at the moment, so no way to test

#get-all files with .xls extension in the specified folder
#opens them in excel. copies and saves to the same folder
#with the filename_bak.xls

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

$sourceFolder = 'C:\Users\Jon Mull\Documents\Test&#39;

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”

#init the source workbook
$SrcWorkbook = $excel.workbooks.open($
.fullName, $null, $true) # open source, readonly

$wrkWorksheet = $wrkWorkbook.sheets.item(2) # second sheet in destination workbook
$sheetToCopy = $SrcWorkbook.sheets.item('SOW') # source sheet to copy
$sheetToCopy.copy($wrkWorksheet) # copy source sheet to destination workbook
$SrcWorkbook.close($false) # close source workbook w/o saving
$wrkWorkbook.saveas($(join-path -Path $.directoryName -ChildPath $($.basename + "bak" + $.extension)))
$wrkWorkbook.close($true) # close and save destination workbook

}
$excel.quit()
Stop-Process -Name "excel"
by jon_mull at 2012-10-18 07:00:47
Works great, really appreciate it, the only flaw is that it places two tabs in the workbook for the "SOW" information. So i might have to modify slightly but that works really great, now from what it appears if i were to set this to run on an entire directory that has subfolders inside it should run and place the "filename_wo_SOW" back into the directory the "filename_WO" was pulled from. is this correct? or will i need to add the function below


Sort-Object -Property DirectoryName -Descending | `
Foreach-Object -Process{

i would like to modify the file name to drop the "WO" and replace with "SOW" also but im reading over it and changing what i can find to change but other than that its exactly what i need
by nohandle at 2012-10-18 07:32:17
1) i thought two sheets were requirement as in your
original you use first
$worksheet = $worksheets.Item(1)
$worksheet.Name = “SOW”
then second
$sh1_wb1 = $wb1.sheets.item(2) # second sheet in destination workbook

just remove the line in mine
$wrkWorksheet = $wrkWorkbook.sheets.item(2) # second sheet in destination workbook

it should copy the data to the first one, not really sure though, no testing for me :slight_smile:
2)
yep it is ready for all *.xls files in the source folder and its recurse. the files are saved next to the original

Sort-Object -Property DirectoryName -Descending | is unnecessary to add and it is unnecesary in the script either, in fact it is a bottleneck because it prevents the foreach passing the objects one by one.
$wrkWorkbook.saveas($(join-path -Path $
.directoryName -ChildPath $($
.basename + "bak" + $.extension)))
save as original path + original name +_bak + extension (dot is already there)

you should add some error handling and ofcourse logging of errors
btw: is performance improved??
by jon_mull at 2012-10-18 08:01:48
Performance is tremondous, roughly 14 excel files processed and copied every minute, had it run during a meeting and finished 240 files in 20 minutes, now im going to start another project and work on this one though not much improvement is needed…

NEW UPDATE: I did find a flaw, after running the program on the directory intended for this script i have found that it is not copying the "SOW" contents as were shown in the test bed scenario. will look for reasoning behind this and provide update.
by jon_mull at 2012-10-18 09:58:18
I was incorrect in my statement there wasnt a flaw in the script. its the workbooks that do not have a tab labeled "SOW" are being copied and since the source doesnt have this tab its making a blank copy… is there a way to correct? may add to source so if file is not present roll to next?? thoughts ideas?
by nohandle at 2012-10-19 03:08:18
yes,
but you should change logic of the foreach a bit
first open the source file
check if the sow is present and if so do actions

open the source file
if ($SrcWorkbook.sheets.item(‘SOW’))
{
<everything else besides the source file closing>
<yes open the working file as second file>
<save and close the working file>
}
<closing the source file>

do not forget to close the file even if there is no sow sheet

using try catch finally is probably a better option but i wont rewrite it for you.