Dynamic printing - Excel VBA 1

[ItIsHardToProgram]

ok so, I am thinking of something, that is probably possible, and that can probably be done, and I am looking for an idea, not a code (that I will try myself), in where I can get started on how to have a dynamic printing macro.

Let me put you in context:

I have a workbook, that has about 90 worksheets, each worksheet has a different pivot table, that references to the same data sheet.

What my workbook does, is that when you update the data sheet with new data,(run a macro) it takes the new data, reformats it in a new sheet, and that new sheet is what the pivot table feeds on.

Now, once this is done, I want a macro, that prints every sheet in a PDF to a specific location.

The format of the pivot tables might be expanded or retracted, depending on the number of new fields or deleted fields in the new data sheet (monthly). (some information on the right of the sheet must not be printed, that leaves the excel auto-detect print out of the question)

And so, my macro should be able to detect, weither or not the pivot table was expanded or not, and by that re-define the print area. I am really mystified on how to manage that.


My second problem is on how to approach the pdf print, we will make sure that we all have the same PDF pilot, located in the same place, so that it references correctly, and maybe have a prompt if the pilot is not the same, so that they can change it on will.

But will I be able to manage where to print the PDF without prompts?


Thanks for all of your ideas, very much appreciated, if It can't be done, Ill think of an alternative, maybe formated excel sheets with no formulas, only numbers (as an output to replace the pdf).











"Knowing that you know is the greatest sign of stupidity, knowing that you are ignorant is the best proof of intelligence.

 

[SkipVought]

hi,

"some information on the right of the sheet must not be printed"

How is that defined?

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[ItIsHardToProgram]

Good point, its standart on each sheet, always the same columns, so I could basicaly hide those.

Thanks, thats a great point.

What about the managing the PDF print location?

"Knowing that you know is the greatest sign of stupidity, knowing that you are ignorant is the best proof of intelligence.

 

[SkipVought]

Have you checked out HELP on the PrintOut Method?

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[ItIsHardToProgram]

I am familiar with the PrintToFile, and OutputFileName method, but will this print it as is in a pdf, and will it work with any type of PDF writing program, as I thought that the PDF program was a different entity that managed the printout location???

Thanks for your enlightments.

"Knowing that you know is the greatest sign of stupidity, knowing that you are ignorant is the best proof of intelligence.

 

[SkipVought]

"...will this print it as is in a pdf..."

Only if your have that printer driver conversion.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[ItIsHardToProgram]

This errors on OutputFileName saying it dosnt recognize the arguments...

ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, PrintToFile:=True, OutputFileName:="C:\Documents and Settings\jroy\My Documents\test.pdf

"Knowing that you know is the greatest sign of stupidity, knowing that you are ignorant is the best proof of intelligence.

 

[ItIsHardToProgram]

Oups, forgot the "

ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, PrintToFile:=True, OutputFileName:="C:\Documents and Settings\jroy\My Documents\test.pdf"

"Knowing that you know is the greatest sign of stupidity, knowing that you are ignorant is the best proof of intelligence.

 

[SkipVought]

Is your PDF Printer selected? At home I select my CutePDF printer to get a PDF file "printed"

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[PHV]

Anyway, have a look at the Application.ActivePrinter property.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[ItIsHardToProgram]

Yes, it was defined, it was by defualt, but upon research I stumbled across this.

"

http://www.tek-tips.com/viewthread.cfm?qid=416162"

And so it would seem that the problem nest's in the Acrobat distiller version 8.0

I shall see what I can do with the trial version of cutePDF and get back to you as soon as I can.


Thanks again skip,

"Knowing that you know is the greatest sign of stupidity, knowing that you are ignorant is the best proof of intelligence.

 

[ItIsHardToProgram]

Well although I am starting to annoy even myself, I stumbled accross this reading about CutePDF:

" DO NOT look for any application to run. Just print your document using CutePDF Writer (the printer) to get PDF output.

[highlight]Open your original document and select Print command in File menu of your application to bring up Print dialog box.[/highlight]
Then select CutePDF Writer as the Printer to print (DO NOT select "Print to file" option).
You will get a Save As dialog box prompted for saving created PDF file.
Select a folder to Save in and enter a File name, then click on Save.
Go to that folder to find your PDF file.
"

I do not want this, I want to specify the selected file for printing.

"Knowing that you know is the greatest sign of stupidity, knowing that you are ignorant is the best proof of intelligence.

 

[ItIsHardToProgram]

ActiveWindow.SelectedSheets.PrintOut ActivePrinter:="CutePDF Writer", Copies:=1, Collate:=True, PrintToFile:=True, OutputFileName:="C:\Documents and Settings\jroy\My Documents\test.pdf"

This does not fire....

ActiveWindow.SelectedSheets.PrintOut ActivePrinter:="CutePDF Writer", Copies:=1, Collate:=True, PrintToFile:=True

This fires but prompts me.

"Knowing that you know is the greatest sign of stupidity, knowing that you are ignorant is the best proof of intelligence.

 

[SkipVought]

Just a long shot.

Open tools > references and see if you can find a library for CutePDF. You may be able to add that library and use some property in that library for the file name. There may also be some online developer documentation; who knows.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[ItIsHardToProgram]

Theres one for adobe 7, but I did not see CutePDF anywhere.

Basicaly, what you are telling me is that CutePDF does not support the OutPutFileName wich is why it errors on that specific line??

"Knowing that you know is the greatest sign of stupidity, knowing that you are ignorant is the best proof of intelligence.

 

[SkipVought]

I do not know. I have never tried doing that.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[ItIsHardToProgram]

Ok, Ill see if I can get my hand on a Adobe 5.0 pdf writer version, wich would render this alot easier than it has to be.

On the other hand, I've seen people say that CutePDF could do this, but I can't seem to make it work, and I am prety certain that theres no error in that line of code...

Ill ponder on this some more when Ill have time, So ill stick this thread and leave it open for further information any one might add,

Thanks alot Skip, I love squeezing the juice out of your brain, its always a great substance.

"Knowing that you know is the greatest sign of stupidity, knowing that you are ignorant is the best proof of intelligence.

 

[PHV]

Have a look here:

http://www.cutepdf.com/Solutions/pdfwriter2.asp#Module

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[innov]

I have searched for an answer to this for years as well. Here's what I know.
PDFWriter in Acrobat 5.0 DOES let you use the PrintToFile properties of the PrintOut method. Releases 6.0 and on require either: (a) user intervention (the saveas dialog); (b) use the Distiller COM object which requires 2 steps (1 - create a postscript, and 2 - call distiller to convert the postscript to PDF); or (c) modify the registry for the PDF file location. At this point, I too have chosen to continue to use Acrobat 5.0.

FYI - You can do a custom install of the 5.0 PDFWriter ONLY and continue to use the rest of a later Acrobat relase (i.e. 6.0 thru 9.0)

I continue to look for a PDF print driver that allows destination control via automation.

Hope this helps.

 

[ItIsHardToProgram]

Well, we have decided to split the pear in half and took half of the pear, or should I say a crum of it.

Our solution was to simply create quarterly reports, and reduce the number of reports from 90 to 6, as it would be alot more costly to manage all those reports, instead of simply managing a small amount and having an outstanding reports, that reports anomalies.

Thanks for your help all.

Julien ~

"Knowing that you know is the greatest sign of stupidity, knowing that you are ignorant is the best proof of intelligence.