nesher13
asked on
How can I print out automatically several pdf files in defined folder with powershell ?
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.
$ex.Visible = 1
$wb = $ex.Workbooks.Open("C:\use rs\dov143\ Documents\ Template.x lsx")
$ws = $wb.Worksheets.Item(1)
$result = @()
[int]$rmax = $ws.Range("G65536").End(-4 162).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:\invoi ces\0070\" +($_.Value 2)+".pdf")
Values = $val.Value2
}
}
$result | Where {!$_.Values} | Foreach-Object { Start-Process -FilePath s:\INVOICES\0070\*.pdf –Verb Print}
$ex.Quit()
specified.
At C:\PowerShell\Scripts\Invo ices-pdf.p s1: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
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.
$ex = New-Object -ComObject Excel.Application$ex.Visible = 1
$wb = $ex.Workbooks.Open("C:\use
$ws = $wb.Worksheets.Item(1)
$result = @()
[int]$rmax = $ws.Range("G65536").End(-4
$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
Values = $val.Value2
}
}
$result | Where {!$_.Values} | Foreach-Object { Start-Process -FilePath s:\INVOICES\0070\*.pdf –Verb Print}
$ex.Quit()
Start-Process : This command cannot be run due to the error: The system cannot find the file specified.
At C:\PowerShell\Scripts\Invo
+ $result | Where {!$_.Values} | Foreach-Object { Start-Process -FilePath s:\INVOI ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : InvalidOperation: (:) [Start-Process], InvalidOperationExcepti
on
+ FullyQualifiedErrorId : InvalidOperationException,
rocessCommand
ASKER
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?
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?
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
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
ASKER
Justin Yeung
I followed your advice and put the cmdlet set-location s:\
in the beginning of the script. That looks like now:
$ex.Visible = 1
$wb = $ex.Workbooks.Open("C:\use rs\dov143\ Documents\ Hoveret-3- Eli.xlsx") #Template.xlsx")
$ws = $wb.Worksheets.Item(1)
Set-Location s:\
$result = @()
[int]$rmax = $ws.Range("G65536").End(-4 162).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()
I also enclose an error message :
the parameters that take pipeline input.
At C:\PowerShell\Scripts\Invo ices-pdf.p s1:25 char:58
+ $result | Where {!$_.Values} | Foreach {$_.Invoices} | Copy-Item c:\Users\dov1 ...
+ ~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : InvalidArgument: (סה"כ שולם.pdf:String) [Copy-Item], Parameter
BindingException
+ FullyQualifiedErrorId : InputObjectNotBound,Micros oft.PowerS hell.Comma nds.CopyIt emComm
and
I followed your advice and put the cmdlet set-location s:\
in the beginning of the script. That looks like now:
$ex = New-Object -ComObject Excel.Application$ex.Visible = 1
$wb = $ex.Workbooks.Open("C:\use
$ws = $wb.Worksheets.Item(1)
Set-Location s:\
$result = @()
[int]$rmax = $ws.Range("G65536").End(-4
$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
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()
I also enclose an error message :
the command does not take pipeline input or the input and its properties do not match any ofthe parameters that take pipeline input.
At C:\PowerShell\Scripts\Invo
+ $result | Where {!$_.Values} | Foreach {$_.Invoices} | Copy-Item c:\Users\dov1 ...
+ ~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : InvalidArgument: (סה"כ שולם.pdf:String) [Copy-Item], Parameter
BindingException
+ FullyQualifiedErrorId : InputObjectNotBound,Micros
and
where is the document actually converted to?
I can see that you changed your destination with the script provided previously.
I can see that you changed your destination with the script provided previously.
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\Invo ices-pdf.p s1: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
the parameters that take pipeline input.
At C:\PowerShell\Scripts\Invo
+ $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
ASKER
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.Automati on.PSCusto mObject
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
..........................
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.Automati
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
$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
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
ASKER
Justin Yeung
the script still does not work
.......................... .......... .......... .......... .......... .......... .......... .......... ...
$result = @()
[int]$rmax = $ws.Range("G65536").End(-4 162).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\Invo ices-pdf.p s1:24 char:57
+ $result | Where {!$_.Values} | Foreach {$_.Invoices | Copy-Item c:\Users\dov14 ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : InvalidArgument: (7141157.pdf:String) [Copy-Item], ParameterBi
ndingException
+ FullyQualifiedErrorId : InputObjectNotBound,Micros oft.PowerS hell.Comma nds.CopyIt emComm
and
.......................... .......... .....
the script still does not work
..........................
$result = @()
[int]$rmax = $ws.Range("G65536").End(-4
$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
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\Invo
+ $result | Where {!$_.Values} | Foreach {$_.Invoices | Copy-Item c:\Users\dov14 ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : InvalidArgument: (7141157.pdf:String) [Copy-Item], ParameterBi
ndingException
+ FullyQualifiedErrorId : InputObjectNotBound,Micros
and
..........................
$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.
what's the result of the $_.invoices?
it doesn't seem like it can pass the value to the pipeline.
ASKER
Justin Yeung
it displays a list of pdf files (Invoices)
it displays a list of pdf files (Invoices)
$_.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\ $($_.invoi ces) -Destination D:\dov2}
$result | Where {!$_.Values} | Foreach {Copy-Item c:\Users\dov143\Documents\
ASKER
Justin Yeung
Copy-Item : Cannot find path 'C:\Users\dov143\Documents \test1.pdf ' because it does not exist
At C:\PowerShell\Scripts\Invo ices-pdf.p s1:30 char:42
Copy-Item : Cannot find path 'C:\Users\dov143\Documents \test2.pdf ' because it does not exist
At C:\PowerShell\Scripts\Invo ices-pdf.p s1:30 char:42
Copy-Item : Cannot find path 'C:\Users\dov143\Documents
At C:\PowerShell\Scripts\Invo
Copy-Item : Cannot find path 'C:\Users\dov143\Documents
At C:\PowerShell\Scripts\Invo
where is the actual file located after it coverted to PDF?
ASKER
Hi folks
The solution:
$ex.Visible = 1
$wb = $ex.Workbooks.Open("C:\use rs\dov143\ Documents\ Template.x lsx")
$ws = $wb.Worksheets.Item(1)
$result = @()
[int]$rmax = $ws.Range("G65536").End(-4 162).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()
The solution:
$ex = New-Object -ComObject Excel.Application$ex.Visible = 1
$wb = $ex.Workbooks.Open("C:\use
$ws = $wb.Worksheets.Item(1)
$result = @()
[int]$rmax = $ws.Range("G65536").End(-4
$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()
$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.
I think it will just list all the invoices with your command.........
but it is not going to do anything.
ASKER
Justin Yeung
The script is working
Where
The script is working
Where
{!$_.Values}is OK
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
specified.
At C:\PowerShell\Scripts\Invo
+ $result | Where {!$_.Values} | Foreach-Object { Start-Process -FilePath s:\INVOI ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : InvalidOperation: (:) [Start-Process], InvalidOperationExcepti
on
+ FullyQualifiedErrorId : InvalidOperationException,
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:\