Excel Macro to Open and Print Multiple PDF Files

[ChadDiesel]

I asked the question a couple of years ago about a macro that would
open a spreadsheet based on a file path listed in a certain cell,
print the spreadsheet, close the spreadsheet, and move to the next
cell and keep printing until it hits a blank cell. Here is the
example:

I have a spreadsheet that contains 2 columns-Part Number and Drawing
Location. The sheet looks something like this:

---A------------------------B-------------------------
Part#-----------Drawing Location
Part 1----------c:\drawings\part1.xls
Part 2----------c:\drawings\part2.xls
Part 3----------c:\drawings\part3.xls

When the macro runs, part1.xls opens/prints/closes, part2.xls opens/
prints/closes, part3.xls opens/prints/closes and then it stops.

Don Guillett and Dave Peterson helped me with this last time:

Dim LR As Long
Dim WB As Workbook
Dim WBCell As Range

LR = Cells(Rows.Count, "b").End(xlUp).Row

For Each WBCell In Range("b2:b" & LR)
Set WB = Workbooks.Open(WBCell.Value)
WB.PrintOut
WB.Close SaveChanges:=False
Next WBCell

This has worked great for 2 years. My only problem now is that we are
switching to drawings in PDF format instead of Excel format. We might
even have some drawings in Tiff format but primarily PDF

If the new format is:

---A------------------------B-------------------------
Part#-----------Drawing Location
Part 1----------c:\drawings\part1.pdf
Part 2----------c:\drawings\part2.pdf
Part 3----------c:\drawings\part3.pdf

Can the same thing be done with PDF or Tiff files, since an external
program is needed to open the files?

Any help would be appreciated. Thank You.

-Chad

 

[Jim Rech]

When I look at .pdf in my registry under HKCR the default type is
AcroExch.Document. When I look that up, also under HKCR, and go to Print
under Shell:

HKEY_CLASSES_ROOT\AcroExch.Document\Shell\print\command

I see: "C:\Program Files\Adobe\Acrobat 7.0\Acrobat\Acrobat.exe" /p /h "%1"

So that is the program Windows calls to print on my machine when I
right-click a PDF file in Windows Explorer and pick Print.

So I can Shell to this program from Excel like this:

Sub PrnPDF()
Dim LR As Long
Dim WBCell As Range
LR = Cells(Rows.Count, "b").End(xlUp).Row
For Each WBCell In Range("b2:b" & LR)
Shell "C:\Program Files\Adobe\Acrobat 7.0\Acrobat\Acrobat.exe /p /h"
& WBCell.Value
Next WBCell
End Sub

You may have a different program assigned to print PDFs so you'd have to use
Regedit.exe like I did (Start->Run->Regedit.exe) and change the macro
accordingly based on what you find.

--
Jim
|I asked the question a couple of years ago about a macro that would
| open a spreadsheet based on a file path listed in a certain cell,
| print the spreadsheet, close the spreadsheet, and move to the next
| cell and keep printing until it hits a blank cell. Here is the
| example:
|
| I have a spreadsheet that contains 2 columns-Part Number and Drawing
| Location. The sheet looks something like this:
|
| ---A------------------------B-------------------------
| Part#-----------Drawing Location
| Part 1----------c:\drawings\part1.xls
| Part 2----------c:\drawings\part2.xls
| Part 3----------c:\drawings\part3.xls
|
| When the macro runs, part1.xls opens/prints/closes, part2.xls opens/
| prints/closes, part3.xls opens/prints/closes and then it stops.
|
| Don Guillett and Dave Peterson helped me with this last time:
|
| Dim LR As Long
| Dim WB As Workbook
| Dim WBCell As Range
|
| LR = Cells(Rows.Count, "b").End(xlUp).Row
|
| For Each WBCell In Range("b2:b" & LR)
| Set WB = Workbooks.Open(WBCell.Value)
| WB.PrintOut
| WB.Close SaveChanges:=False
| Next WBCell
|
| This has worked great for 2 years. My only problem now is that we are
| switching to drawings in PDF format instead of Excel format. We might
| even have some drawings in Tiff format but primarily PDF
|
| If the new format is:
|
| ---A------------------------B-------------------------
| Part#-----------Drawing Location
| Part 1----------c:\drawings\part1.pdf
| Part 2----------c:\drawings\part2.pdf
| Part 3----------c:\drawings\part3.pdf
|
| Can the same thing be done with PDF or Tiff files, since an external
| program is needed to open the files?
|
| Any help would be appreciated. Thank You.
|
| -Chad

 

[ChadDiesel]

Thank you very much! That worked great. Thank you for all the help.

-Chad

 

[Jorge_baja]

I found interesting your solution, I am very rusty on this, I have your
solution but I don't know how to use it. Do I go to tools, macro , visual
editor? if this is the case, I entered your program in there in sheet 1 and
saved it. For this excel sheet I have a file name in colulmn B and the file
path in column C. How do I put everything together, how do I make the
program run? thank you for your time.

 

[Jim Rech]

The code should not go in the Sheet1 module. Remove it from there and
instead use Insert, Module to create a standard module.

Since Chad had the full path with file name in column B and you have the
file name only in column B and the path in C, the code has to be modified.
Remember that the first file name starts in cell B2 of the active sheet and
there can be no empty lines..

''Replace this string with the path to the EXE that handles printing PDFs on
your machine
Const PDFPrnCmd As String = "C:\Program Files\Adobe\Acrobat
7.0\Acrobat\Acrobat.exe /p /h"

Sub PrnPDF()
Dim WBCell As Range, Rg As Range
Dim Pth As String, FName As String
Set Rg = Range("B2")
If Rg.Offset(1).Value <> "" Then Set Rg = Range(Rg, Rg.End(xlDown))
For Each WBCell In Rg
FName = WBCell.Value
Pth = WBCell.Offset(0, 1).Value
If Right(Pth, 1) <> "\" Then Pth = Pth & "\"
Shell PDFPrnCmd & Pth & FName
Next WBCell
End Sub

--
Jim
|I found interesting your solution, I am very rusty on this, I have your
| solution but I don't know how to use it. Do I go to tools, macro , visual
| editor? if this is the case, I entered your program in there in sheet 1
and
| saved it. For this excel sheet I have a file name in colulmn B and the
file
| path in column C. How do I put everything together, how do I make the
| program run? thank you for your time.
|
| "Jim Rech" wrote:
|
| > When I look at .pdf in my registry under HKCR the default type is
| > AcroExch.Document. When I look that up, also under HKCR, and go to
Print
| > under Shell:
| >
| > HKEY_CLASSES_ROOT\AcroExch.Document\Shell\print\command
| >
| > I see: "C:\Program Files\Adobe\Acrobat 7.0\Acrobat\Acrobat.exe" /p /h
"%1"
| >
| > So that is the program Windows calls to print on my machine when I
| > right-click a PDF file in Windows Explorer and pick Print.
| >
| > So I can Shell to this program from Excel like this:
| >
| > Sub PrnPDF()
| > Dim LR As Long
| > Dim WBCell As Range
| > LR = Cells(Rows.Count, "b").End(xlUp).Row
| > For Each WBCell In Range("b2:b" & LR)
| > Shell "C:\Program Files\Adobe\Acrobat 7.0\Acrobat\Acrobat.exe /p
/h"
| > & WBCell.Value
| > Next WBCell
| > End Sub
| >
| > You may have a different program assigned to print PDFs so you'd have to
use
| > Regedit.exe like I did (Start->Run->Regedit.exe) and change the macro
| > accordingly based on what you find.
| >
| > --
| > Jim
| >
| > |I asked the question a couple of years ago about a macro that would
| > | open a spreadsheet based on a file path listed in a certain cell,
| > | print the spreadsheet, close the spreadsheet, and move to the next
| > | cell and keep printing until it hits a blank cell. Here is the
| > | example:
| > |
| > | I have a spreadsheet that contains 2 columns-Part Number and Drawing
| > | Location. The sheet looks something like this:
| > |
| > | ---A------------------------B-------------------------
| > | Part#-----------Drawing Location
| > | Part 1----------c:\drawings\part1.xls
| > | Part 2----------c:\drawings\part2.xls
| > | Part 3----------c:\drawings\part3.xls
| > |
| > | When the macro runs, part1.xls opens/prints/closes, part2.xls opens/
| > | prints/closes, part3.xls opens/prints/closes and then it stops.
| > |
| > | Don Guillett and Dave Peterson helped me with this last time:
| > |
| > | Dim LR As Long
| > | Dim WB As Workbook
| > | Dim WBCell As Range
| > |
| > | LR = Cells(Rows.Count, "b").End(xlUp).Row
| > |
| > | For Each WBCell In Range("b2:b" & LR)
| > | Set WB = Workbooks.Open(WBCell.Value)
| > | WB.PrintOut
| > | WB.Close SaveChanges:=False
| > | Next WBCell
| > |
| > | This has worked great for 2 years. My only problem now is that we are
| > | switching to drawings in PDF format instead of Excel format. We might
| > | even have some drawings in Tiff format but primarily PDF
| > |
| > | If the new format is:
| > |
| > | ---A------------------------B-------------------------
| > | Part#-----------Drawing Location
| > | Part 1----------c:\drawings\part1.pdf
| > | Part 2----------c:\drawings\part2.pdf
| > | Part 3----------c:\drawings\part3.pdf
| > |
| > | Can the same thing be done with PDF or Tiff files, since an external
| > | program is needed to open the files?
| > |
| > | Any help would be appreciated. Thank You.
| > |
| > | -Chad
| >
| >
| >

 

[akmaneesh]

Hi I have a requirement with macro code (VBA) in excel, can some one help me.

1. i have a excel with name of the peoples and with the same name there are pdf files. With vba code each names pdf files need to be opened and book mark the pages accordingly as updated next to name updated in excel.

name age place group adderess blood group category

2. after bookmarkin it need to come and update the excel as yes/no(found or not)

can some one help me with this.

thanks in advance.

 

[chadclark0]

The code should not go in the Sheet1 module. Remove it from there and
instead use Insert, Module to create a standard module.

Since Chad had the full path with file name in column B and you have the
file name only in column B and the path in C, the code has to be modified..
Remember that the first file name starts in cell B2 of the active sheet and
there can be no empty lines..

''Replace this string with the path to the EXE that handles printing PDFson
your machine
Const PDFPrnCmd As String = "C:\Program Files\Adobe\Acrobat
7.0\Acrobat\Acrobat.exe /p /h"

Sub PrnPDF()
Dim WBCell As Range, Rg As Range
Dim Pth As String, FName As String
Set Rg = Range("B2")
If Rg.Offset(1).Value <> "" Then Set Rg = Range(Rg, Rg.End(xlDown))
For Each WBCell In Rg
FName = WBCell.Value
Pth = WBCell.Offset(0, 1).Value
If Right(Pth, 1) <> "\" Then Pth = Pth & "\"
Shell PDFPrnCmd & Pth & FName
Next WBCell
End Sub

|I found interesting your solution, I am very rusty on this, I have your
| solution but I don't know how to use it. Do I go to tools, macro , visual
| editor? if this is the case, I entered your program in there in sheet 1
and
| saved it. For this excel sheet I have a file name in colulmn B and the
file
| path in column C. How do I put everything together, how do I make the
| program run? thank you for your time.
|
| "Jim Rech" wrote:
|
| > When I look at .pdf in my registry under HKCR the default type is
| > AcroExch.Document. When I look that up, also under HKCR, and go to
Print
| > under Shell:
| >
| > HKEY_CLASSES_ROOT\AcroExch.Document\Shell\print\command
| >
| > I see: "C:\Program Files\Adobe\Acrobat 7.0\Acrobat\Acrobat.exe" /p /h
"%1"
| >
| > So that is the program Windows calls to print on my machine when I
| > right-click a PDF file in Windows Explorer and pick Print.
| >
| > So I can Shell to this program from Excel like this:
| >
| > Sub PrnPDF()
| > Dim LR As Long
| > Dim WBCell As Range
| > LR = Cells(Rows.Count, "b").End(xlUp).Row
| > For Each WBCell In Range("b2:b" & LR)
| > Shell "C:\Program Files\Adobe\Acrobat 7.0\Acrobat\Acrobat.exe/p
/h"
| > & WBCell.Value
| > Next WBCell
| > End Sub
| >
| > You may have a different program assigned to print PDFs so you'd haveto
use
| > Regedit.exe like I did (Start->Run->Regedit.exe) and change the macro
| > accordingly based on what you find.
| >
| > --
| > Jim
| > "ChadDiesel" wrote in message
| >
...
| > |I asked the question a couple of years ago about a macro that would
| > | open a spreadsheet based on a file path listed in a certain cell,
| > | print the spreadsheet, close the spreadsheet, and move to the next
| > | cell and keep printing until it hits a blank cell. Here is the
| > | example:
| > |
| > | I have a spreadsheet that contains 2 columns-Part Number and Drawing
| > | Location. The sheet looks something like this:
| > |
| > | ---A------------------------B-------------------------
| > | Part#-----------Drawing Location
| > | Part 1----------c:\drawings\part1.xls
| > | Part 2----------c:\drawings\part2.xls
| > | Part 3----------c:\drawings\part3.xls
| > |
| > | When the macro runs, part1.xls opens/prints/closes, part2.xls opens/
| > | prints/closes, part3.xls opens/prints/closes and then it stops.
| > |
| > | Don Guillett and Dave Peterson helped me with this last time:
| > |
| > | Dim LR As Long
| > | Dim WB As Workbook
| > | Dim WBCell As Range
| > |
| > | LR = Cells(Rows.Count, "b").End(xlUp).Row
| > |
| > | For Each WBCell In Range("b2:b" & LR)
| > | Set WB = Workbooks.Open(WBCell.Value)
| > | WB.PrintOut
| > | WB.Close SaveChanges:=False
| > | Next WBCell
| > |
| > | This has worked great for 2 years. My only problem now is that we are
| > | switching to drawings in PDF format instead of Excel format. We might
| > | even have some drawings in Tiff format but primarily PDF
| > |
| > | If the new format is:
| > |
| > | ---A------------------------B-------------------------
| > | Part#-----------Drawing Location
| > | Part 1----------c:\drawings\part1.pdf
| > | Part 2----------c:\drawings\part2.pdf
| > | Part 3----------c:\drawings\part3.pdf
| > |
| > | Can the same thing be done with PDF or Tiff files, since an external
| > | program is needed to open the files?
| > |
| > | Any help would be appreciated. Thank You.
| > |
| > | -Chad
| >
| >
| >

Jim,

I know this is a several year old topic, but I'd appreciate some help. Is there a way to run this macro on an Excel file, PDF or Tiff file? I realize this is throwing in a curve-ball, but we now have a mixture of drawing file formats that would take a while to convert. Could this macro be changedto accomodate any of these formats, or am I asking for too much? Thank you for the help on this.

-Chad