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
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

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
7,329
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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,016
Messages
5,834,943
Members
430,327
Latest member
Mr_Himalayan778

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top