0 Replies - 139 Views - Last Post: 27 October 2013 - 04:12 AM Rate Topic: -----

#1 jeansymolanza  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 77
  • Joined: 20-February 08

Powershell - Pulling data from additional column on workbook

Posted 27 October 2013 - 04:12 AM

I'm currently pulling out information from column B in an Excel worksheet + the worksheet name (36 worksheets in total).

How would I go about pulling information from column J (which has the same start row/end row as the information pulled out from column B)/> in the same worksheet and assigning it to the object property $apps.location? I've done a bit of research on the internet, but there doesn't seem to be a straightforward method of accomplishing this.

$(for ($i=3; $i -le $wb.sheets.count; $i++){

    $sh=$wb.Sheets.Item($i)
    $startCell_appname = $sh.cells.item(1,2).EntireColumn.find("Application Name").Address()
    $startCell_appname = [regex]::replace($startCell_appname, '(\$\w+\$)(\d+)', {param($m) $m.groups[1].value + ([int]$m.groups[2].value+1)})    
    $endCell_appname = $sh.cells.item(1,2).EntireColumn.find("").Address()
    $endCell_appname = [regex]::replace($endCell_appname, '(\$\w+\$)(\d+)', {param($m) $m.groups[1].value + ([int]$m.groups[2].value-1)}) 

    $rangeAddr=$startCell_appname + ":" + $endCell_appname
    $sh.Range($rangeAddr).Value2 |
      foreach {
        $apps = "" | Select appname,location,lob,os
        $apps.appname = $_ 
        $apps.location = ""   
        $apps.lob = $sh.Name
        $apps.os = "Windows 7"
        $apps
      } 
}) | Export-csv "apps.csv" -NoTypeInformation


Is This A Good Question/Topic? 0
  • +

Page 1 of 1