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

[cfdh_edmundo]

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:

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

 

[cfdh_edmundo]

This is the code I have so far, which builds the array of filepaths and names, and strips out any blanks.

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]

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.