How do I refer to Word's bookmarks for multiple pages? (1 Viewer)

[Banana]

I want to feed data from Access to Word. I have set the bookmarks for one report so I can ensure the right data goes to right form fields.

But the thing is, this report I'm working will grow; it has three section which is just repeats, and if we need to report more than three, we add a new page which is identical in formatting to the first page; just continuation of details for the report.

How, then, do I refer to those form fields' bookmarks as the report grows?

I thought of dynamically creating bookmarks as a new page is added to add a numeric index to end of the bookmark's name based on how many we have so far but not sure if this is best solution....

TIA.

 

[RoyVidar]

Based on what you say, I'd say it looks like mail merge could be a reasonable suggestion. If you're not familiar with it, check out Albert Kallals "Super Easy Word Merge." from http://www.members.shaw.ca/AlbertKallal/msaccess/msaccess.html

I think perhaps a thought could be to use some form fields for general stuff, then have a catalog setup for repeating info, dunno, what do you think?

 

[Banana]

Thanks for the suggestion; I'll look at this, but this isn't a mail merge. This is simply a report that will grow as data accumulate. I can't modify the formatting as this is a government publication so I have to do with what I have.

Edit: I had missed the instructions when I first looked at it. Now having read it, this looks nice but there's problem: Since I'm going to add new page which is identical in formatting, I need to be able to differentiate the bookmarks on the new pages from previous page, so this isn't as simple as listing the form fields' bookmark and linking the data to right bookmark.

Does that clarify the problem here?

 

[Banana]

Did it the gritty-nitty way. What I did was give each form fields in each section bookmark value of "NameX", then for next section, used "NameY" (e.g. same name but used letter Y), and for third & last section, "NameZ".

When the new page is added programmically (by opening a template, selecting all, closing the template, and pasting it to the end of document), I then re-name the bookmarks so

NameX = Name + Format$(intX)
... (repeat a lot of times for each form field in section)

intX = intX + 1

NameY = Name + Format$(intX)
... (repeat a lot of times for each form field in section)

intX = intX + 1

NameZ = Name + Format$(intX)
... (repeat the friggin' drill)

And it works. Well, sortas.

See, when it runs, the bookmarks are now numbered based on how many sections (3 section per pages), as I want it. I can save it, but when I close it and re-open, all of monkeying around is gone and it has returned to NameX, NameY and NameZ...

I suppose if I were to re-create the document from first page, it woudln't be a big deal, but if the document extend past few words, it'd be ridiculous amount of work when I could just execute the procedure to update only the last page.

Any suggestions?

Also, I couldn't get my cursor to move to the end of document... Macro recording is limited in that reason, and I'm not sure if there's even a method I can issue so I don't have to worry about inserting in a new page in middle of a old page or something like that.

TIA.

 

[RoyVidar]

MailMerge - for new pages with identical formatting, one idea could be to play with Headers and/or footers for the information on each page, then use catalog setup for the repeating information. A catalog setups doesn't necessarily mean there's one line/paragraph per "record", you can have multiple lines/paragraphs. There's also the paragraph properties on the "Line and Page Breaks" tab, perhaps in particular the "Keep with next" property (forces the paragraph to be on the same page as the next paragraph).

Moving the cursor to the end of the document, should normally be something like

TheWordOjbect.Selection.EndKey Unit:=wdStory

Record it with the standard shortcut Ctrl+End

but knowing what you've tried, what happened, what didn't etc, makes it easier to troubleshoot.

When I've worked with bookmarks, I've only filled preexisting bookmarks, so I've never experienced what you're describing. One question, though, when you've fetched this section from somewhere, is the info placed at the correct place? If so, to be blunt, does it matter where the bookmarks are?

One thing to look out for, is what happens if you create a new bookmark with a name already existing (deletes the old one), or copy/paste text containing bookmarks with names already existing (I think the existing bookmarks are kept, and only non existing bookmarks occur in the pasted area). Could that be the reason it behaves strange? You may wish to ensure the bookmark names are unique through the whole process. Also, be sure you're saving the correct document. Depending on which method you use of referencing different docs, there can easily be confusement about what you're saving

To the repeating stuff, you could probably create a subroutine, see for instance http://word.mvps.org/FAQs/MacrosVBA/InsertingTextAtBookmark.htm (though the link relates to doing something else to the bookmark, it illustrates the point), and you can probably build it even more dynamic.

You seem to be talking about both form fields and bookmarks. what is it that you're (re)naming, the bookmarks or the form fields? Where/how to you put the info?

.Bookmarks("MyBookMark").Range.Text = "SomeText"
.FormFields("MyFormField").Result = "SomeText"

I'll usually only use FormFields if the user is going to enter information manually (then toggle protection and all that stuff), but only bookmarks (or Mail Merge) if I'm to create reports.

Another thing I thought about, was using tables. With tables, you can loop the individual cells, and fill information as you wish. Here's one sample found through a quick search

http://groups.google.com/group/comp.databases.ms-access/browse_frm/thread/9f5ff3a933fc353e/

A "kludge", would be to create a template or document with sufficient space/bookmarks, then just delete the unused stuff afterwards.

 

[Banana]

Roy, that was quite a wealth of information. I truly appreciate it.

MailMerge - for new pages with identical formatting, one idea could be to play with Headers and/or footers for the information on each page, then use catalog setup for the repeating information. A catalog setups doesn't necessarily mean there's one line/paragraph per "record", you can have multiple lines/paragraphs. There's also the paragraph properties on the "Line and Page Breaks" tab, perhaps in particular the "Keep with next" property (forces the paragraph to be on the same page as the next paragraph).

Will need to look into this...

Moving the cursor to the end of the document, should normally be something like

TheWordOjbect.Selection.EndKey Unit:=wdStory

I had expected it to be something like InsertAfter.End or something because I'd have thought Selection.EndKey would *select* the body within. Go figure.

Record it with the standard shortcut Ctrl+End

but knowing what you've tried, what happened, what didn't etc, makes it easier to troubleshoot.

Yeah, you know that little icon thingy at upper left corner of a table where you click on to select the table? The thing was that this is what I want to select, but if I turn on recording, that icon won't come up at all. I could use menu Table -> Select -> Table, but only if I have a cursor in the table. I'm trying to avoid using cursor mainly because this will be done automated by Access and I don't want dynamic pointer to flake out the results, especially if there's multiple instances of same bookmarks for each page... Maybe that is an unjustified concern, I don't know.

When I've worked with bookmarks, I've only filled preexisting bookmarks, so I've never experienced what you're describing. One question, though, when you've fetched this section from somewhere, is the info placed at the correct place? If so, to be blunt, does it matter where the bookmarks are?

Maybe because I'm not 100% sure how I would refer to bookmarks that may recur on every page? As mentioned earlier, the report can grow past one page but have identical formatting. Each page has 3 sections, and each section has ungodly amounts of checkboxes (UGH!) *and* dropdown list. Those form fields' bookmarks need to be somehow differeniate from same form fields on previouis page, which is why I thought of renaming them with a incrementer suffix every time a new page was added. Maybe this is actually a waste time as there's a way to tell which bookmark I want for which page.

One thing to look out for, is what happens if you create a new bookmark with a name already existing (deletes the old one), or copy/paste text containing bookmarks with names already existing (I think the existing bookmarks are kept, and only non existing bookmarks occur in the pasted area). Could that be the reason it behaves strange? You may wish to ensure the bookmark names are unique through the whole process. Also, be sure you're saving the correct document. Depending on which method you use of referencing different docs, there can easily be confusement about what you're saving

The idea is that I'd copy the new page from a template, add it to an existing document, rename bookmarks within that page based on how many pages we have so far so it's now ready for filling in by Access. Also, if the bookmarks are given a numbers, it makes it easier for me to tell Access to where to put information about record, based on its position of the query (which is sorted by date) and use it 1=1 to the section's form fields' bookmarks integer suffix.

To the repeating stuff, you could probably create a subroutine, see for instance http://word.mvps.org/FAQs/MacrosVBA/InsertingTextAtBookmark.htm (though the link relates to doing something else to the bookmark, it illustrates the point), and you can probably build it even more dynamic.

Hmm, looks like the bookmarks for mail merge is somehow different from form fields' bookmarks. See, I only use bookmarks as a pointer to the formfield and fill in the formfield using what you posted a bit below.

I can then move through the long list of formfields using bookmarks like thus:

.Formsfields("BookmarkName1").Result = "SomeText1"
.Formsfields("BookmarkName2").Result = "SomeText2"
...

You seem to be talking about both form fields and bookmarks. what is it that you're (re)naming, the bookmarks or the form fields? Where/how to you put the info?

.Bookmarks("MyBookMark").Range.Text = "SomeText"
.FormFields("MyFormField").Result = "SomeText"

I'll usually only use FormFields if the user is going to enter information manually (then toggle protection and all that stuff), but only bookmarks (or Mail Merge) if I'm to create reports.

Formfields does have bookmark. If you right click on any form fields, and click "Properties", you can see an entry for bookmarks. This is what I am referring to.

I suppose there could be a easier way to do it, but this is a government form which I don't have much leeway. As long I'm doing it behind the scene, no problem, but on paper/computer monitor it must be 100% identical.

Another thing I thought about, was using tables. With tables, you can loop the individual cells, and fill information as you wish. Here's one sample found through a quick search


http://groups.google.com/group/comp.databases.ms-access/browse_frm/thread/9f5ff3a933fc353e/

A "kludge", would be to create a template or document with sufficient space/bookmarks, then just delete the unused stuff afterwards.

Unfortunately, whoever designed this table inserted more than one checkbox and dropdown list per cell, maybe 10 per cell, actually. Really wish they'd do a better job of it, but they never knew about bookmarking or that it can be automated. The form was intended to be filled manually on a computer, but why do it manually when Access already stores the needed information and can therefore feed into the report?

I hope that helps clarify what I'm trying to do.

 

[Banana]

An addenum:

I want to make clear that if I were to do:

Formfields("MyFormFieldBookMark").Result = "SomeText"

The value entered is persistent; that is, if I save the document, close it and re-open it, it's still there.

But if I were to simply re-name the bookmark's name as thus:

Formfields("MyFormFieldBookMarkX").Name = "MyFormFieldBookMark1"

It does run, and does indeed changes the value. But it is not saved (whether I programmically save it or manually save it), and reverts to its original name when I close and re-open the document.

I'm simply not sure how I am to ensure that all bookmarks I have for a given document, even as I add new pages, will be unique which I need it to be so I can point Access toward right one.

 

[Banana]

The plot thickens...

Apparently, Word is being inconsistent with how it is saved.

For sample, here's my code:

Private Sub NewPage2()

Dim objDoc As Word.Document
Dim strName As String
Dim strSuffix As String
Dim intName As Integer
Dim intX As Integer
Dim objFld As Word.FormField

Set objDoc = ActiveWindow.Document

'objDoc.SaveAs "CaptKirkNotes"

For Each objFld In objDoc.FormFields
    
    strSuffix = Right(objFld.Name, 1)
    strName = objFld.Name
    intName = Len(strName) - 1
    intX = 1
    
    Select Case strSuffix
        Case "X", "A"
            objFld.Name = Left(strName, intName) + Format$(intX)
        Case "Y"
            objFld.Name = Left(strName, intName) + Format$(intX + 1)
        Case "Z"
            objFld.Name = Left(strName, intName) + Format$(intX + 2)
        Case Else
            MsgBox "An error occurred"
    End Select
Next objFld

objDoc.SaveAs "CaptKirkNotes"
objDoc.Close

Set objFld = Nothing
Set objDoc = Nothing

End Sub

Note that I've commented out the first SaveAs.... Apparently, if this line is executed before the bookmarks are re-named, nothing is saved, even if I issue a Save command after the loop.

However, if I SaveAs after the loop, the new bookmarks are preserved even when I re-open the document.

I initially had SaveAs before loop because I did not want to modify the template, especially if something went wrong and could not complete the routine, leaving me with a half-assed template. But it seems that Word doesn't somehow apply the new bookmark names in the "new" document even if it's saved before and after the loop...

Any suggestions for creating a fail-safe method of modifying the template? I suppose I can have a backup copy of the template to replace in such contingency, but that would be kind of tedious, especially if my users are remote from where I am and shouldn't have to know anything short of simply retrying...

 

[RoyVidar]

I have an app/process where I use a Word template that under some circumstances might change. My approach is to make a (local) copy of the template, which I use (and kill afterwards). Then there's no real damage to the "real template". But usually, you should not intentionally change anything in a template from a document, you should only change in the document(s).

Selecting a table, you could loop the tables collection, or plain

TheDoc.Tables(1).Select
' or
TheApp.Selection.Tables(1).Select
' or
TheDoc.Tables(TheDoc.Tables.Count).Select

should select the table in question.

 

[Banana]

Thanks for the information.

I'll see if creating a temporary copy of the template will bypass the problem of being unable to save the changes to bookmarks' name. Still, this sucks that I have to do an extra hoop just because I can't execute SaveAs method before the loop for editing bookmarks method and successfully save those changes.

Thanks also for the suggestion on how to select the table.

 

[Banana]

Maybe this is a silly question, but is there a way to programmically select a listentry from a dropdown?

I could simply insert the value of listentry as a "Result" of the form field, but that would mean I need to hard-code every list entry for each dropdown form fields.

Surely there is a way to select from the list (ideally, based on the name, but index is OK)?

 

[RoyVidar]

For instance to set the second list entry

TheDoc.FormFields("MyFormField").DropDown.Value = 2

 

[Banana]

So it's a numeric-only deal? No way to compare the string?

Still, that's helpful. Thanks so much, Roy!

 

[RoyVidar]

Sorry, I thought you were after index.

TheDoc.FormFields("MyFormField").Result = "MyText"

should work, shouldn't it? Though there's no validation. Below is a small snippet ensuring the actual list entry is there, prior to selecting.

dim dd as dropdown
dim l as long

set dd = TheDoc.FormFields("MyFormField").dropdown
for l = 1 to dd.listentries.count
    if dd.listentries(l).name = "TheName" then
        dd.value = l
    end if
next l

 

[Banana]

Just as I suspected. Too messy for my liking; I'll stick to the index.

Once again, you've been a great help!