Select and add excel values

Welcome Forums General PowerShell Q&A Select and add excel values

Viewing 1 reply thread
  • Author
    Posts
    • #226140
      Participant
      Topics: 3
      Replies: 1
      Points: 36
      Rank: Member

      I am wanting to create a script that would basically add the sum of what is in excel and save it as a variable so i can input it somewhere else.

      Excel looks like this:

      Count Store_Name
      5 Store 1
      10 Store 2
      5 Store 3

      So i would just need to add everything in column A and store it in a variable (5+10+5) = 20, $Count = 20

      $filePath ="E:\Extra\Note\Test.xlsx"
      $excelObj = New-Object -ComObject Excel.Application
      $excelObj.Visible = $false

      #open WorkBook
      $workBook = $excelObj.Workbooks.Open($filePath)
      $workSheet = $workBook.sheets.Item(1)

      #Select the range of rows should read
      $range= 1
      for($i=1;$i-le $range;$i++){
      $workSheet.Range("A1:A1").Text
      }

      This is what i have learned so far, but im sure there are better ways than me copying  $worksheet.Range ("A1").Textfor the whole column until 300, and then trying to find out how to add them together.

    • #226383
      Senior Moderator
      Topics: 9
      Replies: 1422
      Points: 5,290
      Helping Hand
      Rank: Community MVP

      You could use the fantastic ImportExcel module, then use Import-Excel cmdlet to import the xlsx file.
      then use Measure-Object cmdlet to calculate the sum of any property.

      • This reply was modified 7 months ago by kvprasoon. Reason: post correction
Viewing 1 reply thread
  • The topic ‘Select and add excel values’ is closed to new replies.