Mail merge to PDF

[hughess7]

Hi all

I have a word template I need to print to directly from Access via a button.
I then want the new word doc to be saved as a PDF (and Word closed), all
without any user intervention - is this possible please? I am thinking of
using the PDF995 software to manipulate the PDF in code. I know how to do
this with an Access report but I have never done it via Word.

Can anyone help with this, or should I be asking the question on the Word
forum?

Thanks in advance for any help.
Sue

 

[hughess7]

One extra point worth noting, this will be used on lots of different
standalone computers and all our users have a variety of word pro versions -
at least 2000, 2003 and 2007.

I think I have found the following to print to the default printer but I am
not sure if this will work with all versions of Word? I am not sure how to
specify the PDF printer without having a dialog box pop up for the user to
choose from which I don't want to happen:

ActiveDocument.PrintOut Background:=False

Thanks in advance for any help.
Sue

 

[Mark Andrews]

I think there is code to change the default printer on "the access web".

Our email module has code for word automation and code to manipulate the
registry for win2pdf
to automate pdf printing (and stop the dialog from coming up). We designed
it for use in printing Access reports to PDF, but
the concept should apply to printing Word documents, so you just need
to change that so your code prints the word document via word automation.

I believe win2pdf has a product to automate word to pdf files (not sure if
this might help or not).

Couple all these things together and I think you have a solution.

HTH,
Mark
RPT Software
http://www.rptsoftware.com

 

[Albert D. Kallal]

Any particular reason why you don't just build a report in ms-access, and
send that to a pdf directly?

The advantage of the above is that then you eliminate word.

And, if you grab Stephan's pdf maker here:
http://www.lebans.com/reporttopdf.htm


The above means you don't have to install a PDF printer driver.
The above means you don't have to switch printers.
The above means you don't have to even had word installed....
The above sample will work with 2000 to 2007, and you not even have to
install pdf software...

In other words, we eliminate quite a LARGE number of steps and problems.
When you eliminate so many possible failure points, then you substantially
increase the reliability of your application.

Throw in a pdf driver...that is some again more support and setup time.
(and again a possible point of breakage in your software).
Throw in word, and again you have another point of breakage in your
software.

I would work hard to try and use a report and send that to a pdf using
Stephans pdf code....

 

[hughess7]

Oh how I wish it was that simple!!! I have already tried to reproduce the
word template the guys use as their audit report, to cut out word completely
to simplify matters. But it is a design nightmare and not really practical.
The word report is several pages long consisting of sections and paragraphs
of 'boier text' with calculations / values etc inserted from the database in
the middle of sentences.

eg The total found was £#.## which equates to an error level of #%. etc etc
etc

Just aligning all the paragraphs of 'boiler text' is tedious but trying to
insert values in alignment is impossible! I actually read a post on this
forum asking for advice on a similar scenario to use access or word template
and you advised on word 'hands-down'. I can get a mail merge to work but it
produces a new document and then I need to secure it somehow so it can not be
amended by an end user. I thought the most appropriate way to do this would
be to have the end result produced in PDF, direct from the Access db, as I
have read there are ways round a document that is password protected - eg
save as etc.

Help!

Sue

 

[Graham Mandeno]

Hi Sue

The easiest way to print a Word document to a specific printer is not to set
the system-wide default printer (which you would need to set back again),
but to use the ActivePrinter property of the Word.Application.

wdApp.ActivePrinter = "PDF995"
wdApp.ActiveDocument.PrintOut Background:=False

I take it you know how to manipulate PDF995 through its INI files to output
the PDF to a specified file?

Also, if you are using multiple versions of Word, I STRONGLY recommend you
use late binding.

This means that your Access VBA project contains no reference to the Word
object library. Instead, you create the COM object on the fly and the
references to object properties and methods are resolved at run-time.

This means you must declare all Word object variables "As Object". So
instead of:
Dim wdApp As Word.Application
Dim wdDoc As Word.Document
...
Set wdApp = New Word.Application
you would need to say:
Dim wdApp As Object
Dim wdDoc As Object
...
Set wdApp = CreateObject("Word.Application")

Note that CreateObject is preferable in any case because it works with both
early and late binding.

Late binding has some disadvantages, notably that you lose the intellisense
which gives you a nice list of methods and properties when you type an
object variable, and you lose the built-in constants that are defined in the
object library.

I use conditional compilation to get the best of both worlds.

At the top of your module, add a compiler constant:

#Const EarlyBinding = 1

Then, in any procedure where you use these objects, put a structure like
this:

#If EarlyBinding Then
Dim wdApp As Word.Application
Dim wdDoc As Word.Document
Debug.Assert wdDialogFilePrint = 88
Debug.Assert wdSendToNewDocument = 0
#Else
Dim wdApp As Object
Dim wdDoc As Object
Const wdDialogFilePrint = 88
Const wdSendToNewDocument = 0
#End If

In other words, when you are late binding, change the declaration of each
object to "As Object" and declare any constants you use, but when you are
early binding, declare the objects as what they really are and use
Debug.Assert to verify you have the correct value for each constant.

When you are ready to deploy your application, simple remove the reference
to the Word object library and change the constant declaration to:

#Const EarlyBinding = 0

 

[hughess7]

Hi Graham

Thanks for this!! Yes I have manipulated PDF995 before via its INI files to
output the PDF, so I should be able to look back at some existing code I have
to help me with this.

I will have a look at your advice and see if I can get something working
using late binding. Cheers, Sue

This means that your Access VBA project contains no reference to the Word
object library. Instead, you create the COM object on the fly and the
references to object properties and methods are resolved at run-time.

This means you must declare all Word object variables "As Object". So
instead of:
Dim wdApp As Word.Application
Dim wdDoc As Word.Document
...
Set wdApp = New Word.Application
you would need to say:
Dim wdApp As Object
Dim wdDoc As Object
...
Set wdApp = CreateObject("Word.Application")

Note that CreateObject is preferable in any case because it works with both
early and late binding.

Late binding has some disadvantages, notably that you lose the intellisense
which gives you a nice list of methods and properties when you type an
object variable, and you lose the built-in constants that are defined in the
object library.

I use conditional compilation to get the best of both worlds.

At the top of your module, add a compiler constant:

#Const EarlyBinding = 1

Then, in any procedure where you use these objects, put a structure like
this:

#If EarlyBinding Then
Dim wdApp As Word.Application
Dim wdDoc As Word.Document
Debug.Assert wdDialogFilePrint = 88
Debug.Assert wdSendToNewDocument = 0
#Else
Dim wdApp As Object
Dim wdDoc As Object
Const wdDialogFilePrint = 88
Const wdSendToNewDocument = 0
#End If

In other words, when you are late binding, change the declaration of each
object to "As Object" and declare any constants you use, but when you are
early binding, declare the objects as what they really are and use
Debug.Assert to verify you have the correct value for each constant.

When you are ready to deploy your application, simple remove the reference
to the Word object library and change the constant declaration to:

#Const EarlyBinding = 0

--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

Oh how I wish it was that simple!!! I have already tried to reproduce the
word template the guys use as their audit report, to cut out word
completely
to simplify matters. But it is a design nightmare and not really
practical.
The word report is several pages long consisting of sections and
paragraphs
of 'boier text' with calculations / values etc inserted from the database
in
the middle of sentences.

eg The total found was #.## which equates to an error level of #%. etc
etc
etc

Just aligning all the paragraphs of 'boiler text' is tedious but trying to
insert values in alignment is impossible! I actually read a post on this
forum asking for advice on a similar scenario to use access or word
template
and you advised on word 'hands-down'. I can get a mail merge to work but
it
produces a new document and then I need to secure it somehow so it can not
be
amended by an end user. I thought the most appropriate way to do this
would
be to have the end result produced in PDF, direct from the Access db, as I
have read there are ways round a document that is password protected - eg
save as etc.

Help!

Sue

 

[hughess7]

Hi Albert

I have been reading your word merge continued page and it would appear that
it might be able to achieve all I need?

MergeNoPrompts strFromDocTemplate, [strDir],
[bolFullPath],[strOutputDoc],[strsql],[bolPrint],[strPrinter]

From what I read, using the above, you can create a word doc, prints it and
close without saving the word doc. Could this be tweaked to print to an
installed PDF995 printer, instead of a normal printer?

Thanks in advance for any help.
Sue

 

[hughess7]

Slight progress, it now sends the doc to PDF995 but it just sits in the queue
and doesn't get produced...

Hi Albert

I have been reading your word merge continued page and it would appear that
it might be able to achieve all I need?

MergeNoPrompts strFromDocTemplate, [strDir],
[bolFullPath],[strOutputDoc],[strsql],[bolPrint],[strPrinter]

From what I read, using the above, you can create a word doc, prints it and
close without saving the word doc. Could this be tweaked to print to an
installed PDF995 printer, instead of a normal printer?

Thanks in advance for any help.
Sue

Any particular reason why you don't just build a report in ms-access, and
send that to a pdf directly?

The advantage of the above is that then you eliminate word.

And, if you grab Stephan's pdf maker here:
http://www.lebans.com/reporttopdf.htm


The above means you don't have to install a PDF printer driver.
The above means you don't have to switch printers.
The above means you don't have to even had word installed....
The above sample will work with 2000 to 2007, and you not even have to
install pdf software...

In other words, we eliminate quite a LARGE number of steps and problems.
When you eliminate so many possible failure points, then you substantially
increase the reliability of your application.

Throw in a pdf driver...that is some again more support and setup time.
(and again a possible point of breakage in your software).
Throw in word, and again you have another point of breakage in your
software.

I would work hard to try and use a report and send that to a pdf using
Stephans pdf code....