PowerShell: Format second sheet within xlsx file

PowerShell: Format second sheet within xlsx file

I am working with an xlsx file that has two sheets within it.
I am uploading data into these sheets and formatting it.
I am able to successfully format the first sheet but not the second sheet.

This is the code for how I format the first sheet:

# Format Data: Autofit Columns
$lgTime = "[{0:HH:mm:ss}]" -f (Get-Date)
Write-Host "$lgTime Autofitting Data Columns..."
$range2autofit = $worksheet.UsedRange
$rowCount = $range2autofit.Rows.Count
[void] $range2autofit.EntireColumn.Autofit()
$lgTime = "[{0:HH:mm:ss}]" -f (Get-Date)
write-host "$lgTime Creating Excel Table Format ..."
$tableStyle = "TableStyleMedium9"
$tableStyle = "TableStyleLight21"
$Worksheet.Columns.Item("A").NumberFormat = "MM/DD/YYYY"
$ListObject = $WorkBook.ActiveSheet.ListObjects.Add(1, $range2autofit, $null , 1, $null, $tableStyle)

I would like the same formatting to be applied to the second sheet within the file but am having trouble doing that. I have tried using that same code again but with small changes such as:

Workbook.Worksheet.Item(2).UsedRange 

Workbook.Worksheet.Item("Sheet2Name").UsedRange

My thought process is that I should be able to use the same code but just access the second sheet in it, I think I am just not accessing the second sheet correctly. That could be completely wrong though.

Edit:

This is where I defined $workbook and added a sheet to the xlsx file which is followed by the renaming of each sheet

$dataFile = "FILE LOCATION.xlsx" 

$objExcel = New-Object -ComObject Excel.Application
$objExcel.Visible = $false
$workbook = $objExcel.Workbooks.Open($dataFile)
$worksheet = $workbook.Worksheets.Add()

$worksheetOne = $workbook.Worksheets.Item(1)
$worksheetOne.Name = "Sheet1Name"
$worksheetTwo = $workbook.Worksheets.Item(2)
$worksheetTwo.Name = "Sheet2Name"

Solution

From the code you added in your edit, it looks like you're getting tripped up when adding a sheet, then using the wrong index later? Worksheets.Add() creates a blank worksheet at index 2 by default, not at the end. For example:

# A good way to check and see what you're doing with your sheets:
$workbook.Worksheets | select Index,Name

Index Name      
----- ----      
    1 Sheet1
    2 Sheet2

# add a new sheet
$worksheet = $workbook.Worksheets.Add()

# check again
$workbook.Worksheets | select Index,Name

Index Name      
----- ----      
    1 Sheet1
    2 Sheet3  # whoops!
    3 Sheet2

To add before/after a specific worksheet instead, you can specify like this:

# missing value is required for COM functions
$newSheet = $workbook.Worksheets.add( 
  [System.Reflection.Missing]::Value,  ## before index n
  $workbook.Worksheets.Item(2)         ## after index n
)

Then just be careful when you select your sheets, and you should be good to go!

# Setting sheet properties by referring to variable
$ws1 = $workbook.Worksheets.Item(1)
  $ws1.Name = "Sheet1Name"
  $ws1.UsedRange.EntireColumn.AutoFit()

$ws2 = $workbook.Worksheets.Item(2)
  $ws2.Name = "Sheet2Name"
  $ws2.UsedRange.EntireColumn.AutoFit()