none
Creating Excel Worksheets with Powershell RRS feed

  • Question

  • Dear All,

    I'm having an issue when creating a excel sheet using office 2016 with powershell. The same script is working fine in Office 2007.

    This is the alert being promped

    "The property 'Name' cannot be found on this object. Verify that the property exists and can be set "

    "Invalid index. (Exception from HRESULT: 0x8002000B (DISP_E_BADINDEX)) "

    Any help please what wrong ?

    Thanks

    # Create a Excel Workspace 
        $excel = New-Object -ComObject Excel.Application 
     
    # make excel visible 
        $excel.visible = $true 
     
    # add a new blank worksheet 
        $workbook = $excel.Workbooks.add() 
    
    
    # rename worksheet    
    
        $Testworksheet = $workbook.Worksheets.Item("Sheet1")
        $Testworksheet.Name = "Test1"
    
        $Test2worksheet = $workbook.Worksheets.Item("Sheet2")
        $Test2worksheet.Name = "Test2"
    
    
        $Test3worksheet = $workbook.Worksheets.Item("Sheet3")
         $Test3worksheet.Name = "Test3"
    
    
        #$worksheets = $workbook.worksheets
        #$worksheet = $workbook.worksheets.Item(1)

    • Moved by Bill_Stewart Tuesday, November 7, 2017 10:49 PM This is not Excel automation support forum
    Tuesday, October 3, 2017 12:33 PM

All replies

  • How many sheets did you add?  You created a new workbook which only has 1 sheet.

    Do this:

    $workbook.WorkSheets.Count

    If you want more sheets you will have to add them.

    # add two sheets at after current
    $workbook.Worksheets.Add([type]::Missing,[type]::Missing,2)


    \_(ツ)_/

    Tuesday, October 3, 2017 12:48 PM
  • I tried your suggestion but still being prompted by the same error
    Tuesday, October 3, 2017 1:18 PM
  • Works absolutely as expected:

    $xl = New-Object -ComObject Excel.Application
    $xl.visible = $true
    $wb = $xl.Workbooks.add()
    $wb.Worksheets.Item('Sheet1').Name = 'Test1'
    $ws = $wb.WorkSheets.Add()
    $ws.Name = 'Test2'
    $ws = $wb.WorkSheets.Add()
    $ws.Name = 'Test3'
    


    \_(ツ)_/

    Tuesday, October 3, 2017 1:38 PM
  • Thanks for the feedback provided much appreciated. 

    Worked fine

    Wednesday, October 4, 2017 11:43 AM