Merge PDF and Excel

[fobioha]

Hello,

I need help with a macro for printing a excel sheet to pdf and merging with an existing pdf file. Currently I have a file that is formula based to print statement for 300 people, I also have a macro that changes the name and prints individual statements for each person. I now then manually enter each of the 300 files to merge in a standard memo.

I was wondering if there is any type of macro that can do this. After a bit of research I have found macros for merging two or more excel sheets into 1 pdf but cannot find anything about merging to an already existing pdf. Any help or guidance would be MUCH appreciated.

For reference here is my existing macro:

Please Login or Register  to view this content.

[buran]

Please take time to read forum rules and use [CODE] tags around your code.

[fobioha]

Please take time to read forum rules and use [CODE] tags around your code.

updated, thanks!

[buran]

Thanks.
I'm not aware of a way to print worksheet or save it save as fixed format to an existing pdf. However you can print all pdfs in a single folder and use third party software like pdf toolkit https://www.pdflabs.com/tools/pdftk-the-pdf-toolkit/ to merge all files in the folder. You can use the GUI, or open a command prompt and run pdftk from there or implement the same in your VBA code using Shell something like

Please Login or Register  to view this content.

take a look at the example code provided on pdftk website.
Note that there are also other free/paid software packages that can easily merge large numbers of existing pdfs.

[fobioha]

I just want to clarify that I'm looking to merge 1 standard memo into the 300 different statements. Using the macro above, I already am able to print the 300 statements to pdf from my excel. I am just wondering if I can also somehow add the same memo into the 300 pdf files either as the statements print or after.

[buran]

Sorry, I misunderstood your request. Here is your code amended:

Please Login or Register  to view this content.

with the this code you don't need the second sub.
The code above assumes you have pdftk installed, the folders "C:\temp\output\" and "C:\temp\output\merged\" exist and you have file named memo.pdf at following location "C:\temp\output\memo.pdf"
Also note that due to restrictions in pdftk you are not able to have spaces in the file name.
all merged files will be located in "C:\temp\output\merged\"

[fobioha]

Thanks so much for working on this. I don't have pdftk installed and cannot install it due to security restrictions on my work pc, is there a workaround for that?

[buran]

Unfortunately I don't think it's possible without third party tools. You should have mentioned that you have such restrictions from the beginning or at least after I first mentioned using third-party tool in my post #4. This way I would avoid loosing my time to amend your code.

[fobioha]

Hello, I was able to get pdftk installed on my pc but the macro is not working. I still just get the individual statements printed to C:\Temp\output. Nothing happens within the merged folder, pdftk does open for every file that is supposed to merged but nothing is merged. Can you help?

Please Login or Register  to view this content.

[buran]

Hi,
sorry, I don't know where was my mind. shell call to pdftk should be:

Please Login or Register  to view this content.

make sure that strFileName has ONLY the file name. My experience shows that when full path to file is supplied it doesn't work. Or at least I wasn't able to make it work.
I don't know why you have added xp at the end of pdftk.

[fobioha]

Still just printing the statements and opening pdftk, but no merging is happening. I have the merged folder at C:\Temp\output\merged and the statements just print to C:\Temp\output without the memo. Anything you can see?

Please Login or Register  to view this content.

[buran]

Where is your memo.pdf file? it should be in the same folder c:\temp\output

[fobioha]

It is there. Everything is set up correctly, just the merging function. Do you mind sending a code just to merge two existing pdf documents and I'll try and see if that works and play with it from there? Feel like it's so close!

[buran]

Do you mind sending a code just to merge two existing pdf documents and I'll try and see if that works and play with it from there?

Not sure what do you mean
can you create the pdftk command in a separate line and print it, just to see what you get?

[fobioha]

Not sure what do you mean
can you create the pdftk command in a separate line and print it, just to see what you get?

Please Login or Register  to view this content.

I meant if you have a command for a simpler operation where it just merges two documents without the printing function but I'm assuming that it what the code above would do. I just ran that and it opens pdftk but nothing happens after. Also I added xp to the name because that is what my application is titled. If I don't have the xp, the program doesn't pop up.

[buran]

i don't have more ideas. anyway, I don't see any pdftkxp examples here
https://www.pdflabs.com/docs/pdftk-cli-examples/
you can check that the command line string is correct:

Join in1.pdf and in2.pdf into a new PDF, out1.pdf

pdftk in1.pdf in2.pdf cat output out1.pdf

or (using handles):

pdftk A=in1.pdf B=in2.pdf cat A B output out1.pdf

or (using wildcards):

pdftk *.pdf cat output combined.pdf

I would suggest that you check that the command line is ok by printing it

[fobioha]

I would suggest that you check that the command line is ok by printing it

Can you explain that. Sorry I'm new to this, not familiar with the command language.

[buran]

Please Login or Register  to view this content.

You can see the string printed in the immediate window in the VBE

[buran]

By the way, I've checked my latest code where I have used pdftk
I see that there I have added " around the output file name. I don't remember why I have done so, but obviously it works.
it don't hurt to try

Please Login or Register  to view this content.

[fobioha]

When you use pdftk within a macro, does the program open and you actually see the function occurring? Or does it all happen in the background?

[buran]

you can use second optional parameter to Shell function - window style.
here is the help:

Shell Function

Runs an executable program and returns a Variant (Double) representing the program's task ID if successful, otherwise it returns zero.

Syntax

Shell(pathname[,windowstyle])

The Shell function syntax has these named arguments:

Part Description
pathname Required; Variant (String). Name of the program to execute and any required arguments or command-line switches; may include directory or folder and drive. On the Macintosh, you can use the MacID function to specify an application's signature instead of its name. The following example uses the signature for Microsoft Word:

Shell MacID("MSWD")

windowstyle Optional. Variant (Integer) corresponding to the style of the window in which the program is to be run. If windowstyle is omitted, the program is started minimized with focus. On the Macintosh (System 7.0 or later), windowstyle only determines whether or not the application gets the focus when it is run.

The windowstyle named argument has these values:

Constant Value Description
vbHide 0 Window is hidden and focus is passed to the hidden window. The vbHide constant is not applicable on Macintosh platforms.
vbNormalFocus 1 Window has focus and is restored to its original size and position.
vbMinimizedFocus 2 Window is displayed as an icon with focus.
vbMaximizedFocus 3 Window is maximized with focus.
vbNormalNoFocus 4 Window is restored to its most recent size and position. The currently active window remains active.
vbMinimizedNoFocus 6 Window is displayed as an icon. The currently active window remains active.

Remarks

If the Shell function successfully executes the named file, it returns the task ID of the started program. The task ID is a unique number that identifies the running program. If the Shell function can't start the named program, an error occurs.

On the Macintosh, vbNormalFocus, vbMinimizedFocus, and vbMaximizedFocus all place the application in the foreground; vbHide, vbNoFocus, vbMinimizeFocus all place the application in the background.


Note
By default, the Shell function runs other programs asynchronously. This means that a program started with Shell might not finish executing before the statements following the Shell function are executed.


Example
This example uses the Shell function to run an application specified by the user. On the MacIntosh, the default drive name is "HD" and portions of the pathname are separated by colons instead of backslashes. Similarly, you would specify Macintosh folders instead of \Windows.

Please Login or Register  to view this content.

use 0 and the window will be hidden

[buran]

We exchanged few private messages with fobioha and for sake of completeness here is the code that works as expected:

Please Login or Register  to view this content.

the problem was with the names of the files, e.g. special characters like comma and lack of file extension (.pdf)
I think this thread may be marked as SOLVED