none
Nested loop how

    Question

  • Below code brings 1 - A, 2 - A, 3 - A, 4 - A. I want to put another nested loop so that bring all the infomation. In vb script I put For Next loop. How do i do it in powershell? Please help

    $xl = New-Object -COM "Excel.Application"
    $xl.Visible = $true
    $xl.DisplayAlerts = $False
    $wb = $xl.Workbooks.Open("C:\temp\asif.xlsx")
    $ws = $wb.Sheets.Item(1)
    $cols=$ws.UsedRange.Columns.Count
    write-host "Cols=$cols"
    $rows = $ws.UsedRange.Rows.Count
    Write-Output "Rows=$rows"
    $RowNum = 1
    $ColNum=1

     

    while ($ws.Cells.Item($RowNum, $ColNum).Text -ne "")
        {
        $ws.Cells.Item($RowNum, $ColNum).Text
        $RowNum++
           
        }

    $wb.Close()
    $xl.Quit()

    Thursday, September 26, 2013 8:41 PM

Answers

  • Hi,

    one option is to use a nested for loop:

    $xl = New-Object -COM "Excel.Application"
    $xl.Visible = $true
    $xl.DisplayAlerts = $False
    $wb = $xl.Workbooks.Open("C:\temp\asif.xlsx")
    $ws = $wb.Sheets.Item(1)
    $cols=$ws.UsedRange.Columns.Count
    write-host "Cols=$cols"
    $rows = $ws.UsedRange.Rows.Count
    Write-Output "Rows=$rows"
    for($col=1;$col -le $cols;$col++){
    	for($row=1;$row -le $rows;$row++){
    		$ws.Cells.Item($row, $col).Text
    	}
    }
    $wb.Close()
    $xl.Quit()
    #release com objects (otherwise it will leave a running excel process behind)
    [void][System.Runtime.Interopservices.Marshal]::FinalReleaseComObject($wb)
    [void][System.Runtime.Interopservices.Marshal]::FinalReleaseComObject($xl)


    • Proposed as answer by Mike Laughlin Thursday, September 26, 2013 11:22 PM
    • Edited by Dirk_74 Thursday, September 26, 2013 11:30 PM
    • Marked as answer by asif300 Friday, September 27, 2013 12:03 AM
    Thursday, September 26, 2013 11:00 PM