none
How to see all Excel worksheets using PowerShell. RRS feed

  • Question

  • I have a script that does a lot of Excel processing. After making two worksheets, I then make three more and populate them with data. However, when I open that Workbook, sheets are in the order of 5, 4, 3, 1, 2

    Open the spreadsheet

    $ExcelObject=New-Object -ComObject Excel.Application
    $ExcelWorkbook=$ExcelObject.WorkBooks.Open("c:\Output.xlsx") 

    Make a worksheet

    $ActiveWorksheet=$ExcelWorkbook.WorkSheets.Add()
    $ActiveWorksheet.Activate()
    $ActiveWorksheet.Visible=$True
    $ActiveWorksheet=$ExcelWorkbook.WorkSheets.item("$WorksheetName")

    It looks like the three last worksheets don't exist until you click the left worksheet navigation arrow three times to make them appear.

    If I record a macro, the VBA code to make them visible is three instances of

    ActiveWindow.ScrollWorkbookTabs Sheets:=-1

    There is the xlFirst enumeration, but I can't seem to find the right syntax to get that working.

    Why are they not showing? Is there a Powershell way to make them visible or to scroll them to show before saving?

    • Moved by Bill_Stewart Monday, October 28, 2019 9:34 PM Move to more appropriate forum
    Monday, October 28, 2019 9:08 PM

All replies

  • Yes - you need to specify the order when inserting new sheets. By default the sheets are inserted in the front. Specify to add them after the last sheet.

    Just make the first sheet active if the order is not important.  Just select the sheet to activate and it should make all visible on the bottom abs.

    $wb.Worksheets[1].Activate()

    or

    $wb.Worksheets[1].Select()


    \_(ツ)_/




    • Edited by jrv Monday, October 28, 2019 10:16 PM
    Monday, October 28, 2019 10:09 PM
  • Here is a quick and dirty way to insert sheets in order and reset the tab.

    $inputfolder = 'c:\test'
    $mergedwb = 'c:\test\mytest.xlsx'
    
    $xl = New-Object -ComObject Excel.Application
    $xl.Visible = $true
    
    $wb = $xl.WorkBooks.add()
    
    Get-ChildItem $inputfolder\*.csv |
        ForEach-Object{
            Write-Host "Moving $_" -ForegroundColor green
            $xl.WorkBooks.OpenText($_) # creates a new WB from CSV
            # move sheet to end of new WB sheets collection also closes empty WB
            $xl.Workbooks[$_.Name].Worksheets[$_.Basename].Move([type]::Missing,$wb.Sheets.Item($wb.Sheets.count))
        }
    
    $wb.Worksheets['sheet1'].Delete() # remove original blank sheet
    $wb.Worksheets[1].Select()
    $wb.SaveAs($mergedwb)
    $wb.Close()
    $xl.Quit()
    


    \_(ツ)_/

    Monday, October 28, 2019 10:42 PM