Powershell how to I copy() a worksheet and saveas it's own workbook

Question

I'm not to great with powershell, but I am writing a script that opens an excel file, reads content from a .txt file, and throws that data into the excel sheet. The data entered in will give values to a chart on another worksheet. I want to ask the user if they would like to create a copy of this chart or not (not always required) If the user would like to then it will copy only the chart worksheet, and saveas its own workbook (..copyedChart.xls).

Now, I know when I use the .Copy() function it will take the current active sheet and already open up a new instance of excel. My problem is actually being able "control" this new instance of excel, I am having trouble understanding how to actually call that sheet and save it.

Here is what I have..

#Create an instance of Excel
$excel = New-Object -comobject Excel.Application

Write-Host "Initializing applications.."   #all Write-Host are for the users..

#declaring sheet names.. 
$sheetName = "Sheet1"
$sheetName2 = "Sheet2"

$excel.displayAlerts = $false 


Try{    

    #open Excel file
    [string]$file = "C:\Users\Desktop\test.xls"

    #create a reference to the specified excel workbook
    $workBook = $excel.Workbooks.open($file)

    #activates sheet
    $sheet = $workBook.WorkSheets.Item($sheetName).activate()

    $excel.Visible = $true

    $data = Get-Content C:\Users\Desktop\input.txt  

}#end try

Catch{
    Write-Host "An error has occured."
}#end catch


Write-Host "Inputting new data.."

$i = 0
$rowNumber = 2

#Im just parsing the input.txt and spitting it out into excel
foreach($row in $data){    
    if($row){   
        [Array] $index = $row.Split(" ")
        $i++
        $column = 1

        if($i -ge 1){    
                foreach($item in $index){          
                    $excel.Cells.Item($rowNumber, $column) = "$item"
                    $column++
                }#end foreach  
             $rowNumber++             
        }#end if $i          
    }#end if $row
}#end foreach

$date = $excel.Cells.Item(2, 1).Value().toString("MMMM-dd-yyyy")       #row, column 

#changes the active sheet
$sheet2 = $workBook.Sheets.Item($sheetName2).Select()

#references the active chart on the active page
$chart = $workBook.ActiveChart

Write-Host "Updating charts.."

#changes the title of the chart to include the current date    
$chart.ChartTitle.Text = "Some title - $date"

#saves the files to these locations
$save = 'C:\Users\Desktop\'+$date+'-text.xls'
$saveChartCopy = 'C:\Users\Desktop\'+$date+'-CHARTCOPY.xls'

Write-Host "Saving new file.."

#save this workbook
$workBook.SaveAs($save)


#asks the user if they would like to create a copy of the chart
$makeCopy = Read-Host ("Would you like to create a copy of the chart? (y/n)")

#-----------------------------------------------------------STUCK HERE
#if yes, copy and save the chart as a new workbook.
if($makeCopy -eq "y" -or $makeCopy -eq "Y"){

    $copiedChart = $chart.Copy()           #Copies the chart and opens into a new instance of excel...    
    $copiedChart.SaveAs($saveChartCopy)    #My sad attempt at trying to save the copied chart...
}

#if no, than close excel
elseif($makeCopy -eq "n" -or $makeCopy -eq "N"){

    #close excel
    Write-Host "Closing Excel.."
    $excel.Quit()
    Write-Host "Complete!"
}

else{
    Read-Host "Please enter a valid option!"
}

If there is any confusion as to what I am asking please ask and I will try to further explain.

Also, because I am new to powershell and I am kind of a noob programmer.. I am open to all other input in regards to my code.

Is this what you're looking for? http://stackoverflow.com/questions/3226096/copy-excel-worksheet-from-one-workbook-to-another-with-powershell — Adi Inbar, Jan 28 '14 at 19:18
@AdiInbar They already have a destination file, I am trying to create the destination file from the .Copy() function (because it already opens a new instance of Excel and creates a new workbook with the .copy() as a work sheet), and be able to "Control" this new workbook. I don't know how to call and control the new instance of excel — BRBT, Jan 28 '14 at 19:38

score 1 · Answer 1 · answered Feb 10 '14 at 16:18

1

You have to create a new workbook object with $Excel.Workbooks.add():

$NewWorkBook = $Excel.Workbooks.Add()
# Copy and paste your sheet
$NewWorkBook.SaveAs($FileName)
$NewWorkBook.Close()

answered Feb 10 '14 at 16:18

Andy Chen

361
2
9

This just creates another workbook. Using `.Copy()` will already do this..I just don't know how to interact with that new "copied" workbook – BRBT Feb 10 '14 at 20:36
The $NewWorkBook is another Workbook object just like the one you are interacting with. Use it as you want. – Andy Chen Mar 04 '14 at 15:40

score 1 · Answer 2 · answered Mar 06 '14 at 21:28

1

If I understand your problem correctly(and please tell me if I'm not), all you have to do is catch the handle to the new workbook in another variable. I was curious about this myself and decided to throw something quick and dirty to play around with it. Try something like this:

$Excel = New-Object -ComObject "Excel.Application"

$Workbook = $Excel.Workbooks.Add()
$Sheet = $Workbook.Worksheets.Item(1)
$Excel.Visible = $true
$Cells = $Sheet.Cells

$Workbook2 = $Excel.Workbooks.Add()
$Sheet2 = $Workbook2.Worksheets.Item(1)
$Cells2 = $Sheet2.Cells    

$Cells.Item(1,1) = "Book 1"
$Cells2.Item(1,1) = "Book 2"

$Workbook.Close()
$Workbook2.Close()
$Excel.Quit()
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($Excel)

Also, don't forget to cleanup that ComObject!

answered Mar 06 '14 at 21:28

Telestia

300
2
12

Yes, my problem is I can't figure out how to handle the new workbook that I get when I use `.Copy()`. I want to be able to save this "new workbook" or copy, under a different name. Also I want to be able to `.Copy()` 2 worksheets, and have them in their own workbook. Try running the `.Copy()` on a worksheet and you will see what I mean. Also could you explain or point me to what you're last line means? `[System.Runtime.Interopservices.Marshal]::ReleaseComObject($Excel)` – BRBT Mar 07 '14 at 13:45
That just releases the Excel process. If you run your script a bunch of times without calling it you could have several instances of EXCEL.exe running at once. I didn't know about it for a while actually. One day I checked my task manager and had like 9 of them running. I'll look into the copy issue today. – Telestia Mar 07 '14 at 14:49

Powershell how to I copy() a worksheet and saveas it's own workbook

2 Answers2