PDA

View Full Version : Duplicate bookmark requirement



UHsoccer
03-21-2005, 06:28 AM
I need to be able to make substitution in a sentence depending whether the reference person is male or female. A Visual Basic program is initiated from an Excel spreadsheet, it opens a "starter" document, substitutes bookmark settings and saves the document. An example might be:

"He is in the office but he cannot come to the meeting" versus

"She is in the office but she cannot come to the meeting"

This type of substitution might show up many times. As is, I need a unique bookmark for each one. There has to be a better way.

Anyone?

sandam
03-21-2005, 07:02 AM
Have you considered using a Field instead of a bookmark. That way you place the same kind of field in all the substituion places and just set the Field value in the VBA program. I myself am still trying to understand bookmarks. tsk,tsk so much to learn, so little time.

UHsoccer
03-21-2005, 08:20 AM
To be honest, I first tried the "Mail-Merge" concept but could never get the Vb to work.

Then found Bookmark code (on another forum) and was succesful with that method (except for the "diplicate" bookmark issue).

I would be more then willing to consider Fields, but then I need to know the VB properties in order to get it to work. Such as how to add my own, etc, etc

You are right though in saying: So much to learn, so little time....

sandam
03-21-2005, 09:01 AM
Set each of the positions that you want to change to a specific piece of text then maybe try this??

HTH
Andrew


Sub ChangeGender
Dim GenderRole as String
GenderRole = "He"
With ActiveDocument.Content
.Find.Execute FindText:="GENDER", MatchCase:=True, _
ReplaceWith:= .Fields.Add( ActiveDocument.Content, _
wdFieldEmpty, GenderRole),Replace:=wdReplaceAll
End With
End Sub

UHsoccer
03-21-2005, 09:38 AM
I modified my "starter" document and then in my VB placed the code right after

Set wdDoc = wdApp.Documents.Open(docNameStart) (which is what I use to open my document)

your code creates the error "The command is not available becuase no document is open" yet I am looking at the word document.
Must be a VB trick that I am missing.

Henry

sandam
03-21-2005, 09:47 AM
try changing activedocument.content to wdDoc.Content .Also, have you placed the "Microsoft Word x.0" library into your excel project refereces? That and the "Microsoft Object" reference? these both usually sort out niggly little errors.

UHsoccer
03-21-2005, 10:36 AM
try changing activedocument.content to wdDoc.Content .Also, have you placed the "Microsoft Word x.0" library into your excel project refereces? That and the "Microsoft Object" reference? these both usually sort out niggly little errors.

Should really have seen that myself, the wdDoc myself.
I have Microsoft Office, Word and Excel 11.0 Object Library and Forms 2.0 Object in my reference list

So once I changed the wdDoc, it now detects a "type mismatch" in the underlined segment. Experimented with dim Genderrole as Fields, but that created other errors

Dim GenderRole As String
GenderRole = "He"
With wdDoc.Content
.Find.Execute FindText:="GENDER", MatchCase:=True, _
ReplaceWith:=.Fields.Add(wdDoc.Content, _
wdFieldEmpty, GenderRole), Replace:=wdReplaceAll
End With

Thanks in advance

sandam
03-21-2005, 11:24 AM
try this instead. It maybe that find and replace isn't compatible with fields. ??

HTH
Andrew;?


Dim GenderRole As String
Dim aFlag as boolean
GenderRole = "He"
aFlag = True
While aFlag
With wdDoc.Content
aFlag = False
.Find.Execute FindText:="GENDER", MatchCase:=True
If .Find.Found Then
.Select
Selection.Fields.Add Selection.Range, wdFieldEmpty, GenderRole, True
aFlag = True
End If
End With
Wend

UHsoccer
03-21-2005, 11:53 AM
try this instead. It maybe that find and replace isn't compatible with fields. ??

HTH
Andrew;?


Dim GenderRole As String
Dim aFlag as boolean
GenderRole = "He"
aFlag = True
While aFlag
With wdDoc.Content
aFlag = False
.Find.Execute FindText:="GENDER", MatchCase:=True
If .Find.Found Then
.Select
Selection.Fields.Add Selection.Range, wdFieldEmpty, GenderRole, True
aFlag = True
End If
End With
Wend


Now the error at Selection.Fields is "Wrong number of arguments or invalid property assignment"

Hope I do not outlive my welcome.....

hairywhiterabbit
03-21-2005, 03:48 PM
Hi Guys,

You definitely want to use the custom properties of the word document here. You can access them through a field in your document and then update the field through your code.

If you are running a pre-defined template, the easiest way to set it up is to go to File->Properties and select the custom tab. Add a meaningful name such as c_Gender (I always prefix mine to seperate them from the 'built in' values) and assign the type to 'text' (which is default) and enter a default value. I generally put the defaults in brackets such as [Gender] so that if you are working in the template it is easier to navigate. Also, I recommend turning on the field code shading so you know where in the document they are located.

Now you can insert a field reference to this custom doc property by selecting Insert->Field. On Word 2003 it is under the Document Information category and the field name 'Doc Property'. Select the custom type you just created and insert it into the document.

Once the document is set up all you need is the code to update the custom document property and to update the fields in the document. Assuming your word.application is "wdApp" and the word.document is "Starter", the following code should do the job

if Male then
wdApp.Documents(Starter).CustomDocumentProperties("c_Gender").Value = "Male"
else
wdApp.Documents(Starter).CustomDocumentProperties("c_Gender").Value = "Female"
end if
wdapp.documents(Starter).Fields.Update


Hope that helps,
Cheers,
Andrew

fumei
05-10-2005, 10:48 AM
Just wondering..... there does not appear to be any testing of Case in here. You want to substitute both "He" AND "he".

Next. Where is the original information come in? the reason I ask is that you could make the original location a formfield and all the other empty fields that reference back to it. If the original has Calculate on exit, all the others will update automatically.

MOS MASTER
05-10-2005, 10:58 AM
Next. Where is the original information come in? the reason I ask is that you could make the original location a formfield and all the other empty fields that reference back to it. If the original has Calculate on exit, all the others will update automatically.

Good Question wondering if this topic is still alive .... :rofl:

If data commes in writing to a range bookmark you can just add Ref-fields to all those bookmarks (you can preset those) and after the write do a update off all fields in the document...

This of course lookes a lot like your approach but you use formfields, Hence the protection must be on (But you have calculate benefit) and I use range bookmarks so the Ref-fields need an update command..

Seams to me both methods would probably answer this question...:whistle:

fumei
05-10-2005, 12:33 PM
Wrong wrong wrong. Formfields do NOT have to be protected in order to write data to them. This is a common misunderstanding. They must be protected for to allow USER input. They can be used freely programmatically without protection at all.

MOS MASTER
05-10-2005, 12:41 PM
Wrong wrong wrong. Formfields do NOT have to be protected in order to write data to them. This is a common misunderstanding. They must be protected for to allow USER input. They can be used freely programmatically without protection at all.
Right, right, right...your sollution speaks of the Calculate on exit checkbox...!

For that to work Protection has to be on!:tease:

hairywhiterabbit
05-10-2005, 12:46 PM
If you are using any type of field you can use the \* field format for case selection. If you know where the text is going you should know the case.
\* Caps - First letter of each word caps
\* FirstCap
\* Upper
\* Lower

MOS MASTER
05-10-2005, 12:49 PM
If you are using any type of field you can use the \* field format for case selection. If you know where the text is going you should know the case.
\* Caps - First letter of each word caps
\* FirstCap
\* Upper
\* Lower
Hi Andrew, :D

Which part of the question does this refer to? I don't see any question about Case Formatting...

What am I missing...:whistle:

hairywhiterabbit
05-10-2005, 12:54 PM
Hey MOS,

The first part of Fumei's response five post above yours. :thumb

Fumei just asked if he wants it done or not. :dunno Either way, thats it.

Cheers,
Andrew

MOS MASTER
05-10-2005, 12:57 PM
Hi Andrew,

hehehe now I see it! :banghead:

So sorry...Gerry and I have (or perhaps I'm only having this thing) this thing about bookmarks versus formfields so I've lost focus on this one...:cloud9:

fumei
05-10-2005, 12:58 PM
....oh my......I am SOOOOOOOOOO embarassed....wrong thread..........yes....BLUSH....Calculate on exit will need to protected......I will crawl off to my hole now and lick my wounds.

:banghead:

MOS MASTER
05-10-2005, 01:00 PM
....oh my......I am SOOOOOOOOOO embarassed....wrong thread..........yes....BLUSH....Calculate on exit will need to protected......I will crawl off to my hole now and lick my wounds.

:banghead:
No problems over here I'm beying stupid also today lets use each others heads to bang on one another....:banghead: :banghead: (the last one is reversed)

mdmackillop
05-10-2005, 01:22 PM
How about a Find and Replace?

Sub HeShe()
Dim HisText, HerText
HisText = Array("He", "he", "His", "his")
HerText = Array("She", "she", "Her", "her")

Selection.Find.ClearFormatting
Selection.Find.Replacement.ClearFormatting
For i = 0 To 3
With Selection.Find
.Text = HisText(i)
.Replacement.Text = HerText(i)
.Forward = True
.Wrap = wdFindContinue
.MatchCase = True
.MatchWholeWord = True
End With
Selection.Find.Execute Replace:=wdReplaceAll
Next
End Sub

fumei
05-10-2005, 01:36 PM
Finally!!! This was the solution I was waiting to be suggested. I did not want to be the one, as there was that reference to bookmarks in the original post...but really, is this not what is needed?

MOS MASTER
05-10-2005, 01:41 PM
Yepz nice post Malcom! :clap:

If it truly is the answer.....

For me the question was how to use one value multiple times in the document...That's what all our answers are about!

The sub of Malcom of course does something very different..might as well be the answer....

Can't wait for UHsoccer to return and tell us! :whip

hairywhiterabbit
05-10-2005, 01:44 PM
That would work only if there we no other references to He's or She's not related to the search and replace. You may have to introduce styles.... :devil:

Do guys you think a search and replace four times would be quicker than a fields update if it was done with fields? Obviously it depends on the complexity of the document, but still.

MOS MASTER
05-10-2005, 01:51 PM
Hi Andrew,

Field update is of course quicker and you have a point...

But I'm sure we can speculate about the real question all night...and I'll bet you in the morning we still don't have the answer! :rofl:

mdmackillop
05-10-2005, 02:37 PM
Thanks Gerry,
I think I'll change my signature to

"Never let the question influence the answer!"

fumei
05-11-2005, 06:46 AM
Hey, i like that!

Looks like MOS is correct...we still don't know. And correct again, fields would definitely be faster.

Styles? Well gosh...I just assumed! Yuck yuck yuck.

MOS MASTER
05-11-2005, 09:26 AM
Hi,

Yepz a good one.

I'll change mine to: "There's no such thing as a dumb question!" :whip

I do hope UHsoccer will stop by and tell us if his question ever got solved....