Excel PDF Printing

[SteeleTheShow]

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?

[Kenneth Hobson]

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().

[SteeleTheShow]

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

[Kenneth Hobson]

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.

Please Login or Register  to view this content.

[SteeleTheShow]

This is great, thanks for the help!

This is almost exactly what I need and is definitely pointing in the right direction and I can adapt it to my spreadsheet. A problem I'm running into though is that I need to format the area where the watermark will be placed.

I'm looking at this line:

Please Login or Register  to view this content.

and I'm hoping you can provide more syntax for this?

Basically I need to modify these properties
Capture.JPG

[SteeleTheShow]

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?

[SteeleTheShow]

I found the syntax for the addWatermarkFromFile command!

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

[Kenneth Hobson]

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

[SteeleTheShow]

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?

[Kenneth Hobson]

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.

[SteeleTheShow]

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.

Please Login or Register  to view this content.

Thanks!

[Kenneth Hobson]

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.

[SteeleTheShow]

It looks like the error is occurring at

Please Login or Register  to view this content.

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

[Kenneth Hobson]

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

[SteeleTheShow]

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

Please Login or Register  to view this content.

needs to be placed inside and if statement like this

Please Login or Register  to view this content.

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

[SteeleTheShow]

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.

[Kenneth Hobson]

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.

[SteeleTheShow]

OK so I'm pretty close to completion with this project!

I'm running into a very strange issue though. When I make the pdf and insert the watermark on it, the pdf file looks just fine, but when I actually print the pdf, the watermark is rotated 90 degree but the rest of the pdf prints in landscape. Any ideas? I've tried multiple different printers.

[SteeleTheShow]

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.