Select and add excel values

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

Viewing 1 reply thread
  • Author
    • #226140
      Topics: 2
      Replies: 1
      Points: 24
      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++){

      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.

      • This topic was modified 3 weeks, 5 days ago by KarolSweats.
    • #226383
      Senior Moderator
      Topics: 8
      Replies: 1215
      Points: 4,335
      Helping Hand
      Rank: Community Hero

      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.

      Import-Excel -Path c:\temp\test.xlsx | Measure-Object -Property count -Sum
      • This reply was modified 3 weeks, 4 days ago by kvprasoon. Reason: post correction
Viewing 1 reply thread
  • You must be logged in to reply to this topic.