none
Move a worksheet from one excel file to another existing excel file with powershell

    Question

  • How can I use powershell to move a worksheet from an existing open excel file to another open existing excel file.  Note, I don't want to create a new excel file which is what "$excel.worksheets.item("name of worksheet to move").move()" does. 
    Friday, July 24, 2009 6:45 PM

Answers

  • I'm not sure if you've figured something out already...

    move needs to know where to move the sheet. You can specify to move it before or after a sheet in the same workbook or in another workbook. move will move the sheet to a new workbook if it doesn't know where to put it.

    I'm still trying to get comfortable with powershell and excel, so here is a vbscript that hopefully, you can convert to powershell. I used sheet instead of worksheet because sheet can be charts or worksheets.

    'moves "sheet1" in book1.xls to book2.xls
    'it is moved before 1st sheet in book2.xls
    
    set xl = createobject("excel.application")
    
    xl.visible = true
    
    set wbSrc = xl.workbooks.open("c:\scripts\book1.xls")
    set wbDst = xl.workbooks.open("c:\scripts\book2.xls")
    
    wbSrc.sheets("sheet1").move wbDst.sheets(1)
    

    Hopefully, soon, I can do this comfortably in powershell,
    Francis
    Monday, August 31, 2009 3:00 AM

All replies

  • I'm not sure if you've figured something out already...

    move needs to know where to move the sheet. You can specify to move it before or after a sheet in the same workbook or in another workbook. move will move the sheet to a new workbook if it doesn't know where to put it.

    I'm still trying to get comfortable with powershell and excel, so here is a vbscript that hopefully, you can convert to powershell. I used sheet instead of worksheet because sheet can be charts or worksheets.

    'moves "sheet1" in book1.xls to book2.xls
    'it is moved before 1st sheet in book2.xls
    
    set xl = createobject("excel.application")
    
    xl.visible = true
    
    set wbSrc = xl.workbooks.open("c:\scripts\book1.xls")
    set wbDst = xl.workbooks.open("c:\scripts\book2.xls")
    
    wbSrc.sheets("sheet1").move wbDst.sheets(1)
    

    Hopefully, soon, I can do this comfortably in powershell,
    Francis
    Monday, August 31, 2009 3:00 AM
  • I know this question was posted some time ago, but for anyone wondering how to do this, here is the answer:

    # Create excel com object
    $Excel = new-object -comobject Excel.application   

    # Create Excel Workbook
    $WorkBookA = $Excel.WorkBooks.add()    

    # Open an Existing Excel Workbook
    $WorkbookB = $excel.Workbooks.Open("c:\Temp\test.xlsx") 

    # Move the first sheet in the existing workbook to the second workbook as the second sheet.
    $WorkbookB.Worksheets.Item(1).Move($WorkBookA.WorkSheets.item(2))

    # Save the new workbook
    $WorkBookA.SaveAs("C:\Temp\excel.xlsx")

    # Close the Workbook
    $WorkBookA.Close()

    # Quit Excel
    $Excel.Quit()

    Ta da! :)
    Thursday, May 27, 2010 1:46 PM