Excel PDF Printing

I'm not sure if this is a problem that excel VBA can solve or not.

So I have an excel file with a dozen or so worksheets. One page is inputs, then there are 2 different types of outputs and the rest are a bunch of tedious calculations and lookup tables.

Currently, we use this default excel file to just enter the data on the inputs tab, then we copy a section of the relevant output tab and paste it to AutoCAD as an OLE object. Then we print the AutoCAD drawing to PDF. The AutoCAD drawing contains a standard layout and default drawing just to demonstrate the geometry.

The PDF is printed from AutoCAD and has layers that can be activated or hidden from the pdf. There's a drawing at the top, and information about the project at the side. The red box is where I want the cells from excel to be.

Capture.JPG

I would like to cut out having to copy this to AutoCAD and instead print it straight to PDF. Is there any way to do this using excel vba? If not, does anybody have any suggestions?

If you have adobe acrobat, not adobe reader, then I could show you how to use one as a watermark.

If not, one can use 3rd party programs and pass command line parameters to do it via VBA's Shell().

I have Acrobat. That sounds like it might be easier than the shell method correct?

Using the acrobat object will be a bit more efficient but shelling to 3rd party programs can work fine too.

You may have to create a scratch worksheet to get it setup and sized to fit the area you have set.

' http://www.vbaexpress.com/forum/showthread.php?t=39616
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/Adobe/en/devnet/acrobat/pdfs/js_api_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

A second problem I'm having is that my watermark (data) may be longer than 1 page. Is there any way to have it copy the default page as many times as it needs in the pdf?

I found the syntax for the addWatermarkFromFile command!

http://help.adobe.com/livedocs/acrob...ccessible=true

Good deal. That is why I commented the link to download the SDK for the API so you can seen that kind of thing.

Is there any way to copy a pdf page multiple times using similar excel vba code? For example, my watermark may be many pages long. Can I have a pdf that is a single page and copy that page to the same pdf document based on the number of pages that my watermark is?

Watermarks should always be one page long. You can use code to find the number of PDF pages as-well-as doing such things as merging (concatenating) and splitting PDF files. Most of what you see the 3rd party programs doing, can be done by code.

So I've made some progress but I'm getting a compile error "Function call on left hand side of the assignment must return Variant or Object". Any ideas?

Right now all it's doing is opening a pdf, reading the number of pages, opening another pdf, asking how many pages are in it (because for some reason if it has more than 1 page then it returns a -1 which means it doesn't know). If the number you enter is larger than 1, it inserts the page from "OPC_backup" as many times as there are pages.

   
    Dim AcroApp As Acrobat.CAcroApp
    Dim Part1Document As AcroPDDoc, Part2Document As AcroPDDoc, Part3Document As AcroPDDoc
    Dim jsObj As Object
    Dim npgs1 As Integer, npgs2 As Integer
    Dim x As Integer
    
    Set AcroApp = CreateObject("AcroExch.App")
       
    Set Part1Document = CreateObject("AcroExch.PDDoc")
    Set Part3Document = CreateObject("AcroExch.PDDoc")
    Part1Document.Open (OPC)
    Part3Document.Open (OPC_backup)
    npgs1 = Part1Document.GetNumPages()
    npgs2 = InputBox("How many pages of Data is there?")
    x = npgs2 - npgs1
    
    For y = 1 To x
        Part1Document.InsertPages(0, Part3Document, 0, 1, False) = False
        y = y + 1
        Next y
    End

Thanks!

I don't have adobe on this computer so it would be tomorrow before I can test.

Of course InputBox() returns a string, not an integer.

Application.InputBox() gives you a bit more control.

Place your cursor in or next to a command word and press F1 to get specific help.

It looks like the error is occurring at

Part1Document.InsertPages(0, Part3Document, 0, 1, False) = False

It seems to me that the InsertPages function isn't recognizing Part1Document?

I don't see where OPC was set to a value.

Is there any way to manipulate layers on multiple pages at the same time? i have the same layers with the same names on multiple pages and I don't want to have to toggle them on every page of the document.

I don't have any advice for that. Maybe see a link in: http://lmgtfy.com/?q=VBA+adobe+layers+pdf

Yes, it makes sense that you would need an IF. Otherwise it would be like putting True or False as one line of VBA code which would be a syntax error.

Got it, I just changed the "OPC" to the actual file path and it liked that much better. Also, apparently

Part1Document.InsertPages(0, Part3Document, 0, 1, False) = False

needs to be placed inside and if statement like this

If Part1Document.InsertPages(0, Part2Document, 0, 1, True) = False Then
            MsgBox "Cannot insert pages"
        End If

Otherwise it doesn't work. That was the weird object error I was getting.

I figured it out. The pdf from autocad printed in portrait mode and I manually rotated it to landscape. However, it would appear that acrobat still recognized it as being in portrait mode so when I inserted the watermark, it was off by -90 degrees. I resolved it by leaving the autocad pdf in portrait then running my code.