Using VBA to print a list of files to a single PDF.

cfdh_edmundo

Board Regular
Joined
Nov 9, 2005
Messages
133
Hi,

I have a list of files (filepaths and names) in column C of my worksheet. What I would like to do is to run down the column and print each file into a combined master PDF.

Column C contains both excel files and PDFs and additionally there are some blank cells (which should be skipped). For the excel files the page layout and print areas have already been set up correctly.

The column is as follows:

C:\Temp\Test1.xlsm
D:\Reports\Report3.pdf
<blank cell>
C:\Temp\Test2.xlsx
C:\Temp\Test15.xlsx
D:\Reports\Report1.PDF
<blank cell>
D:\Reports\Report22.PDF

I think I need to put everything from column C into an array. I can use a form of the code below to limit column C to the last used cell:

Code:
LastRow = .Cells(.Rows.Count, "C").End(xlUp).Row

And I can also then use code to remove blank items from the array that's been created.

The major problem I have is figuring out the best way of printing each of the files (which can be Excel files or PDFs) to a single, combined, master PDF.

Any help would be great.

Many thanks,

Chris
 

Some videos you may like

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

cfdh_edmundo

Board Regular
Joined
Nov 9, 2005
Messages
133
This is the code I have so far, which builds the array of filepaths and names, and strips out any blanks.

Rich (BB code):
Sub EnterArray()
Dim MyArray() As Variant
Dim MyColumn As Range
Dim cell As Range
Dim i As Long

i = 0
' set the column to read
Set MyColumn = ActiveSheet.Range("C8:C5000")

For Each cell In Intersect(ActiveSheet.UsedRange, MyColumn)
  If Not IsEmpty(cell) Then
    ReDim Preserve MyArray(i)
    MyArray(i) = cell.Value
    i = i + 1
  End If
Next


' Run code on each element in the array
For i = LBound(MyArray) To UBound(MyArray)
  'Here is where I need to write the code for printing to merged/master PDF
  'use MyArray(i) to get filepath
  'e.g. ActiveSheet.[H1].Offset(i, 0) = MyArray(i)
Next

End Sub

It's the bold bit that I'm struggling on.
 

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
6,423
Standard VBA can't merge files to a PDF. You would need to call an API (e.g. Acrobat Pro) installed on your computer, or use a web service API (search for online merge pdf api) and call it with XMLhttp/WinHttp, or upload the files to an online merger.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,000
Messages
5,526,177
Members
409,685
Latest member
Bellybb

This Week's Hot Topics

Top