Print External PDF Files from Excel Sheet

Topic

New Reply

Hi,
Hope some can help me.
I have a list of file locations of .pdf files in column A of my spreadsheet. All the path and files have info on Sheet1.
i.e. Column A1 c:testpacking_1.pdf A2 c:testcountry_1.pdf
Can I use VBA to, when I run it, print all of the files in the list in column A1 to D20 ?
Thanks in advance.

Reply | Quote

Replies

Hi foncesa

Welcome to the Lounge!

Yes, you can use vba to do what you want. Probably.
I tried this code on my laptop, and it printed my test pdf files to my default printer.
It uses the Adobe Reader to do the printing:

Code:

Sub printPDFfiles()

'ChDrive "E"        '<< if your files are on a different drive!

'NOTE:Example below uses Adobe Reader, but apply to Acrobat as well.
'If you are using Acrobat, substitute Acrobat.exe in place of AcroRd32.exe
'on the command line.

'NOTE:
'/s=don't show splash screen
'/n=new instance
'/h=minimised window
'/t=print to default printer; or use /t    

'CHECK YOUR ADOBE READER VERSION, AND USE CORRECT PATH..
'zProg = "C:Program Files (x86)AdobeReader 10.0ReaderAcroRd32.exe"
zProg = "C:Program Files (x86)AdobeReader 11.0ReaderAcroRd32.exe"

zLastRow = [a65536].End(xlUp).Row           'find last row in column [A]; e.g. 15
temp = "a1:a" & zLastRow                    'e.g. "a1:a15"

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
For Each cell In Range(temp)                'loop through all entries in range
zFile = cell.Value                          'fetch filename from cell

If zFile Like "*.pdf" Then                  'check it is a pdf file type
Shell (zProg & " /n /h /t " & zFile)        'execute command to print  the pdf document
End If                                      'end of test for pdf file type

Next                                        'process nect file in list
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

End Sub

Note that after printing all the files, it leaves the Adobe Reader open.
I don’t know how to close it.
Perhaps someone here can tell us that bit.
Meanwhile, you can close the Adobe session manually.

Hope this helps.
I have attached a sample file containing this code.

Let me know if this works for you.

zeddy

Reply | Quote

Let me know if this works for you.

zeddy

Thanks zeddy for reply, I tried on network printer but was unsuccessful to print, I have Windows 7, Office 2013.

Then i tried to test it from the command prompt “C:Program FilesAdobeReader 11.0ReaderAcroRd32.exe” / n/ h/ t “C:1.pdf” “HP LaserJet Professional M1213nf MFP” and it printed the document.

The printer is HP LaserJet Professional M1213nf MFP with IP_192.168.1.40
Does it require to add any references in VBA Editor.
Please help to sort it, Thanks in advance.

Reply | Quote

Hi foncesa

Thanks for getting back.

..if you look at my NOTE: in post#1:
‘/t=print to default printer; or use /t

So, now that I know the name of your network printer, I amended the vba code to include this.
See attached file.

Please let me know if that works. I think it should!

zeddy

Reply | Quote

Hi Zeddy,

I tried & finally with this [ Shell “””” & zProg & “””/n /t “”” & zFile & “””” ] i got the success, Credit goes to you. Thankyou.
I want to trouble you, Is there any way to have a popup asking which printer do you want to print, we have 2 printers on network.

Once again thanks a lot.

Reply | Quote

Hi foncesa

In my previous attached file rz2-print-pdf-files.xlsm I think I should have left a space between the filename and printername in the Shell command.
see attached new file.

Note: When you use the Shell command, you can either use it like this with brackets Shell (xxx)
e.g.
Shell (command)
..or like this
Shell (zProg & ” /n /h /t ” & zFile)
or like this without brackets Shell “command”
..so that is why you had to use the double sets of quotes etc etc.

If you let me know the name of your second network printer, I could give another update.

zeddy

Reply | Quote

Thanks Zeddy for explantion, you are very helpful person, God Bless You.

The second printer on network HP LaserJet P1106.

Thanks

Reply | Quote

Hi foncesa

See attached file.

This file, when opened with macros enabled, will put the name of your default Excel printer into a named cell [chosenPrinter]
This is always done on startup, via the Workbook_Open event.

The default Excel printer may vary, depending on what computer you are on.
The names of available printers will also vary, depending on what network computer you are on.

A Printer Selection button has been provided, which will allow you to select from the available printers for that computer.
The selected printer name will be placed in the named cell [chosenPrinter]

A button has been provided to start printing the pdf files listed in column [A]
A message box is displayed telling you how many files will be printed, and what printer will be used.
You can cancel this, and change the required Printer if you want to.

I have modifed the code to allow for any spaces in the folder names for the pdf files.
(For example, C:My DocumentsMy foldersample1.pdf)
The folders containing the pdf files are assumed to be on drive C:

Please let me know if this works.

zeddy

Reply | Quote

Thanks Zeddy, I appreciate your efforts for me, Thanks a lot.
A small request to you, I have lots of printing and if send all of them to printer in one shot as the script does the printers stops printing because of lots of job or the job is lost over network. I want an pause of 2 minutes after 5 rows of column A, and continue for next 5 then pause to continue till end.

With folded hands thankyou very much.

Reply | Quote

Hi foncesa

Did the last attached file work OK?

Yes, a pause can be put in the routine.
Perhaps one of our helpers in this forum would like to have a go at this?

I will check back if you don’t get a response.

zeddy

Reply | Quote

Hi foncesa

..I see there hasn’t been any other replies.

So, in the attached file, I have added a two-minute pause after every five pdf files have been sent to the printer.
I also included a progress message (in the bottom statusbar) and a completion message when finished.

Please let me know if this works OK.

zeddy

Reply | Quote

Hi foncesa

..I see there hasn’t been any other replies.

So, in the attached file, I have added a two-minute pause after every five pdf files have been sent to the printer.
I also included a progress message (in the bottom statusbar) and a completion message when finished.

Please let me know if this works OK.

zeddy

Hi,

How to enter print Copies no ….???

Reply | Quote

Hi

I have attached my latest version of a tool to print pdf documents.
This tool allows you to select a folder for pdf files, or to manually search and choose pdf files for printing.
You can choose which printer to use.
The list of your available printers is generated automatically.
You can choose which printer to use.
You can also look at the vba code if you want to specify a specific network printer on a particular port address.

After creating the list of pdf files to print, you can specify how many copies you want to print for each of the pdf files by entering the number required for each pdf print alongside in column .

So if you want 4 copies of a particular pdf, enter 4 in the adjacent cell.

This version attached has some minor updates to the vba coding comments.

zeddy

Reply | Quote

Hi

I have attached my latest version of a tool to print pdf documents.
This tool allows you to select a folder for pdf files, or to manually search and choose pdf files for printing.
You can choose which printer to use.
The list of your available printers is generated automatically.
You can choose which printer to use.
You can also look at the vba code if you want to specify a specific network printer on a particular port address.

After creating the list of pdf files to print, you can specify how many copies you want to print for each of the pdf files by entering the number required for each pdf print alongside in column .

So if you want 4 copies of a particular pdf, enter 4 in the adjacent cell.

This version attached has some minor updates to the vba coding comments.

zeddy

Hi zeddy,

your file was so helpful, many thanks

I have 2 problem,

1.the pdf file that I would like to print is using A3 paper,
how can I change it to A4 automatically?

2.how to close the pdf application automatically after print? or maybe you can make it not to open the pdf application at all before printing

Thank you

Reply | Quote

Hi

Welcome to the Lounge as a new poster!

If your pdf document is created as an A3 layout document, (for example it contains spec. drawings of turbo laser canon engines etc etc ) then you can have the printer scale the output to fit on A4 paper. In the file I posted in post#40, click the button in cell [G1] on the [Main] sheet, and then click the [Setup..] button on the displayed Printer Setup dialog. Click the [Effects] tab, and then, in the section labelled Resizing Options click the option button next to Print Document On and then choose your required paper size from the dropdown, and then tick the checkbox option for [ ]Scale to Fit.
Note that if this is a network printer, you may need to have appropriate permissions to change printer settings.

I will have a look at closing the pdf application after the final pdf print, and will post back here after I test it.

zeddy

Reply | Quote

Hi Zeddy,

I’m looking for some help to modify your code. Currently I scan a bar code into column G (starting with G2, then G3 and work my way down) In column J the page number of the page I’d like printed from the PDF I have populates. I’d like the code to print the Page number in the last cell of J every time a value is added to G. (I can also work with splitting the PDF into separate documents if this is a must, so then the page number from J would just be the last part of the document name. IE. “Box Label 61” as the document name rather then page 61 of the document “Box Label”) Please let me know If I can explain any parts better.

Thanks

Reply | Quote

Hi zeddy,

Your code works perfectly as many others have stated. Thanks so much! However I’m trying to print some PDFs with your code in color at my office, and for that to work, normally with adobe reader, after selecting File –> Print and selecting printer’s name, I have to click properties, then a box will pop up. I then have to click quality, uncheck “Print in black and white”. Now with the file you attached here, rz4-print-pdf-files.xlsm, it allows me to select the printer at my office, the same box will pop up when I click set up, but it doesn’t print in color even when I uncheck “Print in black and white”. Any tips on how to fix this?

I’ve also read the comment you made about making a “new printer”. I don’t think I can do that at my office, or even knows how one would achieve such thing. Any help is appreciated.

Thanks zeddy!

Hi foncesa

See attached file.

This file, when opened with macros enabled, will put the name of your default Excel printer into a named cell [chosenPrinter]
This is always done on startup, via the Workbook_Open event.

The default Excel printer may vary, depending on what computer you are on.
The names of available printers will also vary, depending on what network computer you are on.

A Printer Selection button has been provided, which will allow you to select from the available printers for that computer.
The selected printer name will be placed in the named cell [chosenPrinter]

A button has been provided to start printing the pdf files listed in column [A]
A message box is displayed telling you how many files will be printed, and what printer will be used.
You can cancel this, and change the required Printer if you want to.

I have modifed the code to allow for any spaces in the folder names for the pdf files.
(For example, C:My DocumentsMy foldersample1.pdf)
The folders containing the pdf files are assumed to be on drive C:

Please let me know if this works.

zeddy

Reply | Quote

Hi there,

If you need to get rid of the residual Adobe Reader window, here’s the code:

Code:

‘*************************
‘ KILL Adobe Acrobat Reader…
‘*************************
tmpBat = Environ(“Temp”) & Application.PathSeparator & “tmp.Bat”
Open tmpBat For Output As #1
Print #1, “@Echo Off”
Print #1, “Set ImageName=AcroRd32.Exe”
Print #1, “:Loop”
Print #1, “TaskList /FI “”ImageName EQ %ImageName%”” | Find /I “”%ImageName%”” > Nul”
Print #1, “If Not ErrorLevel 1 (TaskKill /F /IM “”%ImageName%”” > Nul & GoTo :Loop)”
Print #1, “Set ImageName=”
Close #1
CreateObject(“WScript.Shell”).Run WindowStyle:=0, WaitOnReturn:=True, Command:=tmpBat
Kill tmpBat

Eugen

Reply | Quote

Hi there,

This code finds the Adobe Acrobat Reader’s path (version independent):

Code:

‘******************************************
‘CHECK YOUR ADOBE READER VERSION HERE,
‘AND USE CORRECT PATH…
‘******************************************
zProg = “?”
On Error Resume Next
zProg = Replace(Replace(CreateObject(“WScript.Shell”).RegRead(“HKEY_CLASSES_ROOTAcroExch.DocumentShellOpenCommand”), ” “”%1″””, “”), “”””, “”)
On Error GoTo 0

If zProg = “?” Then
    MsgBox “Adobe Acrobat Reader was not found !”, vbOKOnly + vbCritical
    End
End If

Eugen

Reply | Quote

Hi I have used the sheet and it works perfectly.

can you tell me why when I paste values I make no other changes on the sheet (b2:B40)

I get a type mismatch error and the VBA highlights the below code

zPrintCopies = Int(cell.Offset(0, 1).Value) ‘fetch number from adjacent cell (on same row)

Reply | Quote

Hi foncesa

Welcome to the Lounge!

Yes, you can use vba to do what you want. Probably.
I tried this code on my laptop, and it printed my test pdf files to my default printer.
It uses the Adobe Reader to do the printing:

Code:

Sub printPDFfiles()

'ChDrive "E"        '<< if your files are on a different drive!

'NOTE:Example below uses Adobe Reader, but apply to Acrobat as well.
'If you are using Acrobat, substitute Acrobat.exe in place of AcroRd32.exe
'on the command line.

'NOTE:
'/s=don't show splash screen
'/n=new instance
'/h=minimised window
'/t=print to default printer; or use /t    

'CHECK YOUR ADOBE READER VERSION, AND USE CORRECT PATH..
'zProg = "C:Program Files (x86)AdobeReader 10.0ReaderAcroRd32.exe"
zProg = "C:Program Files (x86)AdobeReader 11.0ReaderAcroRd32.exe"

zLastRow = [a65536].End(xlUp).Row           'find last row in column [A]; e.g. 15
temp = "a1:a" & zLastRow                    'e.g. "a1:a15"

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
For Each cell In Range(temp)                'loop through all entries in range
zFile = cell.Value                          'fetch filename from cell

If zFile Like "*.pdf" Then                  'check it is a pdf file type
Shell (zProg & " /n /h /t " & zFile)        'execute command to print  the pdf document
End If                                      'end of test for pdf file type

Next                                        'process nect file in list
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

End Sub

Note that after printing all the files, it leaves the Adobe Reader open.
I don’t know how to close it.
Perhaps someone here can tell us that bit.
Meanwhile, you can close the Adobe session manually.

Hope this helps.
I have attached a sample file containing this code.

Let me know if this works for you.

zeddy

Thanks zeddy.
I tested and it does print the file. However it uses the default print settings and I need to use different settings. Normally with adobe once I change the settings for the first file, it then works for all files until adobe is closed and reopened. So I tried printing a file with the settings I want and then using the macro to print a file. However the file printed by the macro still used the default settings. Also after running the macro adobe was set back to the default settings.

I tried removing the new instance flag from the macro (/n) but it didn’t make any difference. Is there any way to solve this?

Thanks in advance

Reply | Quote

Hi foncesa

Welcome to the Lounge!

Yes, you can use vba to do what you want. Probably.
I tried this code on my laptop, and it printed my test pdf files to my default printer.
It uses the Adobe Reader to do the printing:

Code:

Sub printPDFfiles()

'ChDrive "E"        '<< if your files are on a different drive!

'NOTE:Example below uses Adobe Reader, but apply to Acrobat as well.
'If you are using Acrobat, substitute Acrobat.exe in place of AcroRd32.exe
'on the command line.

'NOTE:
'/s=don't show splash screen
'/n=new instance
'/h=minimised window
'/t=print to default printer; or use /t    

'CHECK YOUR ADOBE READER VERSION, AND USE CORRECT PATH..
'zProg = "C:Program Files (x86)AdobeReader 10.0ReaderAcroRd32.exe"
zProg = "C:Program Files (x86)AdobeReader 11.0ReaderAcroRd32.exe"

zLastRow = [a65536].End(xlUp).Row           'find last row in column [A]; e.g. 15
temp = "a1:a" & zLastRow                    'e.g. "a1:a15"

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
For Each cell In Range(temp)                'loop through all entries in range
zFile = cell.Value                          'fetch filename from cell

If zFile Like "*.pdf" Then                  'check it is a pdf file type
Shell (zProg & " /n /h /t " & zFile)        'execute command to print  the pdf document
End If                                      'end of test for pdf file type

Next                                        'process nect file in list
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

End Sub

Note that after printing all the files, it leaves the Adobe Reader open.
I don’t know how to close it.
Perhaps someone here can tell us that bit.
Meanwhile, you can close the Adobe session manually.

Hope this helps.
I have attached a sample file containing this code.

Let me know if this works for you.

zeddy

This code worked like a charm for me! Thanx!
But is it possible to add checkboxes so I can choose which files I want to print? I tried to mess around in the code with no luck

Reply | Quote

Hi Panyborg

Welcome to the Lounge with your first post!

Everything is possible in Excel.
I’ll post a file back to do this, but I have a pile of stuff to do first.
Please keep checking here.

zeddy

Reply | Quote

Hi Panyborg

Welcome to the Lounge with your first post!

Everything is possible in Excel.
I’ll post a file back to do this, but I have a pile of stuff to do first.
Please keep checking here.

zeddy

Thanks!

Reply | Quote

Hi

Thanks for being patient.
It’s next on my list.

zeddy

Reply | Quote

Hi

OK, here’s the latest version of the Excel PDF file printing tool.
Instead of using checkboxes to select which file you want to print, just leave the number of copies you want to print as blank (or zero).

I thought this would be a better solution as this allows you (and others who have asked for it) to specify how many copies of each print you want.

I have added the ability to choose a folder, which will list all the pdf files in that folder.
Also, you can add individual pdf files to the list, by browsing and using Ctrl-click to multi-select files.
Please let me know if this works for you.

zeddy

Reply | Quote

Your File is Awesome.

Where to edit the exact printing page number , for example in 1 to 10 page PDF 5 th only i need print.

Where to edit the code which you have given.

Pls revert.

Sheik:rolleyes::rolleyes::rolleyes::rolleyes::rolleyes:

Hi zeddy.
Thanks for the quick reply.
The settings are Quality, Colour & Collation. I think the settings are printer specific but the printer lets me save pre-sets which it then what I select.

Thank you

Hi

OK, here’s the latest version of the Excel PDF file printing tool.
Instead of using checkboxes to select which file you want to print, just leave the number of copies you want to print as blank (or zero).

I thought this would be a better solution as this allows you (and others who have asked for it) to specify how many copies of each print you want.

I have added the ability to choose a folder, which will list all the pdf files in that folder.
Also, you can add individual pdf files to the list, by browsing and using Ctrl-click to multi-select files.
Please let me know if this works for you.

zeddy

Reply | Quote

Hi Sheik

..do you have Adobe Acrobat? Are you using Word 2013 (or Word 2015)?
(both of these make it easier to print specified pages from a pdf file.)
Please advise.

zeddy

Reply | Quote

Hi zeddy

Your code is working like charm no doubt, if i need only first page to printout of each and every page then what do i need to change in your code?

Can you please post that code only?

Thanks in advance.

Reply | Quote

Hi Sheik

..do you have Adobe Acrobat? Are you using Word 2013 (or Word 2015)?
(both of these make it easier to print specified pages from a pdf file.)
Please advise.

zeddy

i zeddy,

Pdf split i have done through PDF extract tool.

But the printing order in your excel tool is not ascending, it is taking prinout’s in random manner.

I have changed the loop to 10 and the adobe printing taking order is like 3, 5 , 7,1,2 like this….and the loop is changing every time.

the pdfs like 101,102,103,104 and the printing orders like 103, 101, 104,102 like that.

Could you pls specify where we can restrict the order.

Regards
Sheik.

Reply | Quote

Hi Sheik

..I’m looking into this.

hi serious

if i need only first page to printout

..I’m still looking in to this

zeddy

Reply | Quote

Hi zeddy

i had tried using same vba code to print multiple pdf file, but i am getting an error – stated below

“there was an error opening this document. this file cannot be found”

C:UsersmseenuDocumentsTAPI INDIAProjectRenaming ToolFile RenamerNew folderIHA01_IHA01.pdf

Appreciate if you or anyone can help me

Thank you
Mohan Seenu

Reply | Quote

Hi Mohan

Welcome to the Lounge as a new poster!

The error message seems to suggest that the file named IHA01_IHA01.pdf cannot be found in that folder.
Can you browse to that folder and manually check to see if it is there???
Is the name of that pdf file correct???

zeddy

Reply | Quote

Hi
Hope you are doing good!!!

can you please help me with the query, i have bill number in excel and the pdf file containing the same bill number in a folder but the folder contain lots of PDF files whether i need only the pdf copies that is matching with the excel file. Is there any code that will automatically fetch the pdf file in a folder matching with the excel file names.

Thanks in advance
Sharyansh

Reply | Quote

Thanks Zeddy, I have tested it and its 100%, perfect.
Thankyou so much for your efforts. God Bless you.

Reply | Quote

Hi Warna

Welcome to the lounge.
What sort of settings are you trying to change?
Paper size??

zeddy

Reply | Quote

Hi zeddy.
Thanks for the quick reply.
The settings are Quality, Colour & Collation. I think the settings are printer specific but the printer lets me save pre-sets which it then what I select.

Thank you

Reply | Quote

Hi Warna

Have you tried to create a ‘new printer’ (with the settings you require) and then sending to that ‘new printer’?

zeddy

Reply | Quote

I just found this thread and this is almost exactly what I’m trying to do. Zeddy I also really appreciate what you have created, for my purposes (we are printing product manuals daily) we also need to specify a quantity of each PDF to print. For example we have a list of ~20 files, some days we need 0 of a file, some days 5. Is it easy/possible to do this?

Reply | Quote

Hi CJ5

Welcome to the Lounge with your first posting.

Yes, it is possible to specify the number of copies of each pdf file to print.
However, Adobe reader doesn’t have a command line switch to allow this (great pity), so we have to use a loop to essentially do the same thing.

I have attached an updated file which allows you to specify the required number of copies for each pdf file listed in column [A]. Note, if you leave the required number of copies blank, it will skip that listed pdf file.

Also, to remind you, this attached file needs to be opened with macros enabled. When enabled, it will set the printer to your default printer. There is a button to switch to a different (detected) printer if required. On my system, if I set my printer with paper size, colour, quality etc, these seem to stay in effect for all the printouts.

When finished, the Adobe Reader will still be open, and can be closed manually.
Please let me know if this works for you.

zeddy

Reply | Quote

Very cool, I tweaked it slightly to suit my needs, it’s doing exactly what I want it to.

Thank you so so much zeddy, I don’t think I could have figured this out by myself.

Reply | Quote

Hi

Thank you for thanking me.
Glad you were able to tweak it for your needs.

zeddy

Reply | Quote

Hello,

Is there a way to modify this for Solidworks Edrawing files? The file extension is .EDRW. I tried changing the location of the program and the file type in the code, but it didn’t work.

The visual basic program opened up the program, but it couldn’t find and print the file.

Reply | Quote

You’d get that error if the contents of cell.Offset(0, 1) can’t be converted to an Integer.

Reply | Quote

when pasting into the sheet, I only paste values, if the value is blank is there a way I can convert it to Integer.

Reply | Quote

Hi leigh

..try this:
Replace the vba line:
zPrintCopies = Int(cell.Offset(0, 1).Value) ‘fetch number from adjacent cell (on same row)
with these lines:
zPrintCopies = 0
On Error Resume Next
zPrintCopies = Int(cell.Offset(0, 1)) ‘fetch number from adjacent cell (on same row)
On Error GoTo 0

This will treat any ‘text’ entries as zero, but will allow the number of print copies to be entered with a leading apostrophe e.g. as ‘2 etc etc
The Int is required in case someone enters something like 3.65 for the number of copies!

zeddy

Reply | Quote

Hi Zeddy,
I use your file. i works, too. Very thanks!! I have a problem, could i revised the code to let me Double-sided printing? thank you.

Reply | Quote

Hi terryyen

Welcome to the Lounge as a new poster!

There are several ways of dealing with this duplex-printing issue.
One is to set up an existing printer (that allows for double-sided printing) using the Advanced Properties for that printer, and then adding that setup as a ‘new’ printer. Then you can select that one for your printouts.

zeddy

Reply | Quote

Good Morning All. I know this is an older thread, but I was hoping that someone could help me.
Below is the current code that I am running. Essentially this macro will print external PDF files that are listed in column C.

I am having a little trouble taking this to the next step.
Here is what I am looking to do – hopefully someone can help.

Column C is the file name. Column b is the quantity. I want to print multiple copies of the file depending on what quantity is listed in column b. I figured the best way to do this is to create a PDF with 50 pages of identical files (as the files are very small in size and the print quantity will next extend past 50) and have a function which prints the number of pages in the PDF that correspond to quantity.

For instance, if column b has a quantity of 20, this macro will print pages 1-20 of the given PDF. The problem is, however, while I imagine this code is quite easy for some of you, it is way above my head!

I would appreciate any help available.

Thank you again!
Ryan

Sub printPDFfiles()

zProg = “C:Program FilesAdobeAcrobat DCAcrobatAcrobat.exe”

zLastRow = [c200].End(xlUp).Row
temp = “c9:c” & zLastRow

zPrinter1 = “Afinia L801 Label Printer”
‘~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
For Each cell In Range(temp)
zFile = cell.Value

If zFile Like “*.pdf” Then
Shell (zProg & ” /n /h /t ” & Chr(34) & zFile & Chr(34) & ” ” & zPrinter1)
End If

Next
End Sub

Reply | Quote

Um, if my situation is all PDFs embedded in one sheet, i want to select some of them to be print out , is it possible to do so please?

Reply | Quote

Hi

Welcome to the Lounge as a new poster!

It’s not clear what you want.
Can you give an example?

zeddy

Reply | Quote

I have 50 PDFs which is embedded in a worksheet by insert>object (not linked to the file). I can read the PDFs in anywhere. The PDFs are named, for example A1-1, A1-2…to A1-10 and A2-1…to A2-10 and A3-1..to A3-10……A5-1 to A5-10.
Are there any VBA that can let me select some of the PDF to be printed out, let say I type A1 in input box then put all A1 PDFs?

Reply | Quote

Hi Carl

When you say you have 50 PDFs named A1-1, A1-2,.. are you saying that these are the ‘Object names’ (as per the Excel ‘name-box’ to the left of the top-panel formula bar), or are you saying that these are the actual names of the embedded PDF documents i.e. A1-1.pdf, A1-2.pdf, … etc etc etc.

zeddy

Reply | Quote

Hi Carl

When you say you have 50 PDFs named A1-1, A1-2,.. are you saying that these are the ‘Object names’ (as per the Excel ‘name-box’ to the left of the top-panel formula bar), or are you saying that these are the actual names of the embedded PDF documents i.e. A1-1.pdf, A1-2.pdf, … etc etc etc.

zeddy

Per the name box, please help me

Reply | Quote

Hi Carl

OK, that makes it so much easier!
I can do this, but need to set up some tests.
I have a busy day today.

zeddy

Reply | Quote

Thz for your help n just today I find code for print all pdf but not for selected would I post it here?

Reply | Quote

Hi Carl

..no need to spoil my fun. I hope to post a file tomorrow.

zeddy

Reply | Quote

Please be sure to check the post’s dates, this one is very old and the other posters may no longer be around or following it.
2017-05-09, 08:29 #55

Before you wonder "Am I doing things right," ask "Am I doing the right things?"

Reply | Quote

Thanks Berton for suggestion. Now i have posted the same to the form by creating a new thread.
Appreciate your response

Reply | Quote

i have posted the same to the form by creating a new thread

New thread here:
How to find out pdf file via using the name from excel

Lugh.
~
Alienware Aurora R6; Win10 Home x64 1803; Office 365 x32
i7-7700; GeForce GTX 1060; 16GB DDR4 2400; 1TB SSD, 256GB SSD, 4TB HD

Reply | Quote