Print all open docs

[Joanne]

I have an access app that opens 8 to 10 docs in MSWord.
Am using office 2000 on winxp machine

The docs open just fine for viewing.

Now while they are open I want to give the user the opportunity to
print all of the open documents on one click of the mouse.

How do I code msword to print all open docs, such as:

print doc
close doc

print the next doc
close the next doc

etc

close app (This I know how to do

This is what I tried, by just calling to the docs from the folder and
printing them, and then I would have closed all the files and exited
the application, but it doesn't work for me.

Sub PrintAllDocs()
Dim sMyDir As String
Dim sDocName As String
' The path to obtain the files.
sMyDir = "C:\NMWorkPkg\Benefits\"
sDocName = Dir(sMyDir & "*.DOC")
While sDocName <> ""
' Print the file.
Application.PrintOut FileName: = sDocName
' Get next file name.
sDocName = Dir()
Wend
End Sub

I found this snippet of code thru a google search and adapted it to my
scenario.

Anyway, I am getting no where right now and sure could use a little
nudge of help

Thanks for your time and consideration of my problem

 

[Helmut Weber]

Hi Joanne,
like this from Excel:
Sub PrintAll()
Dim oWrd As Object
Set oWrd = GetObject(, "Word.Application")
Dim oDcm As Object
For Each oDcm In oWrd.Documents
oDcm.PrintOut
oDcm.Saved = True ' or save it if you like
oDcm.Close
Next
End Sub

 

[Joanne]

Thanks for your very prompt reply
You have made my day look much easier as a result of your coding help.
Bless you

 

[Malcolm Smith]

Just a point; this will Close all Word documents. Does one wish to do
this?

- Malc

 

[Joanne]

Malcolm
Thanks for your concern.
Yes, closing the docs is what I want to happen. I will also be closing
the app at the end of printing.
I do this because I am running this from my Access project, and I want
the user to go back to the user interface after the print job.

Might I ask you a question here.
I need help on how to call my MSWord 'PrintAll' macro from access.
Here's the scenario

User clicks on group name in access, all files in group open up in
MSWord for user to view. When they are here, they can click on the
'PrintAll' button, and the macro prints out all the docs, closes them
and quits msword, returning to my user interface in access as
expected. I got that working nicely now.

But because there will be times when I want msword to run at visible =
false since the user does not want/need to view docs but wants to
simply send them to the printer, I need to programmatically call the
MSWord 'PrintAll' Macro we created from Access and have it do the
print job in the background.

I looked it up in help, but they talk about references and when I
check tools/references, vb, msword and msaccess references all are
checked.

I lack the basic knowledge to help me find out where to look next or
what to do next.

Any help is received appreciatevely

 

[Malcolm Smith]

Joanne

You are only one line away from being there yourself!

In fact Helmut sent you the correct code. If Helmut doesn't mind me
repeating his code we have:


Sub PrintAll()
Dim oWrd As Object
Set oWrd = GetObject(, "Word.Application")
Dim oDcm As Object
For Each oDcm In oWrd.Documents
oDcm.PrintOut
oDcm.Saved = True ' or save it if you like
oDcm.Close
Next
End Sub


What happens here is that there is a pointer, oDcm, which goes through
each open instance of a Word document and then the oDcm.PrintOut is the
actual printout method of that document object.

If you don't do it his way exactly, then you still need a pointer to each
Word document.

- Malc
www.ukhorseracing.co.uk

 

[Joanne]

Malcolm
My kudos to Helmut for sure for his code, and to you also for helping
me understand what is going on in the code.

Are you telling me that this subroutine should be in my access
project? I put it into my msword as a macro and now I don't know how
to call it up from access to print in the background.

Does the line

Set oWrd = GetObject (, "Word.Application")

open an instance of word to do my printing or does it merely point to
word and allow the access project to print word docs?

Bottom line is that I am not sure if this code should be in my access
project module, or as a macro in msword.

Thanks again for your time and consideration

 

[Joanne]

Helmut
Your code works sweet - everything prints out nicely. I am in a
conversation with Malcolm and we are using your code - I hope that is
okay - I don't mean to have bad net manners here so please forgive me
if I do.

I would like you to explain to me, if you have a moment to, what the
line
'oDcm.Saved = True' does.
Actually, I do not want to save the docs after printing and before
closing them, as they will be full of bookmarks being filled in by
data entered in Access, and it is a one time dealy, so all data needs
to be dumped and the bookmarks empty and ready to receive the next
batch of data.

I'm a little confused because the line says odcm.save = true, but your
comment says 'or save it if you like'. Does the comment mean that to
save it the line needs to be oDcm.Saved = False?

I'm really trying to learn what is going on here so that I can begin
to get some basic understanding and maybe be able to do some of these
things on my own someday.

Thanks so much for your time and help. It is truly appreciated.

 

[Chad DeMeyer]

Joanne,

When you print a document in Word it tends to do things like update fields,
etc., that make Word think the document is "dirty", i.e., the document has
unsaved changes. oDcm.Saved = True is a way of fooling Word into thinking
that the document is clean, so when you close the document programmatically
no prompt to save changes will be displayed.

Regards,
Chad

 

[Joanne]

Chad
Thank you so much for the explanation. It is so helpful to get answers
on this message board - and explanations of how and why are the cherry

 

[Malcolm Smith]

Joanne

This code lives in any VB or VBA application. It could live in Excel,
Access, Visio, AutoCAD and one of a few hundred other applications.

Basically what it does is to grab a running instance of Word. This can
be seen in the line:

Set oWrd = GetObject(, "Word.Application")

If Word is running then a POINTER to the Word's application will be set.
This pointer is called oWrd.

When you install an application on your PC one of the many things it does
is to REGISTER itself. This means that commands like GetObject() and
CreateObject() can be passed the name of the application and the operating
system sorts out what is required and sets up the pointers and passes them
into, as in this case, oWord.

In another example you could have:

Set oXL = CreateObject("Excel.Application")

this will create a new instance of Excel (even if one is running) and then
set the oXL to 'point' that that new instance.

Now back to the code.

The line:

For Each oDcm In oWrd.Documents

creates a loop and goes through each of the open documents within the oWrd
application. Not only that; it holds a pointer to each Document in
turn. This pointer is called oDcm.

Now you have a REFERENCE to each Document and you can do whatever you want
with that document as if you were in Word's VBA. Where in Word you would
type ActiveDocument you would reference each document as oDcm.

So you can print out the document: oDcm.Printout
You can save the document: oDCm.Save
You can count the number of tables: MsgBox oDCm.Tables.Count

Okay? So, in short. You use this code anywhere outside of Word as it will
pick up the running Word application.

Clear? Or have I made it worse?

- Malc
www.dragondrop.com

 

[Joanne]

Malcolm
You are definitely NOT making things worse. I am getting much clearer
on what is going on here.

So I can run this code from my access project module, but I first must
run my code to open the docs in msword so that access can 'grab the
running instance of word', then access will run the rest of the
subroutine to print the docs and close them without a save (or
whatever else I may require), all being done from inside my access
project, NOT needing coding in my MSword macro module.

Am I getting it straight? Actually, this is really exciting to know
about, my head is banging around lots of things that I think will help
me be more efficient.

How do you know these things? I want to know all of them also

Thank you very much

 

[Malcolm Smith]

Joanne

If the documents aren't open already then why not open them all from
Access and work on them from there.

To do this you need to create a new instance of Word. You could look for
an existing instance but why trample all over what the user may be doing.

So, you could start off with this instruction somewhere in Access:

dim oWord as Object


set oWord = CreateObject ("Word.Application")

(or whatever variable pointer name you may choose to call it. Helmut
calls it one thing, I tend to put a little 'o' in front of my object
pointers.


Then you get the list of Word documents, let's guess that you do it by
some sort of SQL call and we have an Access RecordSet object, which I will
call oRS. So:

Set oRS = OpenRecordset (........)
if not oRS is Nothing then
do while not oRS.EOF

oRS.MoveNext
loop
end if


This ought to be bog-standard Access code which you are familiar with.
In the middle of this loop you could write, for example:


sFileName = "" & oRS("FileName")
if len(Dir$(sFileName)) > 0 then
Set oDoc = oWord.Documents.Open
if not oDoc is nothing then
oDoc.Printout
oDoc.Saved = True
oDoc.Close
end if
end if

And then, at the end don't forget to close word down with:

oWord.Quit

And that should open one document in your list at a time and then print
it before closing it.

The Len(Dir$()) business is there to make sure that the file exists.
When I check to see if oDoc is not nothing I wanted to make sure that the
document was opened; after all something could have gone wrong.


How do I know all this gubbins? Crikey! I've been playing with computers
since the early 70s and coding most of my life. I may not be able to put
up a set of shelves, but I can bake a cracking loaf and write code.

- Malc
www.dragondrop.com / www.weakfavourites.co.uk

 

[Joanne]

I understand a lot of what you have here.
First, open an instance of word to allow us access to the docs.

Then open the docs (using sql, I'll have to investigate how to do that
part).

After I get the files open, how do I set the open docs to a recordset,
or does access do this for me?

Then we set the object recordset (oRS) to be equal to the Recordset we
have the files listed in

Set oRS = OpenRecordset (..RS Name here?......)

Checking to be sure the recordset contains files

if not oRS is Nothing then
do while not oRS.EOF

you are then say that while the recordset is not empty, do this:


Here I am a bit shakey, but I think you are setting a variable to
catch the names of the docs from the recordset, and while there are
still open docs in sFileName, you are printing one, stopping the save
(tricking MSWord, I LOVE it), closing the doc, and when at the end of
the array, closing the instance of word.

sFileName = "" & oRS("FileName")

if len(Dir$(sFileName)) > 0 then
Set oDoc = oWord.Documents.Open
if not oDoc is nothing then
oDoc.Printout
oDoc.Saved = True
oDoc.Close
end if
end if

oWord.Quit

How am I doing? You're a good teacher!

 

[Joanne]

Well, I didn't do as well as I'd hoped.

I have 7 folders on drive c, each containing a number of docs.

In access, I built a table for each folder, listing the filname.doc
(Do I need to put the full path to the filenmae in the table?)
THen I ran a query on the table, and copied the sql statement the
query generated into a public sub I inserted into my module. Could not
get anything to work this way - now I understand that this method was
not running the sql statement to get the recordset.

I'm thinking now after your further explanations here that I should
base my 'on click' event of the "print" command button and it will
run the query to get the recordset, then use and adapt the coding you
gave me yesterday to iterate thru the docs to print & close the docs,
then exit word, being sure to close all open objects. It seems to me
that I should put the full path to the files in the table with the
filename, else how will access know where to point to get the files.
Am I correct in this?

Thanks for you patience with me on this. I am trying to learn this
stuff but it takes a bit to get it to sink in and stay there for me.
It is like working a puzzle, and very gratifying when somethin does
what I want it to do.

 

[Malcolm Smith]

Well, I didn't do as well as I'd hoped.

I have 7 folders on drive c, each containing a number of docs.

In access, I built a table for each folder, listing the filname.doc
(Do I need to put the full path to the filenmae in the table?)
THen I ran a query on the table, and copied the sql statement the
query generated into a public sub I inserted into my module. Could not
get anything to work this way - now I understand that this method was
not running the sql statement to get the recordset.

I'm thinking now after your further explanations here that I should
base my 'on click' event of the "print" command button and it will
run the query to get the recordset, then use and adapt the coding you
gave me yesterday to iterate thru the docs to print & close the docs,
then exit word, being sure to close all open objects. It seems to me
that I should put the full path to the files in the table with the
filename, else how will access know where to point to get the files.
Am I correct in this?

Yes, I would. Then the database can be placed anywhere and the files can
still be found.

Thanks for you patience with me on this. I am trying to learn this
stuff but it takes a bit to get it to sink in and stay there for me.
It is like working a puzzle, and very gratifying when somethin does
what I want it to do.

I do think that you are getting there. Though I wouldn't have one table
per folder. I would have table with all of the information in there and
then another field describing that folder or the category of documents.
What happens if, for example, you find this is a success and you are asked
to do the coding for a lot of departments and you end up after a year or
two with five hundred folders?

One table. Just add more fields to it so that you can SELECT via your SQL
statement to get the files that you want.

- Malc
www.dragondrop.com

 

[Joanne]

"I do think you are getting there"
HIGH PRAISE and encouragement from a patient and good teacher. I thank
you.

I will get all docs in one table and add a field to give me a spot to
identify which folder the file belongs to as you suggest, and I will
add the entire path to each doc name to (hopefully) make my life
easier. (Probably this is really important because I will be shipping
this little app to various satellite offices around the country and
when I do I am going to set it up so that they just copy the whole
thing to C:\ - then I know the files will be setup for access to find
them on each machine.) What a treat it would be if my little app was
successful and I actually needed to set it up for other depts. WOW!
You are used to this kind of success in programming, having been in at
it for 30+ years. For me, it is new and a real treat to see it fly.

Going to make the changes. Will post what happens.

I can build shelves (woodworking is a hobby of mine) and I can make a
'cracking' loaf of bread (love to cook) but I'm not so good on the
'bog-around' access programming, YET!!!

Thank you very much Malcolm

Joanne

 

[Malcolm Smith]

Good stuff there, Joanne. What sort of firm is it that you work for?

(Off to bake a loaf with goat's milk cheese in it and to peel a bottle of
wine!)

- Malc
www.dragondrop.com

 

[Joanne]

Malcolm
Here is what I have come up with to print the docs, but I can't check
it out because it keeps stopping on the 'FROM' word in my sql
statement.

Could you look at this procedure and tell me what's wrong. I have
looked at the help files expamples of sql code and realize I can't
just copy/paste from the query sql statement (that didn't work either
so I went investigating) and I tried to use the proper syntax but I am
clearly doing something wrong or else it would fly for me.

Public Sub CmdPrnBenefits_Click()

'Procedure gets recordset based on sql statement,
'opens, prints & closes the files, quits MSWord instance

Dim oWrd As Object
Set oWrd = CreateObject("Word.Application")
Dim oDoc As Object
Set oRst = OpenRecordset

DoCmd.RunSQL "SELECT tblDocumentList.GroupName"
FROM tblDocumentList
WHERE (((tblDocumentList.GroupName) = "Benefits"))

If Not oRst Is Nothing Then
Do While Not oRst.EOF

sFileName = "" & oRS("FileName")
If Len(Dir$(sFileName)) > 0 Then
Set oDoc = oWrd.Documents.Open
If Not oDoc Is Nothing Then
oDoc.PrintOut
oDoc.Saved = True
oDoc.Close
End If
End If

oWord.Quit

End Sub

I work for a job placement firm and this app is to create the work
package documents (65 docs) for new hires. After I get this print
function working, all I have to do is set bookmarks in the docs and
code access to drop the user input info into the docs before printout.
I really begin to wonder if I should have made a user input form in
msword and just kept the whole thing in word, but I love access and
decided to go this way. Phew, has got me in a bit of a sweat though.

 

[Malcolm Smith]

DoCmd.RunSQL "SELECT tblDocumentList.GroupName"
FROM tblDocumentList
WHERE (((tblDocumentList.GroupName) = "Benefits"))

For a start the SQL statement is only "SELECT tblDocumentList.GroupName"
and that's not complete. It doesn't say which table and what are the
WHERE conditions.

Secondly you have two interesting statements:
FROM tblDocumentList
WHERE (((tblDocumentList.GroupName) = "Benefits"))
which are never going to get past the compiler.


Thirdly, why not make the SQL statement something like:

SQL = "SELECT tblDocumentList.GroupName FROM tblDocumentList " & _
"WHERE (((tblDocumentList.GroupName) = """ & _
sGroupName & """));"

Where sGroupName is the name of your group.


Fourthly, you have a RecordSet object. You will need to link that to a
table or to a Query. Which query? Well, what about the one in the SQL
string?

Fifthly, you haven't declared oRS nor have you opened it. You have
created the object with the Set command but you need to open it with a
Table or a Recordset first.


Your problem now isn't Word programming but now there is an issue with how
you access a database. So, you need to look up some sample code on how
to open a recordset which is based on a query.

Hopefully this lot should set you on the right path.

- Malc
www.weakfavourites.co.uk