Reading Excel File, read first sheet and not define a name for it

PowerShell Help
1

Hello Forum,

I have a PowerShell script that reads in an Excel spreadsheet. I’ve defined sheet name as follows:

$sheetName = "Sheet1"
What would be the correct way to identify the first sheet in the workbook instead of locking this to the name of the sheet. We will only have one sheet but the name of the sheet may change. Does PowerShell allow me to read in the data from the first (and only) sheet regardless of name?

The section of my script that has the above code is as follows:

# Open Excel and load in the input file. Define columns from input file: $batchDirectory = $folder $sheetName = "Sheet1" $batchNum = 1 $objExcel = New-Object -ComObject Excel.Application $workbook = $objExcel.Workbooks.Open($file) $sheet = $workbook.Worksheets.Item($sheetName) $objExcel.Visible=$false $rowMax = ($sheet.UsedRange.Rows).count $rowHostname,$ColHostname = 0,1 $rowIP,$ColIP=0,2
 

Thank you.

2

Use the index to reference the sheet:

$sheet = $workbook.Worksheets.Item(1)

See: Worksheets.Item property (Excel) | Microsoft Docs

Also, keep in mind that the index starts at 1, not 0. So, sheet .Item(1) is the first sheet in the workbook.