Print pdf files from folder, matching the numbers in the Excel column

nesher13
nesher13 used Ask the Experts™
on
Hi folks,

I need to write the script performs the following tasks:

there is a column of numbers in Excel . Column's length is not known beforehand. On the other hand there is the folder that contains the files of invoices in PDF format. it is necessary to print all existing files matching the numbers in the column and for those numbers that do not have corresponding the  pdf files, to display a message: "In the folder does not corresponding invoice number"
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Joe WinogradDeveloper
Fellow 2017
Most Valuable Expert 2018

Commented:
Is the entire file name just the invoice number or are there other characters in the file name? For example, if a cell in the Excel spreadsheet is:

123456

is the file name just

123456.pdf

or could it be

Invoice 123456.pdf

or inv_123456_20141109.pdf

or whatever? Regards, Joe

Author

Commented:
cell in the Excel spreadsheet is:

123456

the file name just

123456.pdf
Joe WinogradDeveloper
Fellow 2017
Most Valuable Expert 2018

Commented:
A few more questions:

(1) Is the Excel spreadsheet in the same folder as the PDF files?

(2) Do you already have a program installed that can print a PDF file via the command line? I recommend it to have a "quiet/no-UI" option so that the program doesn't appear for each file that is printed.

(3) Do you want to print to the default printer or be able to specify (by name) which printer to use?

(4) I see that your Tags and Topics for this question include PowerShell. Does the script have to be written in PowerShell or can it be in another scripting language?

Regards, Joe
Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

Author

Commented:
Joe Winograd

(1) no

(2)  I'm going to print pdf using command: Start-Process -FilePath $filepath :\Invoice.pdf"


(3)  I want to print to the default printer

(4)  The script have to be written in PowerShell

Regards, nesher13
Joe WinogradDeveloper
Fellow 2017
Most Valuable Expert 2018

Commented:
Sorry — if it must be in PowerShell, I can't help you. Regards, Joe

Author

Commented:
the question is no longer relevant
Joe WinogradDeveloper
Fellow 2017
Most Valuable Expert 2018

Commented:
Why?
Commented:
The is working script:
$ex = New-Object -ComObject Excel.Application
$ex.Visible = 1
$wb = $ex.Workbooks.Open("C:\Users\dov143\Desktop\Eli-column-new.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 += [pscustomobject]@{
		Invoices = $_.Value2
		Values = $val.Value2
	}
}
	
$result | Where {!$_.Values} | Foreach {Start-Process -FilePath "s:\Invoices-SD-from-documentum\0070\$($_.Invoices).pdf" –Verb Print}
$ex.Quit()

Open in new window

Author

Commented:
I've requested that this question be closed as follows:

Accepted answer: 0 points for nesher13's comment #a40466173

for the following reason:

the script is combination This script is a combination of data foundув from the Internet
Joe WinogradDeveloper
Fellow 2017
Most Valuable Expert 2018

Commented:
I know very little about PowerShell, but my programming background in other languages makes me wonder where that code displays the message "In the folder does not corresponding invoice number" for those numbers that do not have corresponding PDF files. I'd like to understand this. Thanks, Joe

Author

Commented:
I want to close the question
Joe WinogradDeveloper
Fellow 2017
Most Valuable Expert 2018

Commented:
That's fine. I did not object to your closing the question. I simply made another post in the hopes of understanding how that script solves the question you asked.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial