We help IT Professionals succeed at work.

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

287 Views
Last Modified: 2014-08-31
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)?
Comment
Watch Question

CERTIFIED EXPERT
Top Expert 2014

Commented:
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

Author

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
CERTIFIED EXPERT
Top Expert 2014

Commented:
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

Author

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
CERTIFIED EXPERT
Top Expert 2014

Commented:
What result does it give you?

Author

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.
CERTIFIED EXPERT
Top Expert 2014

Commented:
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.

Author

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
CERTIFIED EXPERT
Top Expert 2014

Commented:
What is that second output from?  It's not from the exact command I put in my last post.

Author

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
CERTIFIED EXPERT
Top Expert 2014

Commented:
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.

Author

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

CERTIFIED EXPERT
Top Expert 2014
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
CERTIFIED EXPERT
Top Expert 2014

Commented:
Please update with any issues, or close the question.
Thanks!
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.