Add Background to PDF using Excel VBA

I would like add a background to a PDF File using excel vba code, Please help as I cannot find any alternative other that watermarking API

[VBA]Function WatermarkPDF(Base_PDF As String, WatermarkPDF_AX As String)
Dim bolResult As Boolean
Dim pdfDoc1 As AcroPDDoc
Dim jsObj As Object

Set pdfDoc1 = CreateObject("AcroExch.PDDoc")

If pdfDoc1.Open(Base_PDF) Then
Set jsObj = pdfDoc1.GetJSObject
'jsObj.addWatermarkFromFile WatermarkPDF_AX ', bOnTop:=False
jsObj.addWatermarkFromFile WatermarkPDF_AX, 0, 0, 0, False, True, True, 0, 0, 0, 0, False, 1, False, 0, 1
End If

pdfDoc1.Save 1, Base_PDF

pdfDoc1.Close

Set jsObj = Nothing
Set pdfDoc1 = Nothing

End Function[/VBA]

Do you have Distiller installed? Did you try that routine?

If you don't have Distiller installed, the routine would not work. You would then need to use a 3rd party PDF utility that accepts command line parameters.

Yes! I have distiller (acrobat distiller) but no code for that..will you able provide a piece of code..

Please help!!!!

Your code worked fine. The 0 start and 0 end pages only put the watermark on the first sheet. I just put a big number for the end pages, 100, to put it on all the pages. Your reference to set is Adobe. I put a comment for the api guide and the page number for setting the watermark options.

Here is how I tested it from a blank workbook. On Sheet2, I set A1 to be wide and tall for the watermark value of DRAFT. Sheet1 will have some dummy data for more than 1 page for the main pdf file which will be appended with the watermark pdf.

I just used the Excel 2010 worksheet method of ExportAsFixedFormat to create the two pdf files. I could have used a Distiller method. If you want to use that method see: http://www.excelforum.com/showthread.php?t=650151

Module Code:
[vba]Sub Test_WatermarkPDF()
Dim base_PDF As String, watermark_PDF As String
Dim cell As Range, i As Integer
base_PDF = ThisWorkbook.Path & "\Base_PDF.pdf"
watermark_PDF = ThisWorkbook.Path & "\Watermark_PDF.pdf"

' Make a basePDF
Sheet1.Cells.Clear
i = 0
For Each cell In Sheet1.Range("A1:F100")
i = i + 1
cell.Value2 = i
Next cell
ActiveSheet.PageSetup.PrintArea = "$A$1:$F$100"
PublishToPDF base_PDF, Sheet1

' Make a watermakePDF
Sheet2.Cells.Clear
Sheet2.Range("A1").Value2 = "DRAFT"
With Sheet2.Range("A1").Font
.Name = "Algerian"
.Size = 72
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.Color = -16776961
.TintAndShade = 0
.ThemeFont = xlThemeFontNone
End With
With Sheet2.Range("A1")
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 45
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
PublishToPDF watermark_PDF, Sheet2

watermarkPDF base_PDF, watermark_PDF
End Sub

' Add Tools > References... > Adobe
' JavaScript API: http://www.adobe.com/content/dam/Ado..._reference.pdf
Function watermarkPDF(base_PDF As String, WatermarkPDF_AX As String)
Dim bolResult As Boolean
Dim pdfDoc1 As AcroPDDoc
Dim jsObj As Object

Set pdfDoc1 = CreateObject("AcroExch.PDDoc")

If pdfDoc1.Open(base_PDF) Then
Set jsObj = pdfDoc1.GetJSObject
'jsObj.addWatermarkFromFile WatermarkPDF_AX ', bOnTop:=False
'jsObj.addWatermarkFromFile WatermarkPDF_AX, 0, 0, 0, False, True, True, 0, 0, 0, 0, False, 1, False, 0, 1
' Pg. 272
jsObj.addWatermarkFromFile WatermarkPDF_AX, 0, 0, 100, False, True, True, 0, 0, 0, 0, False, 1, True, 0, 1
End If

pdfDoc1.Save 1, base_PDF

pdfDoc1.Close

Set jsObj = Nothing
Set pdfDoc1 = Nothing

End Function

Sub PublishToPDF(fName As String, ws As Worksheet)
Dim rc As Variant

'ChDrive "c:"
'ChDir GetFolderName(fName)
rc = Application.GetSaveAsFilename(fName, "PDF (*.pdf), *.pdf", 1, "Publish to PDF")
If rc = "" Then Exit Sub

ws.ExportAsFixedFormat Type:=xlTypePDF, Filename:=fName _
, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
:=False, OpenAfterPublish:=False
End Sub
[/vba]

It works, Is there any other API reference for adding a background itself, Like Acrobat Menu->Document->Background->add/replace. As some PDF's which is not in a transparent layer not works for watermarks, but works for add background

Oops!.. It works only for PDF by adding the Watermark PDF on top of BASE PDF. Can you please test it with the attached PDF, BACKGROUND PDF need to appear behind Base PDF and the result need to me Merged PDF. I tried through code but no success (Behind pdf, tried bOntop as True/false)

The merged result is what you want? I would have thought that a light colored watermark would be best but then it depends on your goal.

The other question was did you want to repeat it for all pages or just the first one?

All this happening because of Poor capability of excel's Header & footer and same in Acrobat also..(Excel 2003) I am exporting from excel as PDF and adding a pre-designed background to all the pdf's...Its a big job...about 920 PDF files and everyday 130++

I just need the answers to my questions to proceed.

I need to repeat backgrounds for all the pages. Background PDF need to come as background to the base pdf (All pages), Same result as merged pdf. I merged the page manually in acrobat using add background from file method

Besides adding more pages than needed, your original code does that. Am I missing something?

bOnTop is the 4th parameter:

Quote:

---

bOnTop
(optional) A Boolean value specifying the z-ordering of the watermark. If true (the default), the watermark is added above all other page content. If false, the watermark is added below all other page content. This parameter is ignored if bFixedPrint is true.

---

The 3rd parameter from the end is:

Quote:

---

bFixedPrint
(optional) A Boolean value that indicates that this watermark should be added as a FixedPrint Watermark annotation. This allows watermarks to be printed at a fixed size/position regardless of the size of the page being printed to. If true, bOnTop is ignored. The default is false.

---

[vba]Sub Test_WatermarkPDF2()
Dim base_PDF As String, watermark_PDF As String, merged_PDF As String
Dim cell As Range, i As Integer
base_PDF = ThisWorkbook.Path & "\Base.pdf"
watermark_PDF = ThisWorkbook.Path & "\Backg.pdf"
merged_PDF = ThisWorkbook.Path & "\Merged_PDF.pdf"

If Dir(merged_PDF) <> "" Then
Kill merged_PDF
End If
FileCopy base_PDF, merged_PDF

watermarkPDF2 merged_PDF, watermark_PDF
End Sub

' http://www.vbaexpress.com/forum/showthread.php?t=39616
' Add Tools > References... > Adobe
' JavaScript API: http://www.adobe.com/content/dam/Ado..._reference.pdf
Function watermarkPDF2(base_PDF As String, WatermarkPDF_AX As String)
Dim bolResult As Boolean
Dim pdfDoc1 As AcroPDDoc
Dim jsObj As Object

Set pdfDoc1 = CreateObject("AcroExch.PDDoc")

If pdfDoc1.Open(base_PDF) Then
Set jsObj = pdfDoc1.GetJSObject
'For just the first page:
'jsObj.addWatermarkFromFile WatermarkPDF_AX, 0, 0, 0, False, True, True, 0, 0, 0, 0, False, 1, False, 0, 1
' Pg. 272
jsObj.addWatermarkFromFile WatermarkPDF_AX, 0, 0, 100, False, True, True, 0, 0, 0, 0, False, 1, False, 0, 1
End If

pdfDoc1.Save 1, base_PDF

pdfDoc1.Close

Set jsObj = Nothing
Set pdfDoc1 = Nothing

End Function
[/vba]

I tried the same, but no luck, Its always watermarks on the top of base pdf. Im using Office 2003 and Acrobat 8. What about your result with the attached PDF's.

I guess Adobe version might be the problem. I used Excel 2010 on XPPro with Adobe Acrobat 9 Pro. I used your files and it produced the merged form the same as your manually produced form.

I doubt that many here have Adobe. You can try posting my solution with your files to another forum to see if it is an Adobe version problem. Another option might be to try a 3rd party program that allows command line parameters.

I wrote vb.net program using the iTextSharp.dll that allowed some pdf features by command line parameters. The DLL might have a watermark function. http://www.wpuniverse.com/vb/showthr...s-5-iTextSharp

I tried a luck and it worked. It makes problems with PDF's which is made using Acrobat Distiller through code. If its through Activesheet.printout, There is no problem for merging. and background PDF need to be made using Excel only. Can you provide the code for your DLL for watermarking (background)

The DLL is the iTextSharp.dll but I did not write it. It has been a while since I worked in that project. The code is all there in the link for the command line features that I added already. I don't know that it would work for you if the VBA code did not. The book that I have on the dll is iText In Action, 2nd edition, by Bruno Lowagie. A quick look on page 169 shows how to add a watermark but like a watermark it blends the two. Page 177 uses background so that might be better for you.

Of course the book's code is java so the iTextSharp.dll helps make uses it in vb.net easier.

To Kenneth..
Sir, I have been very lucky to find your contribution to the post on VBAX on the watermarking from VBA subject.
I have then been easily able to adapt and use your code example to the "addWatermarkFromFile" case.
Unfortunatelly although i thought adding a text would be quite straight forward, I keep struggling trying to watermark a selected text to a selected page using the AddWatermarkFromText function.
The only stage I could make is with the simple line
jsObj.AddWatermarkFromText Mytext
but of course the result is a large text centered on all pages...
I would appreciate from you, embedded into a vba code, an example of the command line with all the various parameters as described in the documentation....which works!!
Thank you in advance for your help

Thank you for this excellent information! I was able to use it with very little modification using MS Access vba. My target pdf was not getting the watermark on all pages, and the syntax was hard to find on the internet. Here is what I learned:

'transparent but only page 1 gets the mark:
jsObj.addWatermarkFromFile WatermarkPDF_AX, 0, 0, 0, False, True, True, 0, 0, 0, 0, False, 1, False, 0, 1

'transparent, and all pages marked :
jsObj.addWatermarkFromFile WatermarkPDF_AX, 0, 0, 100, False, True, True, 0, 0, 0, 0, False, 1, False, 0, 1

'all pages, but not transparent:
'jsObj.addwatermarkfromfile WatermarkPDF_AX