PowerShell Trying to compare .Length of $Variable in Excel Worksheets

Viewed 58

I am working on piece of a powershell script to interact with a directory of excel documents. I have a for each loop to loop through each of the files in a directory open them to then copy and paste the DataBodyRange of listobject(1) to my main document. As stated in the title my issue is as follows: The If($shippedVal.Length -gt 0) statement will not proceed. As if the $shippedVal actually contains no value. When I try to Write-Host $shippedVal the IDE stalls. Here is the code in question:

foreach($f in $clientPath){
    $clientFile = $f.FullName
    $clientWorkbook = $excel.Workbooks.open($clientFile)
    $clientWorksheet = $clientWorkbook.Worksheets
    Write-Host $clientFile
    foreach ($ws in $clientWorksheet)
    {
    Write-Host $ws.Name + "Attempting List Object Count"
        If($ws.ListObjects.Count -gt 0)
        {
        Write-Host "Made it to copy range"

            $clientLR = ($ws.UsedRange.Rows.Count +1)
            $shippedVal = $ws.ListObjects(1).DataBodyRange.Cells(1)
            If($shippedVal.Length -gt 0)
            {
            Write-Host "Made it to line 41"
                $shippedBreakdown = $ws.ListObjects(1).DataBodyRange
                $pasteLoc = $mainWorksheet.Range("A" + "&" + $mainLR)
                $shippedBreakdown.Copy() | out-null
                $mainWorksheet.Paste($pasteLoc)
                    
                $mainLR = ($mainWorksheet.UsedRange.Rows.Count + 1)
                Write-Host "Pasting DataBodyRange"
            }
        }
    }
    $clientWorkbook.Close
}

If you need any additional information please let me know.

0 Answers
Related