For loop within For loop PowerShell and Excel not working

Question

I am basically trying to compare a cell within Excel against another cell within another worksheet using PowerShell. This is the code I am using:

# Define location
$crs = "C:\temp\CRSENGCY_PS.xlsx"
$english = "English"
$welsh = "Welsh"
$SubSection = "SubSection"

# Create instance
$objExcel = New-Object -ComObject Excel.Application 
$workBook = $objExcel.Workbooks.Open($crs)
$englishSheet = $workBook.Worksheets.Item($english)
$subSectionSheet = $workBook.Worksheets.Item($SubSection)
$objExcel.Visible = $false

# Num of rows
$engRowMax = 1812
$subRowMax = 677

# Define columns 
$rowName, $colName = 1, 1

for ($i=1; $i -le $subRowMax; $i++) {
    $SubSectionName = $subSectionSheet.Cells.Item($rowName+$i,2).Text
    $3SubSections = $SubSectionName.Substring(0, 3)

    for ($i=1; $i -le $engRowMax; $i++) {
        $englishName = $englishSheet.Cells.Item($rowName+$i, $colName).Text
        $3englishName = $englishName.Substring(0, 3)

        if ($3englishName -eq $3SubSections) {
            Write-Host("Success")
        } else {
            Write-Host("Failed" + $3SubSections + " " + $3englishName)
        }
    }
}

$objExcel.Quit()

The issue I have is that the for loop at the bottom only runs once. The for loop inside runs the correct number of times. If I remove the nested for loop it works fine.

Do not re-use loop variables (in your case $i) in nested loops. Use a different variable for each loop. — Ansgar Wiechers
– Ansgar Wiechers, Commented Mar 12, 2018 at 21:17
Excellent, thanks Ansgar. If you put this as an answer i will mark as the correct. — bsod_
– bsod_, Commented Mar 12, 2018 at 21:39
I am for whatever reason now getting - Exception calling "Substring" with "2" argument(s): "Index and length must refer to a location within the string. Parameter name: length" — bsod_
– bsod_, Commented Mar 12, 2018 at 21:41
Figured it cheers, will post my finished script later for info — bsod_
– bsod_, Commented Mar 13, 2018 at 10:14

Dylan Hurst · Accepted Answer · 2018-03-12 21:37:20Z

0

I believe your problem is that the nested For loop doesn't end. You will probably want to put a Break statement in both For loops to tell them at what point you want them to exit the loops.

The following links may help explain more about a Break statement:

PowerShell Looping: Basics of the Break

Nested ForEach() in PowerShell

answered Mar 12, 2018 at 21:37

Dylan Hurst

339 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

bsod_ Over a year ago

Cheers Dylan, however Ansgar's comment put me in the right direction.

bsod_ · Accepted Answer · 2018-03-19 13:33:19Z

If anyone is interested here is my working script -

# Define location

$crs = "C:\temp\CRSENGCY_PS.xlsx"
$english = "English"
$welsh = "Welsh"
$SubSection = "SubSection"

# Create instance

$objExcel = New-Object -ComObject Excel.Application 
$workBook = $objExcel.Workbooks.Open($crs)
$englishSheet = $workBook.Worksheets.Item($english)
$welshSheet = $workBook.Worksheets.Item($welsh)
$subSectionSheet = $workBook.Worksheets.Item($SubSection)
$objExcel.Visible=$false

# Num of rows

$engRowMax = 1812
$subRowMax = 677

# Define columns 

$rowName,$colName = 2,1

# Vars

$engSubID = $englishSheet.Cells.Item($rowName+$s,8)
$welSubID = $welshSheet.Cells.Item($rowName+$s,8)


for ($i=0; $i -le 339; $i++)
{
    $SubSectionName = $subSectionSheet.Cells.Item($rowName+$i,2).text
    $SubSecID = $subSectionSheet.Cells.Item($rowName+$i,1).text
    #$3SubSections = $SubSectionName.Substring(0,3)
    $SubSecRef = $SubSectionName.Substring(0, $SubSectionName.IndexOf(' ')) 
    #Write-Host($SubSecRef)

        for ($s=1; $s -le 1811; $s++)
        {
            $englishName = $englishSheet.Cells.Item($rowName+$s,$colName).text
            #$3englishName = $englishName.Substring(0,$englishName.)
            $refno = $englishName.Substring(0, $englishName.IndexOf('.') + 1 + $englishName.Substring($englishName.IndexOf('.') + 1).IndexOf('.'))

            if ($SubSecRef -eq $refno)
            {
                $englishSheet.Cells.Item($rowName+$s,8) = $SubSecID
                Write-host("Match!!")
            }
            else 
            {
                #Write-host("No Match " + $3SubSections + " " + $3englishName)
            }

        }
        Write-Host($i)
}

$workBook.Save()
$workBook.Close()
$objExcel.Quit()

Collectives™ on Stack Overflow

For loop within For loop PowerShell and Excel not working

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related