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