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

Welcome Forums General PowerShell Q&A Reading Excel File, read first sheet and not define a name for it

Viewing 1 reply thread
  • Author
    Posts
    • #194207
      Participant
      Topics: 3
      Replies: 2
      Points: 44
      Rank: Member

      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.

    • #194231
      Participant
      Topics: 13
      Replies: 1753
      Points: 3,136
      Helping Hand
      Rank: Community Hero

      Use the index to reference the sheet:

      See: https://docs.microsoft.com/en-us/office/vba/api/excel.worksheets.item

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

Viewing 1 reply thread
  • The topic ‘Reading Excel File, read first sheet and not define a name for it’ is closed to new replies.