Print .PDF files using command start-procces in ascending order of their names (values)

Hi folks,

There is script

$ex = New-Object -ComObject Excel.Application
$ex.Visible = 1
$wb = $ex.Workbooks.Open("C:\users\143\Documents\Hoveret-3-Eli.xlsx")

$ws = $wb.Worksheets.Item(1)

$result = @()

[int]$rmax = $ws.Range("G65536").End(-4162).Row
$ws.Range("G3:G$rmax") | Where {$_.Value2} | Foreach {
      $col = $_.Column+1
      $row = $_.Row
      $val = $ws.Cells.Item($row,$col)
      
            $result += [psobject]@{      
            Invoices = [System.Convert]::ToString($_.Value2)
            Values = $val.Value2
      }
}

$result | Where {!$_.Values} | Foreach {Start-Process -FilePath "s:\INVOICES\0070\$($_.Invoices).pdf" –Verb Print }  
$ex.Quit()

how can I do so that the files are printed in ascending order of their names. (Eg 11.pdf, 13.pdf, 28.pdf)?
nesher13Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

footechCommented:
You can try the below, though it's possible you may encounter names that aren't sorted in a natural language fashion.
$result | Where {!$_.Values} | Sort -property Invoices | Foreach {Start-Process -FilePath "s:\INVOICES\0070\$($_.Invoices).pdf" –Verb Print } 

Open in new window

You could have an order of
1.pdf
11.pdf
2.pdf

The solution to this is zero-padding (could also use spaces, or maybe some other characters).
$result | Where {!$_.Values} | Sort -property {($_.Invoices).PadLeft(20,"0")} | Foreach {Start-Process -FilePath "s:\INVOICES\0070\$($_.Invoices).pdf" –Verb Print } 

Open in new window

0
nesher13Author Commented:
footech,

many thanks for you solution, but it does not completely solve my problem

I found out: if in the column of Excel the are numbers invoices, for which there is no corresponding pdf files in the folder, then in ,  I am receive error messages and sorting is not working.
I was very grateful to you if you can give a final decision
0
footechCommented:
Well, this is an issue that would have existed in your original code and is independent of your original question.  I'm assuming that the error messages are from the Start-Process command, but I'm not seeing how this would impact the sorting as that's already been done.  You can avoid the error messages by testing the path first in order to filter what's passed along to the Start-Process command, or you could use a try-catch statement to catch the errors that come from submitting the invalid path.  Here's an example of the former.
$result | Where {!$_.Values} | Sort -property {($_.Invoices).PadLeft(20,"0")} | Foreach `
{
    If (Test-Path "s:\INVOICES\0070\$($_.Invoices).pdf")
    {
        Start-Process -FilePath "s:\INVOICES\0070\$($_.Invoices).pdf" –Verb Print
    }
}

Open in new window

0
Problems using Powershell and Active Directory?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

nesher13Author Commented:
footech

Here is the modified script:

Open in new window

$ex = New-Object -ComObject Excel.Application
$ex.Visible = 1
$wb = $ex.Workbooks.Open("C:\users\dov143\Documents\Eli-column.xlsx")
$ws = $wb.Worksheets.Item(1)


$result=@()

[int]$rmax = $ws.Range("G65536").End(-4162).row

$ws.Range("G1:G$rmax") | Where {$_.Value2} | Foreach {
      $col = $_.Column+1
      $row = $_.Row
      $val = $ws.Cells.Item($row,$col)

      
            $result += [psobject]@{      
            
            Invoices = [System.Convert]::ToString($_.Value2)
            Values = $val.Value2
      }          
}
                
      $result | Where {!$_.Values} | Sort -property {($_.Invoices).PadLeft(20,"0")} | Foreach `
{
    If (Test-Path "s:\INVOICES\0070\$($_.Invoices).pdf")
   {
       Start-Process -FilePath "s:\INVOICES\0070\$($_.Invoices).pdf" –Verb Print
  }
}

$ex.Quit()

Open in new window


but it does not give the correct result
0
footechCommented:
What result does it give you?
0
nesher13Author Commented:
footech

While I write $result.invoices

I get on the console correct order numbers invoices.

While I run the pipeline, I think the command start-process broke the correct order output files.
0
footechCommented:
I don't understand what you're trying to describe.
You should compare the output from
$result.invoices
$result | Where {!$_.Values} | Sort -property {($_.Invoices).PadLeft(20,"0")} | Foreach {$_.Invoices}

If possible, post the output of both and point out exactly where you think the problem is.
0
nesher13Author Commented:
footech

$result.invoices

137125
140788
142625
142629
143114
143152
143153
143161
143328
143435

Pipeline  with sort output:

142629.pdf
142625.pdf
143328.pdf
143435.pdf
143161.pdf
143153.pdf
143152.pdf
143114.pdf
0
footechCommented:
What is that second output from?  It's not from the exact command I put in my last post.
0
nesher13Author Commented:
You are right, the last command will output numbers invoices (from Excel file) in the correct order. My task: to print the files pdf (invoices), corresponding numers of the invoices from the folder, and display a message, if the folder does not contain the corresponding files pdf
0
footechCommented:
So we know the results are being sorted.  If they are not printing out in the same order then perhaps it has something to do with how fast a file is processed for printing (some might take longer than others).  You might try adding a command like
Start-Sleep -seconds 3
after the Start-Process command (but still in the ForEach block).  You may have to play around with how much a delay to use.  Another thing you could try is using the -Wait parameter of Start-Process.
0
nesher13Author Commented:
footech,

If I understand you correctly then the code:

should look like this:


Open in new window

$result | Where {!$_.Values} | Sort -property {($_.Invoices).PadLeft(20,"0")} | Foreach {$_.Invoices}

 {  If (Test-Path ("s:\INVOICES\0070\$($_.Invoices).pdf")
      
   { Start-Process -FilePath "s:\INVOICES\0070\$($_.Invoices).pdf" –Verb Print | Start-Sleep -seconds 3 }}

Open in new window


or as follows:


Open in new window


Open in new window

$result | Where {!$_.Values} | Sort -property {($_.Invoices).PadLeft(20,"0")} | Foreach {$_.Invoices}

 {  If (Test-Path ("s:\INVOICES\0070\$($_.Invoices).pdf")
      
   { Start-Process -wait -FilePath "s:\INVOICES\0070\$($_.Invoices).pdf" –Verb Print  }

Open in new window

0
footechCommented:
The first one would be like
$result | Where {!$_.Values} | Sort -property {($_.Invoices).PadLeft(20,"0")} | Foreach `
{
    If (Test-Path "s:\INVOICES\0070\$($_.Invoices).pdf")
    {
        Start-Process -FilePath "s:\INVOICES\0070\$($_.Invoices).pdf" –Verb Print
        Start-Sleep -seconds 3
    }
}

Open in new window


The second one is correct.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
footechCommented:
Please update with any issues, or close the question.
Thanks!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Powershell

From novice to tech pro — start learning today.