Solved

How can I print out automatically several pdf files in defined folder with powershell ?

Posted on 2014-07-22
18
1,202 Views
Last Modified: 2014-07-30
Hi folks,

There is a script that displays the numbers of some the invoices from Excel 2010 workbook. how can I print these invoices are stored in PDF format in a folder. I am attach the script with one of the many attempts. and the error message.



Open in new window

$ex = New-Object -ComObject Excel.Application
$ex.Visible = 1
$wb = $ex.Workbooks.Open("C:\users\dov143\Documents\Template.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 += [pscustomobject]@{
      
      Invoices = [System.Convert]::ToString("S:\invoices\0070\"+($_.Value2)+".pdf")
            Values = $val.Value2
            
      }
}
      
$result | Where {!$_.Values} | Foreach-Object { Start-Process -FilePath s:\INVOICES\0070\*.pdf –Verb Print}

 
$ex.Quit()

Open in new window





Open in new window

Start-Process : This command cannot be run due to the error: The system cannot find the file
specified.
At C:\PowerShell\Scripts\Invoices-pdf.ps1:25 char:49
+ $result | Where {!$_.Values} | Foreach-Object { Start-Process -FilePath s:\INVOI ...
+                                                 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidOperation: (:) [Start-Process], InvalidOperationExcepti
   on
    + FullyQualifiedErrorId : InvalidOperationException,Microsoft.PowerShell.Commands.StartP
   rocessCommand

Open in new window

0
Comment
Question by:nesher13
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 10
  • 8
18 Comments
 
LVL 14

Expert Comment

by:Justin Yeung
ID: 40211608
Start-Process : This command cannot be run due to the error: The system cannot find the file
 specified.
 At C:\PowerShell\Scripts\Invoices-pdf.ps1:25 char:49
 + $result | Where {!$_.Values} | Foreach-Object { Start-Process -FilePath s:\INVOI ...
 +                                                 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
     + CategoryInfo          : InvalidOperation: (:) [Start-Process], InvalidOperationExcepti
    on
     + FullyQualifiedErrorId : InvalidOperationException,Microsoft.PowerShell.Commands.StartP
    rocessCommand

if you look at the error it is trying to print the document at c:\powershell\scripts\ folder which doesn't exist.

you can simply do a set-location before "Start-Process -FilePath s:\INVOICES\0070\*.pdf –Verb Print"

set-location s:\
0
 

Author Comment

by:nesher13
ID: 40211697
Justin Yeung

1.In line begining Sresult I made a mistake. line looked like:
 $result | Where {!$_.Values} | Foreach {$_.Invoices} | Start-Process -FilePath s:\INVOICES\0070\*.pdf –Verb Print

2.I do not quite understand where I have to insert: s:\ or s:\invoices\0070, in the pipeline?
0
 
LVL 14

Expert Comment

by:Justin Yeung
ID: 40211752
since the script looks like it is running locally on the machine.

you can set-location even at the beginning of the script.

set-location S:\

basically when you start powershell, it prompted a current location as you see c:\users\xxxx>

which powershell assume all value are store on that location unless you change it by using set-location
0
Is Your Team Achieving Their Full Potential?

74% of employees feel they are not achieving their full potential. With Linux Academy, not only will you strengthen your team's core competencies but also their knowledge of of the newest IT topics.

With new material every week, we'll make sure that you stay ahead of the game.

 

Author Comment

by:nesher13
ID: 40214137
Justin Yeung

I followed your advice and put the cmdlet set-location s:\
in the beginning of the script. That looks like now:


Open in new window

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

Set-Location s:\

$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 += [pscustomobject]@{
            Invoices = [System.Convert]::ToString($_.Value2)+".pdf"
            Values = $val.Value2
      }
}
      $result | Where {!$_.Values} | Foreach {$_.Invoices} |  Copy-Item c:\Users\dov143\Documents -Destination D:\dov2
   
   #Dir D:\dov2\*.pdf | Foreach-Object { Start-Process -FilePath $_.FullName –Verb Print }
   
$ex.Quit()

Open in new window


I also enclose an error message :


Open in new window

the command does not take pipeline input or the input and its properties do not match any of
 the parameters that take pipeline input.
At C:\PowerShell\Scripts\Invoices-pdf.ps1:25 char:58
+     $result | Where {!$_.Values} | Foreach {$_.Invoices} |  Copy-Item c:\Users\dov1 ...
+                                                             ~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidArgument: (סה"כ שולם.pdf:String) [Copy-Item], Parameter
   BindingException
    + FullyQualifiedErrorId : InputObjectNotBound,Microsoft.PowerShell.Commands.CopyItemComm
   and

Open in new window

0
 
LVL 14

Expert Comment

by:Justin Yeung
ID: 40214715
where is the document actually converted to?

I can see that you changed your destination with the script provided previously.
0
 
LVL 14

Expert Comment

by:Justin Yeung
ID: 40214720
the command does not take pipeline input or the input and its properties do not match any of
  the parameters that take pipeline input.
 At C:\PowerShell\Scripts\Invoices-pdf.ps1:25 char:58
 +     $result | Where {!$_.Values} | Foreach {$_.Invoices} |  Copy-Item c:\Users\dov1 ...

it looks like there isn't any properties related to Inovices

can you do a get-member of

 $result | Get-Member
0
 

Author Comment

by:nesher13
ID: 40215136
Justin Yeung
............................
1.  misspeling  Dir D:\dov2\*.pdf | Foreach-Object { Start-Process -FilePath $_.FullName –Verb Print } instead
# Dir D:\dov2\*.pdf | Foreach-Object { Start-Process -FilePath $_.FullName –Verb Print }

 TypeName: System.Management.Automation.PSCustomObject

2
PS S:\> $result | gm
Name        MemberType   Definition                        
----        ----------   ----------                        
Equals               Method             bool Equals(System.Object obj)    
GetHashCode Method       int GetHashCode()                
GetType           Method       type GetType()                    
ToString           Method       string ToString()                
Invoices           NoteProperty System.String Invoices=7137616.pdf
Values              NoteProperty System.Double Values=188949.27
0
 
LVL 14

Expert Comment

by:Justin Yeung
ID: 40215587
$result | Where {!$_.Values} | Foreach {$_.Invoices | Copy-Item c:\Users\dov1 ...}

The pipeline should be within the $_.invoices...... it is doing it for each object.

you don't need the set-location for the script and it should work fine
0
 

Author Comment

by:nesher13
ID: 40216008
Justin Yeung

the script still does not work
...................................................................................................
$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 += [pscustomobject]@{
      
      Invoices = [System.Convert]::ToString($_.Value2)+".pdf"
            Values = $val.Value2
            
       }
      }
$result | Where {!$_.Values} | Foreach {$_.Invoices |  Copy-Item c:\Users\dov143\Documents -Destination D:\dov2}
.......................................................................................................................................................
Copy-Item : The input object cannot be bound to any parameters for the command either because
 the command does not take pipeline input or the input and its properties do not match any of
 the parameters that take pipeline input.
At C:\PowerShell\Scripts\Invoices-pdf.ps1:24 char:57
+     $result | Where {!$_.Values} | Foreach {$_.Invoices |  Copy-Item c:\Users\dov14 ...
+                                                            ~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidArgument: (7141157.pdf:String) [Copy-Item], ParameterBi
   ndingException
    + FullyQualifiedErrorId : InputObjectNotBound,Microsoft.PowerShell.Commands.CopyItemComm
   and

.........................................
0
 
LVL 14

Expert Comment

by:Justin Yeung
ID: 40216995
$result | Where {!$_.Values} | Foreach {write-host $_.Invoices}

what's the result of the $_.invoices?

it doesn't seem like it can pass the value to the pipeline.
0
 

Author Comment

by:nesher13
ID: 40218538
Justin Yeung

it displays a list of pdf files (Invoices)
0
 
LVL 14

Expert Comment

by:Justin Yeung
ID: 40218548
$_.invoices should be the actualy file name like "something.pdf" so then it doesn't need to pass to pipeline since nothing to pass.

$result | Where {!$_.Values} | Foreach {Copy-Item c:\Users\dov143\Documents\$($_.invoices) -Destination D:\dov2}
0
 

Author Comment

by:nesher13
ID: 40218554
Justin Yeung

Copy-Item : Cannot find path 'C:\Users\dov143\Documents\test1.pdf' because it does not exist

At C:\PowerShell\Scripts\Invoices-pdf.ps1:30 char:42

Copy-Item : Cannot find path 'C:\Users\dov143\Documents\test2.pdf' because it does not exist

At C:\PowerShell\Scripts\Invoices-pdf.ps1:30 char:42
0
 
LVL 14

Expert Comment

by:Justin Yeung
ID: 40218567
where is the actual file located after it coverted to PDF?
0
 

Author Comment

by:nesher13
ID: 40230990
Hi folks

The solution:


Open in new window

$ex = New-Object -ComObject Excel.Application
$ex.Visible = 1
$wb = $ex.Workbooks.Open("C:\users\dov143\Documents\Template.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 += [pscustomobject]@{
            Invoices = $_.Value2
            Values = $val.Value2
      }
}
      
$result | Where {!$_.Values} | Foreach {$_.Invoices}

$ex.Quit()

Open in new window

0
 
LVL 14

Expert Comment

by:Justin Yeung
ID: 40230992
$result | Where {!$_.Values} | Foreach {$_.Invoices}

I think it will just list all the invoices with your command.........

but it is not going to do anything.
0
 

Author Comment

by:nesher13
ID: 40231003
Justin Yeung

The script is working

 Where
{!$_.Values}
is OK
0
 
LVL 14

Accepted Solution

by:
Justin Yeung earned 500 total points
ID: 40231016
glad that you have it resolved.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to prepare an HTML email signature template file containing dynamic placeholders for users' Azure AD data. Furthermore, it explains how to use this file to remotely set up a department-wide email signature policy in Office …
The Windows functions GetTickCount and timeGetTime retrieve the number of milliseconds since the system was started. However, the value is stored in a DWORD, which means that it wraps around to zero every 49.7 days. This article shows how to solve t…
Learn the basics of lists in Python. Lists, as their name suggests, are a means for ordering and storing values. : Lists are declared using brackets; for example: t = [1, 2, 3]: Lists may contain a mix of data types; for example: t = ['string', 1, T…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…

717 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question