Bookmarks

[Joanne]

I am running word from access - using msoffice2000, winxp

I have 22 docs with bookmarks in them. I need to fill in the bookmarks
from my access app.

Everything works as expected on 18 of the 22 files.

The other 4 files cause all kinds of problems. I have set the
recordset to just access these 4 files, wondering if I was over-taxing
the whole system, but even if they are the only 4 files, they cause
problems.

Here are the problems.
If I try to do all 22 files, I get the 18 files correct, then the app
stops, everything is in 'not responding' and I have to shut it all
down.

If I try to do just the 4 rogue files, the routine that sets the
bookmarks gets the 4 files in a recordset, opens them, skips over the
portion of the routine that sets the bookmarks.text and closes them
back up, this time without any 'not responding' system problems. I
have checked the docs, and all bookmarks are actually present.

Using debug.print, I see the routine set all 18 files but then it
fails on the first of these 4 files. The debug.print suggests that the
app is working on the files in an ascending alpha sort, and it does
the good 18, and then crashes on the first of the 4 bad files, which
is located in the middle of the alpha sort. I am dumbfounded at this
action, but am wondering if I perhaps have four corrupted files. I
thought I would beg some information before just jumping in and
recreating these files.

Is there something I can do about this short of recreating the files?

Thanks for your expertise. I appreciate it a great deal.

 

[Doug Robbins]

From your description of what happens when you run the routine over just
those four files, there has to be something about them that is causing the
problem. Can't tell from the information that you have provided just what
it is, but that is where you need to look.

--
Please respond to the Newsgroup for the benefit of others who may be
interested. Questions sent directly to me will only be answered on a paid
consulting basis.

Hope this helps,
Doug Robbins - Word MVP

 

[Malcolm Smith]

Can you show us a sample of your code?

 

[Joanne]

Hey Malcom, I'm Baaaack!
You helped me write this code in the first place when I couldn't get
the recordset and didn't know how to access the records after I got
the recordset.
Well, all of that works beautifully, and I have implemented that type
of routine in other aspects of this app.(thank you, thank you, thank
you!)
Here is some of the code in the routine (I'm not going to burden you
with the setting of all the bookmarks - they work fine on the 18 docs
anyway)

This word automation from access office 2000 winxp
Function SetBookmarks()
Dim oWordapp As Object
Dim oDocName As Object
Dim oRst As DAO.Recordset
Dim BsSql As String 'sql stmt to get docs with bookmarks
Dim sFilename As String
'Open instance of MSWord
Set oWordapp = CreateObject("Word.Application")
oWordapp.Visible = True
'Create recordset using a sql query
'sql statment is saying get the file list from table named
'tblDocumentList'
'using the field 'DocNamePath' and choosing only files containing
bookmarks
BsSql = "SELECT tblDocumentList.DocNamePath FROM tblDocumentList" &
_
" WHERE (((tblDocumentList.Bookmarks) = True))"

'Open the recordset that is based on the return of the sql query
Set oRst = CurrentDb.OpenRecordset(BsSql)

'********************************************************
'Code to send recordset to immediate window
'Do Until oRst.EOF
'Debug.Print oRst.Fields("DocNamePath")
'oRst.MoveNext
'Loop 'the docs are all there as expected
'***********************************************************

'Check for records in recordset. If there are records, do until end
of file
If Not oRst Is Nothing Then
Do While Not oRst.EOF
sFilename = "" & oRst("DocNamePath")


'If the file is actually there (checking in case someone deleted it)
If Len(Dir$(sFilename)) > 0 Then

'open doc and if it opened successfully
Set oDocName = oWordapp.Documents.Open(sFilename)
If Not oDocName Is Nothing Then

'Set the bookmarks in the doc
With oWordapp.ActiveDocument.Bookmarks
If oWordapp.ActiveDocument.Bookmarks.Exists("FName") = True Then
.Item("Fname").Range.Text = Nz(Me!FName, "")
End If
If oWordapp.ActiveDocument.Bookmarks.Exists("Midinit") = True Then
.Item("MidInit").Range.Text = Nz(Me!Midinit, "")
End If
If oWordapp.ActiveDocument.Bookmarks.Exists("FNameMidInit") = True
Then
.Item("FNameMidInit").Range.Text = Nz(Me!FNameMidInit, "")
End If
oDocName.Save
DoEvents
oDocName.Close
Set oDocName = Nothing
'Get the next file in recordset and run thru the above loop again
oRst.MoveNext
End If
End If
Loop
End If
oWordapp.Quit
oRst.Close
Set oWordapp = Nothing
Set oDocName = Nothing
Set oRst = Nothing
End Function

Like I said before, in trying to troubleshoot this myself I did try
the routine on only the 4 docs in question, and it simply skips the
entire set of code that sets the bookmarks in the other 18 docs. I
have checked the 4 docs and the bookmarks are firmly in place there.

What do you think Malcoml?
Thanks for your time and expertise
Joanne

 

[Malcolm Smith]

Oh, God! It's you!!

 

[Malcolm Smith]

I think that I see what could be a problem.

In your code you use oDocName as a pointer to the document just opened.

Then you use this:
oWordapp.ActiveDocument.

Why not use the pointer:
oDocName.

as this points to the same document and also using ActiveDocument is not
really recommended for an external application.


And what's this Nz() thing?

- Malc
www.dragondrop.com

 

[Malcolm Smith]

Joanne

What's the status now?

- Malc

 

[Joanne]

Ya, it's me again. How ya doing?

So I should say
oWordapp.oDocName.Bookmarks

Is that the correct syntax for the statement?

Why would the original owrodapp.activedocument work on 18 of the 22
files and then fail on these four files. I will surely change the code
because now that you point it out to me, I have read many times while
browsing the newsgroups that activedocument is not the best way for
automation, that it works better from within word and is not meant to
be used from an outside application.

The Nz() is used because individual docs do not have all of the
bookmarks in the list of
.Item("Fname").Range.Text = Nz(Me!FName, "") statements
on them, and when the routine goes thru the list of bookmarks to set
and the doc doesn't need that particular bookmark, well, the app got
unhappy about that. So using the Nz() tells the app that if there is
no bookmark on this doc by that name it is okay, just skip it and go
to the next bookmark in the routine. Another thing I learned by
begging for help on these newsgroups. I swear trolling thru these
newsgroups is better than any class I have ever taken.
Joanne

 

[Joanne]

Malc
I substituted oWordApp.oDocName.Bookmarks in the 'with/end with'
statements to set the bookmarks and I got this error

'object doesn't support this property or method'

I ran the test of it on the docs that are working well, so I know the
error is not tied into the documents that will not set the bookmark
values.
Joanne

 

[Malcolm Smith]

So I should say
oWordapp.oDocName.Bookmarks

Is that the correct syntax for the statement?

No, oDocName is pointing to the Document you've opened. So you should be
using:

oDocName.Bookmarks

Let me try to explain a little here. If I confuse you then I apologise
in advance.

A lot of these objects which you see and create, especially with the Set,
statement are just pointers to things. For example, you are driving down
a country lane and you want to get to a small town, say, Dogspittle or
somthing, and you see a road sign at a junction with the aid of this sigh
you are able to find you way to the town object called Dogspittle. The
sign is basically a pointer.

So, the same in VB/VBA. You create an object somewhere. It could be a
document object when you create or open a document. It could also be a
pointer to the Word application. Or it could be a pointer to a certain
table, bookmark, range or whathavewe.

You then have all these pointers created. One will point to the Word
application instance, in this case oWordapp and one will point directly to
the new document, oDocName.

Now, just because you have two pointers in your hand, say, it doesn't mean
that the oWordApp instance knows what the hell is oDocName as it has no
knowledge of it or the relationship between the two items. However,
oWordApp does have its own Documents collection and it understands what
those are and, as it happens, your oDocName pointer points to one of the
documents in that collection.

So, if you are the controlling application, in this case MS Access and you
have now two pointers; oWordApp and oDocName. To get to the collection
of documents in that application you can look at the oWordApp.Documents
and if you want to look at some of the Word Application's option settings
then oWordApp.Options will do that for you.

But, we're not too interested in those, are we? We're really interested
in the document itself. Sure, we have to open the document first within
the oWordApp application so this means that the statement:

set oDocName = oWordApp.Document.Open(...)

is correct. It opens an existing document in that oWordApp instance and
puts that oDocName pointer to it.

From that point on when we want to refer to that document we just have to
use the oDocName reference, for example: oDocName.Sections.Count tells us
how many sections there are in the document, oDocName.PrintOut prints the
thing out (remember we did that before) and oDocName.Close closes the
document.

Does this help of does this make it even more complicated then before?
Perhaps I ought to put something onto my site to explain all this. One
day.

- Malc
www.dragondrop.com

 

[Malcolm Smith]

Joanne

Let's fix one thing at a time and then see what happens next...

- Malc

 

[Joanne]

Malc
I understand that oDocName is an object and points to the now
'activedocument' in the recordset which is the one that is now open in
the object oWordApp.
I changed my with / end with statements to read like this
If oWordapp.oDocName.Bookmarks.Exists("FName") = True Then
.Item("Fname").Range.Text = Nz(Me!FName, "")
End If
and got and error telling me
'object doesn't support this property or method'

Apparently I've taken a wrong turn, again ;(
Joanne

 

[Malcolm Smith]

Malc
I understand that oDocName is an object and points to the now
'activedocument' in the recordset which is the one that is now open in
the object oWordApp.
I changed my with / end with statements to read like this
If oWordapp.oDocName.Bookmarks.Exists("FName") = True Then
.Item("Fname").Range.Text = Nz(Me!FName, "")
End If
and got and error telling me
'object doesn't support this property or method'

Apparently I've taken a wrong turn, again ;(
Joanne

NO! I said that you use oDocName and not oWordapp.oDocName

And don't bother with the With statements; they are only confusing you.

Your code ought to read

If oDocName.Bookmarks.Exists("FName") = True Then
oDocName.Bookmarks("Fname").Range.Text = Nz(Me!FName, "")
End If

- Malc

 

[Malcolm Smith]

Malc
I understand that oDocName is an object and points to the now
'activedocument' in the recordset which is the one that is now open in
the object oWordApp.
I changed my with / end with statements to read like this
If oWordapp.oDocName.Bookmarks.Exists("FName") = True Then
.Item("Fname").Range.Text = Nz(Me!FName, "")
End If
and got and error telling me
'object doesn't support this property or method'

Apparently I've taken a wrong turn, again ;(
Joanne

oDocName does NOT point to anything in the Recordset. It points to the
document which you have opened.

The name of the document pointed to by oDocName is the same as the name in
the Recordset. And that is it. There are NO active documents within
the RecordSet.

One can't have an ActiveDocument in an recordset because a recordset
doesn't support ActiveDocuments. Look through your code again and look
at what each line does.

Somewhere you will see that a string variable gets filled with the
document name which is taken from the Recordset. Then a document is
opened with the name in that variable. That document is the active
document (if we must have an active document context) and that document is
pointed to by the oDocName pointer.

You have to get very clear what a pointer is and what it points to.
oWordApp points to a Word Application object. oDocName points to a Word
Document opened in the oWordApp. oRS points to the recordset in the
Access database.


Right, what to do. Get rid of those With statements, they are confusing
you. Re-read my previous post and get rid of those oWordApp.oDocName
references as they are meaningless and wrong and replace them with just
oDocName references.

Then see how it goes.

- Malc

 

[Joanne]

The statement below gives me this error
Compile error:
Invalid or unqualified reference

This is after I commented out the with / end with statements as your
instructions below says

 

[Malcolm Smith]

The statement below gives me this error
Compile error:
Invalid or unqualified reference

This is after I commented out the with / end with statements as your
instructions below says

WHICH statement?

 

[Joanne]

If oDocName.Bookmarks.Exists("FName") = True Then
oDocName.Bookmarks("Fname").Range.Text = Nz(Me!FName, "")
End If
Causes the error

 

[Malcolm Smith]

If oDocName.Bookmarks.Exists("FName") = True Then
oDocName.Bookmarks("Fname").Range.Text = Nz(Me!FName, "")
End If
Causes the error

Again, which statement causes the error? I can see two here.

 

[Joanne]

My error, Malc
I failed to replace .item with oDocName.Bookmarks in the second
statement.

All is corrected and running well now and I'm sure you've helped me
make the app more reliable.

But, this did not do anything for the 4 files that will not work
right, which is how we started this conversation again in the first
place.

I create a recrodset with only these 4 files but when the routine gets
to the 'do while not oRst.EOF' it must be thinking it is at the eof
because it drops out of the loop here and skips the entire set of
statements to set the bookmarks. And of course sFilename = ""
because the routine drops out of the loop before the variable receives
a value.

If Not oRst Is Nothing Then
Do While Not oRst.EOF
sFilename = "" & oRst("DocNamePath")

Strange since 18 of the 22 docs do as expected. That is what makes me
suspicious that these 4 docs are corrupt in some way.

As always, thanks a great deal for your time and expertise
Joanne

 

[Malcolm Smith]

My error, Malc
I failed to replace .item with oDocName.Bookmarks in the second
statement.

All is corrected and running well now and I'm sure you've helped me
make the app more reliable.

But, this did not do anything for the 4 files that will not work
right, which is how we started this conversation again in the first
place.

I create a recrodset with only these 4 files but when the routine gets
to the 'do while not oRst.EOF' it must be thinking it is at the eof
because it drops out of the loop here and skips the entire set of
statements to set the bookmarks. And of course sFilename = ""
because the routine drops out of the loop before the variable receives
a value.

If Not oRst Is Nothing Then
Do While Not oRst.EOF
sFilename = "" & oRst("DocNamePath")

Strange since 18 of the 22 docs do as expected. That is what makes me
suspicious that these 4 docs are corrupt in some way.

As always, thanks a great deal for your time and expertise
Joanne

Can you put a breakpoint at this point and then see what values are placed
in the variables as you single-step (F8) through the code?

- Malc