0

Am trying to automate certain tasks that I have to do, that although simple are tedious, due to the number of files. I currently have a script that will refresh every file within a folder, now these files have more worksheets that what my client needs, so after refreshing, I need to copy/paste the first two sheets in a new workbook, save in a general location where the client pick's it up. I have added what I thought was good code to do this copy/paste, but unfortunately, I'm getting errors in the copy/paste section as well as the SaveAs part. I did some research here and at "powershell.org", but couldn't find anything that helped :(.

This is my code:

Measure-Command {
$excel = new-object -comobject excel.application
$excel.DisplayAlerts = $false
$excelFiles = Get-ChildItem -Path "Network folder location" -Include *.xls, *.xlsm,*.xlsx, *.lnk -Recurse
Foreach($file in $excelFiles) {
$workbook = $excel.workbooks.open($file.fullname)
    foreach ($Conn in $workbook.Connections){
    $Conn.OLEDBConnection.BackgroundQuery = $false
    $Conn.refresh()     
}
$workBook.RefreshAll()
$workbook.save()
$wb2 = $excel.Workbooks.Add()
$sheetToCopy = $workbook.sheets.item(1),$workbook.sheets.item(2) #Source
$sheetToCopy.CopyTo($wb2) #Destination
$filename = $wb2.Sheets.Item(2).Cells.Item(4,2) #Destination file, 2nd sheet, column D row 2 has what I want to call the file (RVP John Doe - Dashboard)
$wb2.SavesAs("Networkfolder\$filename.xlsx")
$workbook.close()
$wb2.close()
}
$excel.quit()
}
Steven Rivera
  • 17
  • 8
  • Well, since worksheets don't have a `CopyTo()` method, I'm guessing that's the base if the errors that you see. For that matter, while they do have a `Copy()` method, it is only good for copying that sheet to another place in the same workbook. What you're going to want to do is make the new workbook, add a second sheet, then select the `UsedRange` of each sheet in the source workbook, copy the contents, and paste them into the new sheets in the new workbook. – TheMadTechnician Mar 06 '17 at 20:57
  • I'm trying to use the solution found in [link](http://stackoverflow.com/questions/23703354/how-to-use-powershell-to-copy-several-excel-worksheets-and-make-a-new-one) I can't really test out of that coding will work for me, since the "SaveAs" function doesn't seem to working. I put `$filename = $wb2.Sheets.Item(2).Columns.Item(4).Rows.Item(2)` which points to what is in the 2nd tab, column D, and row 2 a reference point and am trying to use that in the SaveAs command, but it fails `$wb2.SaveAs("Network Folder\**$filename**.xlsx")` I get a file named "System.__ComObject.xlsx" – Steven Rivera Mar 07 '17 at 20:38
  • A person shows how to do it using VBA...not sure if this can be broken down to how I need it... any experts can help? `Private Sub filename_cellvalue() 'Update 20141112 Dim Path As String Dim filename As String Path = "C:\Users\dt\Desktop\my information\" filename = Range("A1") ActiveWorkbook.SaveAs filename:=Path & filename & ".xls", FileFormat:=xlNormal End Sub` – Steven Rivera Mar 07 '17 at 20:48
  • Ok, the problem is that you are referencing a cell, not the value stored in that cell. A cell is an object with a lot more than just a value, it has (potentially) an underlying function, some formatting, and a ton of other things. You need to reference the `value2` property as such: `$filename = $wb2.Sheets.Item(2).Columns.Item(4).Rows.Item(2).value2`. See if that fixes it for you. – TheMadTechnician Mar 08 '17 at 18:53
  • Really sorry for the delay. I went out of town and was completely unplugged. Will try this now @TheMadTechnician – Steven Rivera Mar 13 '17 at 12:56
  • I thought I commented yesterday again.... well, unfortunately, the code isn't working :(, still saving that junk file "System.__ComObject.xlsx". – Steven Rivera Mar 14 '17 at 16:25

0 Answers0