Solved

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

Posted on 2014-07-22
18
1,106 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
  • 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
 

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
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
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

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Utilizing an array to gracefully append to a list of EmailAddresses
I thought I'd write this up for anyone who has a request to create an anonymous whistle-blower-type submission form created using SharePoint 2010 (this would probably work the same for 2013). It's not 100% fool-proof but it's as close as you can get…
Learn the basics of if, else, and elif statements in Python 2.7. Use "if" statements to test a specified condition.: The structure of an if statement is as follows: (CODE) Use "else" statements to allow the execution of an alternative, if the …
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

707 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now