Print all open docs

[Joanne]

Thanks for the pointers Malcolm. I'll get right on it. I am
determinded to make this work and in the meantime I'm learning about
sql statements in access, and printing all docs and automating MSWord
and using recordsets. Oh My!

 

[Malcolm Smith]

Well, the Word part is there, I think.

You just need how to query a recordset in Access and to step through the
records in the recordset. And then you're home and dry.

Right, that's it for me now. Time to watch the Wales game.

- Malc

 

[Joanne]

Malcolm
Did a lot of searching and this is what I have come up with.
I think that I have successfully linked the recordset to the sql
statement

Public Sub CmdPrnBenefits_Click()

'Procedure gets recordset based on sql statement,
'opens, prints & closes the files, quits MSWord instance
Dim db As dao.Database
Set db = CurrentDb()

Malcolm

This is what I have been able to find in my search for a solution. It
still isn't doing anything, and I'm not sure why

Dim oRst As dao.Recordset ---- Declaring the recordset
Set oRst = dao.Recordset ------ creating the recordset object

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

Dim oDoc As Object
Dim sSql As String
Dim sGrpName As String ------- I have a feeling that this variable is
part of my trouble


'Create sql statement to retrieve filenames
sSql = "SELECT tblDocumentList.GroupName FROM tblDocumentList " & _
"WHERE (((tblDocumentList.GroupName) = """ & _
Benefits & """));"
This, I think, has created the recordset of filenames with
groupname of 'Benefits'


'Set the recordset equal to the sql return
Set oRst = db.OpenRecordset(sSql) ---------- This links the sql
statement to the recordset

'Check to be sure there are records in the recordset
If Not oRst Is Nothing Then
Do While Not oRst.EOF

sGrpName = "" & oRst("GroupName") ---- I'm thinking this is where
my problem is in the code -
I'm not sure how to use the
variable to get the list of
filenames from the
recordset

If Len(Dir$(sGrpName)) > 0 Then
Set oDoc = oWrd.Documents.Open
If Not oDoc Is Nothing Then
oDoc.PrintOut
oDoc.Saved = True
oDoc.Close
End If
End If
Loop
End If

oWrd.Quit
oRst.Close
Set oRst = Nothing

End Sub

Who are the Wales and what game do they play? I am located in the
Midwest section of the United States - from some of your slang I
figure you are located somewhere outside the US - and I am a real
sports enthusiast but don't recognize the team name "Wales" Anyway,
enjoy your game and I hope your team wins.
Joanne

 

[Malcolm Smith]

Did a lot of searching and this is what I have come up with.
I think that I have successfully linked the recordset to the sql
statement

You are so close. If this were a game of Hot and Cold you'd be on fire.
Right, look at this SQL query and you can see two small problems.

'Create sql statement to retrieve filenames
sSql = "SELECT tblDocumentList.GroupName FROM tblDocumentList " & _
"WHERE (((tblDocumentList.GroupName) = """ & _
Benefits & """));"
This, I think, has created the recordset of filenames with
groupname of 'Benefits'

The first is that this 'Benefits' is not a string. You need to shove this
into a variable, say sGroupName, like so:

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

Now you need to populate the variable sGroupName with the string,
"Benefits", elsewhere. Perhaps that can come from a list box or
something:

sGroupName = oForm.lstGroups.Value

I don't know where you want to get that variable from, but this is beyond
the scope of this problem.

The next problem is the SQL statement. It's best to read it out aloud to
oneself. If you read the statement you will be saying "Select all the
records from 'tblDocumentList' where the Groupname is "Benefits" and show
me all the, er, Groupnames".

This last bit is where you are going wrong. You know what the groupname
is so why ask for all the groupnames where the groupnames are 'Benefits'?
Clearly this is nonsense and what you need is to be shown all of the
filenames.

So, let's change the SQL statement to:

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

and this ought to do what you want.

So, when you open the recordset you will then have a pile of filenames of
all the documents which are in the Benefits section. Or whatever section
you choose to use.

Who are the Wales and what game do they play? I am located in the
Midwest section of the United States - from some of your slang I
figure you are located somewhere outside the US - and I am a real
sports enthusiast but don't recognize the team name "Wales" Anyway,
enjoy your game and I hope your team wins.

Wales. It's a country.

You've probably never heard of it as it doesn't have oil and so it won't
need liberating. Tonight was the start of the qualifying rounds of the
football World Cup which will be played next summer in Germany. Wales was
over in Azerbaijan this evening for their qualifying match and managed to
scrape a 1-1 draw...

Wales' traditional game is rugby but, sadly, their glory days of the 70s
have passed into memory and legend.


Hope that this helps
- Malc

 

[Joanne]

Malcolm
thanks again - I will implement the changes and see how it goes.

You are right, reading the sql statement out loud helps to understand
what it is saying

Wales. It's a country.

You've probably never heard of it as it doesn't have oil and so it won't
need liberating.

Ouch!! I am not a politician!

Of course I know Wales - The medieval history of western europe is
another hobby of mine. Love the study of the kings and queens and
their royalty business, the wars and shifting of power, especially in
the middle ages. Have read quite extensively on the subject - just
wish I could get over there and see the different physical locations
I've read about with me own eyes.

I just assumed that 'Wales' was the knickname of your favorite team.
Isn't Azerbaijan the location of a Harry Potter book? I read them with
my 9 year old grandson, and I seem to remember that.

Is this football as played in America, or what we here call soccer,
which I know is known as football in many parts of the world? And
which is becoming increasingly popular here in the states.

So a 1-1 Draw - what does it mean to them as far as getting qualified
for the world cup? Are they still in the fray?

Tonight was the start of the qualifying rounds of the

 

[Joanne]

Malcolm
I said I would post when I solved my problem but I cannot solve it
yet.
I feel like I am spinning in circles and am missing some small
understanding that would make the thing work.
Here is the code as best as I understand what you gave me and what I
have been able to gleam from my searching around the help files and
the internet.
Keep getting messages regarding object and variable and method like
'object doesn't support this method' and type mismatch all related to,
I think, oDocName. Have changed so many things trying to find what is
wrong that I have no clue. May have to abandon the deal and do it the
'rookie' way of opening each doc by hardcoding, printing and closing
the same way. Yuk!

Here is the code for the printall routine

Public Sub PrintAll()
Dim oWordapp As Object
Set oWordapp = CreateObject("Word.Application")
Dim oDocName As Object
Dim oRst As DAO.Recordset
Dim sSql As String

sSql = "SELECT tblBenefits.DocName from tblBenefits"

Set oRst = CurrentDb.OpenRecordset(sSql)

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

Set oDocName = oWordapp.Documents
If Not oDocName Is Nothing Then
oDocName.PrintOut
oDocName.Saved = True
oDocName.Close
End If
oRst.MoveNext
Loop
End If
oWordapp.Quit

oDocName seems to be causing all the trouble, but I don't know how to
fix it.

 

[Malcolm Smith]

Set oDocName = oWordapp.Documents

You are setting a pointer to the collection of all of the open documents
within the open Word application.

As you may guess, there won't be any.


You are doing this all wrong. Surely you need to go through the
recordset items to get the file name. I would assume that you would need
a variable called 'sFilename' or something.

You aren't even looking in the recordset to get the name of the file out.

What you code is doing is nonsensical; it is doing the following:

- Open an instance of Word.
- Open a recordset showing ALL documents (you are not even doing a WHERE
clause in the SQL either!)
- Go through the recordset one record at a time and not even look at what
is in the record at all! In the meantime check to see if there is a
collection of documents in Word (there will be but it will hold 0
documents) then printout the whole collection of documents.


This is nothing like what you want.

I wrote a message earlier which says the following. May I suggest that
you read it again, work out what I am doing and then compare it with your
message.

===========================

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.

 

[Joanne]

Malcolm
This is what I have after making the changes. I was following your
previous instruction, but it didn't work since I couldn't figure out
how to get to the records in the recordset, so I was investigating
help and the internet and got everything messed up.
Anyway, this code is giving me an error on 'OpenRecordset' which says
'sub or function not defined' - compile error.

Public Sub PrintAll()
' Dim oWordapp As Object
Set oWordapp = CreateObject("Word.Application")
Dim oDocName As Object
Dim oRst As DAO.Recordset
Dim sSql As String
Dim sFilename As String
Dim sGroupName As String

sSql = "SELECT tbldocumentlist.DocName_Path from tbldocumentlist" & _
"WHERE (((tbldocumentlist.GroupName) = """ & sGroupName & """));"

Set oRst = OpenRecordset(sSql) ' recordset equals return from sql
If Not oRst Is Nothing Then ' if there are records in the oRst
Do While Not oRS.EOF ' do loop until no more records in oRst

sFilename = "" & oRst("Filename") 'not sure why this is here
If Len(Dir$(sFilename)) > 0 Then 'if still files in rst
Set oDocName = oWordapp.Documents.Open 'not sure what this does
If Not oDocName Is Nothing Then 'if there is an open file
oDocName.PrintOut ' do the rest of the loop
oDocName.Saved = True
oDocName.Close
End If
End If
oRst.MoveNext 'go to the next record in oRst
Loop
End If

oWordapp.Quit 'close msword
oRst = Nothing 'release memory used by oRst

End Sub

I put comments next to the code as to what I think is happening - I
really would like not only to make this work but to understand the why
and how of it working so I can do it on my own in the future. I feel
like I am grabbing more than my share of your time and effort.

My confustion lays in the use of sGroupName, sFilename and oDocName -
I'm just not at all clear about their purpose.

You'll notice that I commented out Dim oWordApp as Object because I
have it in the general declarations and I'm thinking that I am doing
something wrong when I dim it in the subroutine also. Whenever I try
to send the data to the bookmarks in the docs in MSWord, MSWord
reports that someone else is using the file and I can have a 'read
only' access to it, so I was thinking that double dimming may be my
problem there.

Thanks as always Malcolm.

How did your horses make out this week?

 

[Malcolm Smith]

What value is in the variable sGroupname ?

- Malc

 

[Joanne]

Here is where my confusion is. I 'want' the value to be 'Benefits'
meaning that all files from the 'Benefits' portion of the work package
are to be opened for view and/or print.

 

[Malcolm Smith]

Joanne,

Excellent. So the thing to do is to pass the name of the group as a
parameter to the routine:


Public Sub PrintAll(sGroupName as string)


Then it can be called by:
PrintAll "Accounts"
PrintAll "HR"
PrintAll "IT"

and so on. This will then generate the documents for each group.

Now the next thing is that you must go back to my message and find the
code which gets the name of the document from the recordset.

Plug that lot in after the command where you are opening the recordset and
now it ought to work.

You're almost there!

- Malc
www.dragondrop.com

 

[Joanne]

Malcolm
the vb editor is stopping on
'Set oRst = Currentdb.OpenRecordset sSql'
and telling me there is a problem with the syntax in my from
statement, which I see that the keyword 'from' is not in caps. I keep
looking it over and comparing it with your help message but I can't
see the problem with the FROM statement

sSql = "SELECT tblDocumentList.FileName from tblDocumentList" & _
"WHERE (((tblDocumentList.GroupName) = """ & _
sGroupName & """));"

Set oRst = CurrentDb.OpenRecordset(sSql)

Now the next thing is that you must go back to my message and find the
code which gets the name of the document from the recordset.

Plug that lot in after the command where you are opening the recordset and
now it ought to work.

I did change the SELECT statement to
tblDocumentList.FileName
and am hoping that this is where I get the list of filenames from the
recordset. Am I correct here.

Also
I understand now that passing the parameter to PrintAll(sGroupName) is
accomplished when the user clicks to print out "Benefits" docs on the
user form the onclick event code will be

Call PrintAll("Benefits")

And so now I understand that this is how to pass a variable to a sub
or function. Eureka! I finally got it! (this part anyway)

As always, I really appreciate your help on this and feel though I am
a bit slow, I am grasping some of the concepts along the way. Have
several books open in front of me and many access and msword bookmarks
to the internet in use. I intend to get this stuff

 

[Malcolm Smith]

sSql = "SELECT tblDocumentList.FileName from tblDocumentList" & _
"WHERE (((tblDocumentList.GroupName) = """ & _
sGroupName & """));"

This is a tricky little one. If you put a breakpoint on the line after
this statement and then opened the Immediate Window (Alt-F11) then typed:

? sSql

then you would see the error straight off.

The problem is that you are concatenating the word 'WHERE' straight after
'tblDocumentList' to make the string "tblDocumentListWHERE" whiich is
going to make anything choke.

Put a space before the WHERE so that it looks like:

sSql = "SELECT tblDocumentList.FileName from tblDocumentList" & _
" WHERE (((tblDocumentList.GroupName) = """ & _
sGroupName & """));"

That should solve this little problem.

- Malc
www.dragondrop.com

 

[Joanne]

that fixed that problem all right, but of course, I have more problems

set oRst.OpenRecordset (sSql
is returning this error

Runtime error 3601
Too few parameters. Expected 1

As I understand the line in question we are setting the variable oRst
to be equal to the recordset that was created by the sql statement and
is now open and ready for our use.
The recordset is the list of filenames that are equal to the value in
the variable sGroupName.

Then the line sFilename = ""& oRst(filename) takes the first filename
from the recordset, pushes it into the variable sFilename, and then we
proceed to do our work on the file that sFilename represents.

What I can't see in the routine is when and how are the docs being
opened up in msword so we can do our work on them, unless it is done
in the line

Set oDocName=oWordApp.Documents.Open

Does this line actually open the document that is represented in
sFilename, and if so, how is the connection between sFilename and
oDocName being accomplished?

Anyway, I think the recordset is empty still because I get the error
that it needs 'Expected 1' parameter, and I'm thinking that is the
index number of the first file in the recordset.

Am I way out in left field understanding nothing at all, or am I on
the way to understanding how this all fits together? At this point I
care much more to understand how this works than I do about this
little app.

Thanks as always Malcolm

 

[Malcolm Smith]

Joanne

"Too few parameters" suggests that one of the field names in the SQL
statement isn't in the table.

It seems that there are two field names being looked at here; 'FileName'
and 'GroupName'.

Do they both exist in the database table? When we discussed this a while
ago they were both supposed to be there.

- Malc

 

[Joanne]

That was the problem there - easy to fix once you know where to look.

Now the routine is running through without reporting an error, but
there is no action at the printer.

Looking at the Windows Task Manager I see that winword.exe is running
for each time I run the routine, which tells me that we are opening
msword okay and that I'm not getting to the end of the routine to run
oWordApp.quit.

I set a breakpoint on line
** If Len(Dir$(sFilename)) > 0 Then **
and I see the variable to hold the name of the active file,
'sFileName' is doing it's job as I can see in the immediate window
using ** ? sFileName ** the first filename I expect in the recordset
is in the variable.

But I still think the files are not opening up - we have them in the
rst but we don't have them open so the following block can run

sFilename = "" & oRst("Filename")
If Len(Dir$(sFilename)) > 0 Then
Set oDocName = oWordapp.Documents.Open
If Not oDocName Is Nothing Then
oDocName.PrintOut
oDocName.Saved = True
oDocName.Close
End If
End If

It seems we set oDocName to the 'Open' word documents, but I don't
think there are any open so the routine is dropping out of the loop
since there are no files open to work on.

I don't know what to do with the check for open files and setting the
list of open files to oDocName before printing, saving and closing. It
seems that they are being called from a Directory, but they are really
just listed in the recordset. Am I making any sense at all here??

The more I go thru this the more I understand. I have been using your
tip to actually read the code outloud and pay close attention to what
it is saying. It seems to help me make better sense of what is going
on. But it isn't helping me to understand these two lines:

If Len(Dir$(sFilename)) > 0 Then
Set oDocName = oWordapp.Documents.Open

Thanks again Malcolm

 

[Malcolm Smith]

The more I go thru this the more I understand. I have been using your
tip to actually read the code outloud and pay close attention to what
it is saying. It seems to help me make better sense of what is going
on. But it isn't helping me to understand these two lines:

If Len(Dir$(sFilename)) > 0 Then
Set oDocName = oWordapp.Documents.Open

Okay, the first line of these two is making sure that the file exists.

This is where we enter the realms of Real World programming. We know that
there SHOULD be file of that name but we can't be sure. All I can tell
you is that it is not impossible for someone to have come along and
removed it.

So, what we do is to make sure that that file exists. If it exists then
we work with it; if not we move on to the next file...

Right, the Dir$() function returns the file name if the file is present.
If there file cannot be found then the Dir$() command returns an empty
string, "".

So, the best test is to see if the length of the file is greater than 0
bytes in size, hence:

If Len(Dir$(sFilename)) > 0 Then

Right, that's dead easy. Now all we have to do is to open the file and
print it.

In your code you are missing out one teesy-weensy detail; you forgot to
mention which filename you want to open.

So, let's make it correct with

Set oDocName = oWordapp.Documents.Open sFileName

Right, what this does is to create a pointer called oDocName which points
to the document which you've just opened.

Now, if the document can't get opened for some reason then we have to
check for a valid pointer to the Document we've just opened:

This is where the test against Nothing comes in. If the pointer is not
pointing to Nothing then we've got a document open! Hoorah!

If Not oDocName Is Nothing Then
oDocName.PrintOut
oDocName.Saved = True
oDocName.Close
End If
End If


And that, Joanne, is (can I dare hope?) is that. You should now be
churning out documents like crazy!

Cheers
Malc
www.dragondrop.com

 

[Joanne]

Here is the troublesome block of code

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

sFilename = "" & oRst("Filename")
If Len(Dir$(sFilename)) > 0 Then
Set oDocName = oWordapp.Documents.Open.sFilename
If Not oDocName Is Nothing Then
oDocName.PrintOut
oDocName.Saved = True
oDocName.Close
End If
End If
oRst.MoveNext
Loop

I have added sFilename as the file I want to work with to the

Set oDocName = oWordapp.Documents.Open.sFilename

line. I get an argument not optional error. Thought I might have the
variable plugged in wrong. Tried no ".", tried "sfilename", tried
("sfilename"), but it rejects all of my efforts.


running code to the set odocname line equals
? sFilename is showing me the file I expect to see
? oDocName = Nothing

Like you, I hold my breath with each new thing I try, and my hopes are
high, but to no avail.

Sorry to be so difficult - you are being extremely patient with me,
more than I would expect, and I really do appreciate it

BTW, I notice in the lines with the sFilename variable when code has
stopped due to the 'argument not optional' error, on mouse over
sfilename it shows me the correct filename in both the first and the
second lines of the code within the do loop. Interesting.

 

[Malcolm Smith]

Set oDocName = oWordapp.Documents.Open.sFilename

Joanne

There is no period between Open and the argument sFileName, just a space.

..Open is a method of the .Documents collection which explains why there
are those periods there.

The .Open method (click on Help) requires a filename which is what is in
that argument. Only then when the document can be opened can the
pointer oDocName have a valid value.

So, your code now ought to look like this:

Set oDocName = oWordapp.Documents.Open sFilename


When the code works click on some of the names of methods and stuff and
press F1 to get an idea of what they do.

Cheers
Malc

 

[Joanne]

Malcolm
I know you are tired of hearing from me and perhaps I should just
abandon this project except I know I am really close - seriously, tell
me to bug off if you've had enough and I will disappear. ;-)

Problem is if I add the variable sfilename at the end of the Set
oDocName line with one space between them, I get a compile error /
syntax error and sgroupname and sfilename are empty.

If I breakpoint at the If Len(Dir$ etc line, and I must remove the
sfilename variable from the end of the Set oDocName line, then the
variables for sgroupname and sfilename are filled as I would expect
them to be. But if I run to this breakpoint with sfilename at the end
of the set oDocName line, then I get a syntax error on the If Len
(Dir$ etc. line.

I can make nothing of this and begin to suspect there is something
wrong elsewhere in the project causing this to happen.

I tried to comment out the loop and just do
sfilename.open
sfilename.printout
but I got an 'invalid qualifier' error

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

sFilename = "" & oRst("Filename")
If Len(Dir$(sFilename)) > 0 Then
Set oDocName = oWordapp.Documents.Open
If Not oDocName Is Nothing Then
oDocName.PrintOut
oDocName.Saved = True
oDocName.Close
End If
End If
oRst.MoveNext
Loop
End If

So I am at a loss, and perhaps you would advise me to just forget it
and move on. If you've had enough, feel free to say so, no hard
feelings - indeed, I thank you very much for putting up with this and
working so hard and long on this project with me.
Joanne