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.