Value of a REF field via macro/vba inserted into save as filename

[frank dobbelaere]

Hi,

I'm new at this macro / VBA (first post here) and this has been a pretty
exciting journey so far.

Using Word 2003 I am trying to write a couple macros to automate a few
repetitive document functions. I looked at some books in the book store
(which didn't cover the subject), have printed several good tutorials
referenced frequently and prominent in google ranking (I'm sure you know the
ones I found), searched quite a bit through the help, msdn, mvp sites, ...
and have analyzed some related samples, but can't find this detail.

Goal: With this macro I would like to automate and thus standardize the
suggested file save as name. (showing the dialog to allow the user control
over the destination; so not automatically saving the file without dialog ...
at least not for the time being, a further step might be to suggest saving it
with a modified save as path that includes a subfolder based on the name, but
first things first)

Background:
I created a new template.
By the way the title of the template is set to Note, Letter, Fax - which
will be referenced as well. (this is just the first template of 3 - 4 I'm
making).
Via ASK fields it asks a few questions (Name, Number, ...) activated by an
AutoNew macro.
REF fields show the answers in the resulting document (document & header,
extra macro piece added to make sure it updates the field results in the
header).

After finishing the document I would like to use a customized Save As dialog.

The special save as ...

Created a macro which
- looks at the document properties title
- grabs the current date
and then succesfully displays a dialog as follows:

Dim dlg As Dialog
Set dlg = Dialogs(wdDialogFileSaveAs)
With dlg
..name = "_" & strDocumentType & strDate & ".doc"
..show
End With

Now, before the underscore I would like to access the value of Name and
Number; and thus suggest this name:

..name = strName & strNumber & "_" & strDocumentType & strDate & ".doc"

So, my questions is, how can I get the values of { REF Name } and { REF
Number } into strName and strNumber?


Note: With this file name idea I can ending up with very long file names, so
I am using a shorthand date and using an abbreviation for the document type;
done earlier in the macro.

An additional question comes in if it is possible to maniuplate the strName
and have the initials from {REF Name} rather than the full first name and
last name?
Or maybe it should be two ASK questions FirstName and LastName? And take the
initial from both?

Of course an additional ASK for initials would be possible; but it should be
possible to do it automatically, I think.

Thanks in advance for your help and advice.

Frank

 

[Jay Freedman]

Hi Frank,

Each ASK field creates a bookmark in the document and stores the user's answer
in the bookmark. All the REF field does is repeat that value elsewhere in the
document.

In VBA, you can get the value of the bookmark's contents and assign it to a
string, like this:

Dim strName As String, strNumber As Number
With ActiveDocument
strName = .Bookmarks("Name").Range.Text
strNumber = .Bookmarks("Number").Range.Text
End With

Whether you use separate ASK fields (and thus separate bookmarks) for the
FirstName and LastName or a single ASK field for the Name is a choice that's up
to you. My preference would be for a single field, because users dislike
multiple boxes popping up, each of which takes an extra click or keystroke to
press the OK button.

If you have a single field, you can separate it into chunks separated by spaces
and grab the initials like this:

Sub demo()
Dim strName As String, strInits As String
Dim arrayName As Variant, pieceName As Variant

' get the value of the bookmark (whole name)
strName = ActiveDocument.Bookmarks("Name").Range.Text

' words separated by spaces go into an array
arrayName = Split(strName, " ")

' get the first letter of each word
For Each pieceName In arrayName
strInits = strInits & Left(pieceName, 1)
Next

' show us what you got...
MsgBox strInits
End Sub

It doesn't have a lot of smarts, though. For example, if someone enters "Ludwig
von Beethoven" in the ASK field, the initials will come out as "LvB".

--
Regards,
Jay Freedman
Microsoft Word MVP
Email cannot be acknowledged; please post all follow-ups to the newsgroup so all
may benefit.

 

[Doug Robbins - Word MVP]

You should start off doing it right and use a userform, rather than Ask
fields.

See the article "How to create a Userform" at:

http://word.mvps.org/FAQs/Userforms/CreateAUserForm.htm

Have the save routine in the command button click event so that the user is
encouraged to save the document before starting further work on it and then
you can use the .Text property of the controls on the userform to supply the
information that you want to incorporate in the suggested filename.

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

 

[frank dobbelaere]

Jay,

You hit the nail on the head on both accounts! Wow. 1000x thanks to you.

I understand know why I could not find anything to get the value from a REF
field as people get the value from the bookmark!

I didn't know ASK fields store the data in Bookmarks - certainly learned
something new & valuable there! But also if you are not using bookmarks and
don't have them visible the document view ... nothing is going to ring a bell
to connect two dots.

Jay, again a very big thanks for your most valuable and accurate answer. I
couldn't have done this last part without your answer.

 

[frank dobbelaere]

[The internet seems to type out. Apologies if this becomes a double post]

Hi Doug,

Thank you for your response.

I agree with you that one box is better than a series.

That was my goal over a decade ago when I last made some documents (but
access to resources were limited, there was no internet, no mvp, ...) Now
over a decade later, learning about userform and seeing that it is relatively
easy to do I wanted to go that route. Plus I didn't remember my old autonew
ask setup.

So, I read and printed at least three userform tutorials yesterday. This
includes the mvp one you linked and the excellent one from Malcolm Smith at
Dragon Drop. With that in hand I created my userform and it worked like a
charm (with a few bumps at first of course)

My problem with the userform is that according to the examples I read so
far, I can only use the entered data once.

Answer 1 -> Bookmark 1
Answer 2 -> Bookmark 2
etc

and it wasn't clear to me how I could go farther to get the value and go to
the next steps.

On top of that I was at that point struggling with the fact that bookmarks
in the header did not get updated. I fell back on ASK / REF. Same problem. By
then half a day later I discovered later you have to macro/vba into the
header to update the fields there.

Even though I am still on the ASK REF sidetrack, right now, I am most
interested in making the userform.

What would be your suggestion to have one answer repeated in two or three
places? Can I use a REF field or something instead of a bookmark? According
to Malcolm Smith I should create more bookmarks, install them in the template
and then in macro link them to the same value.

Also, you said I can use the .Text property of the controls on the userform
to supply the information that I want.

Any tutorial or example you could link to that would cover that extra step?
.... I admit it has been a long day and I can't think straight right now. I
may have something printed some example that covers it, but I don't see it in
front of me.

That way I could recreate what Jay just helped me with, but using a userform.

Thank you in advance,

Frank

 

[Doug Robbins - Word MVP]

Hi Frank,

To get the information to appear in multiple places in the document, there
are several methods. In each of these, I am going to assume that it is the
name of a person that you want to appear in multiple places and that on the
userform, you have a textbox control to which you have assigned the name
txtName.

1. You can have multiple bookmarks - Assuming that you have bookmarks with
the names "Name1", "Name2", etc., then in the code for the command button
click event in the user form (at least the way I do it, Malcolm Smith might
do it another way) you would have:

With ActiveDocument
.Bookmarks("Name1").Range.InsertBefore txtName.Text
.Bookmarks("Name2").Range.InsertBefore txtName.Text
'etc.
End With

2. Another way is just to have the one bookmark and use cross reference
fields at the other locations to get the text of the bookmark. For this to
work, you have to be sure that the text is actually being inserted into the
..Range of the bookmark and not before it. To get the text inserted into the
bookmark, when you insert the bookmark into the template, you must have a
space selected, so that if you turn on the display of bookmarks, it looks
like [] rather than a single |.

The other thing is that you have to use some code to update the fields in
the document so that the cross references are updated. If the cross
references are all in the body of the document, you can simply use:

ActiveDocument.Range.Fields.Update

But that will not update fields that are in the Header(s)/Footer(s) of the
document, and there might be multiple headers as they are a property of a
Section of which can have a First Page Header/Footer, a Primary
Header/Footer, and Odd Page Header/Footer and an Even Page Header/Footer
(though I think that one of these is considered as the Primary.

Now you can use code that updates the Range of all of the possible
headers/footers in the document as follows:

Dim i As Long
With ActiveDocument
.Range.Fields.Update
For i = 1 To .Sections.Count
With .Sections(i)
.Headers(wdHeaderFooterEvenPages).Range.Fields.Update
.Headers(wdHeaderFooterFirstPage).Range.Fields.Update
.Headers(wdHeaderFooterPrimary).Range.Fields.Update
.Footers(wdHeaderFooterEvenPages).Range.Fields.Update
.Footers(wdHeaderFooterFirstPage).Range.Fields.Update
.Footers(wdHeaderFooterPrimary).Range.Fields.Update
End With
Next i
End With

But as long as the user has the Update Fields at Print option set under
Tools>Options>Print, you can simply use:

With ActiveDocument
.PrintPreview
.ClosePrintPreview
End With

or, in case they don't have that option checked, you can check it for them
with

Options.UpdateFieldsAtPrint = True
With ActiveDocument
.PrintPreview
.ClosePrintPreview
End With

3. The third way is to have code in the command button click event, set
values of variables (document variables actually) to the data that is
entered into the form and then have that displayed in the document by the
use of { DOCVARIABLE [variable name] } fields. Assuming that you are going
to use a variable with the name "varName", in the command button click
event, you would use:

With ActiveDocument
.Variables("varName").Value = txtName.Text
' similar code for other variables
End With

Then in the document, where ever you want what was entered into the txtName
control on the user form to appear, you would insert docvariable fields

{ DOCVARIABLE varName }

Unlike bookmarks, each of which must have a unique name, you can have as
many of these { DOCVARIABLE varName } fields in the document as you like.
Being fields however, they do need to be updated in the same way as the
cross reference fields to the text of bookmarks would, so you either have to
update the fields in all of the possible .Ranges of Headers and Footers of
each Section, or resort to the .PrintPreview/.ClosePrintPreview routine
mentioned above.

One particular advantage of the use of Docvariable fields, though it does
also apply to the use of cross reference fields is that you can use the
\*charformat switch on the fields to control the way in which the text
appears by applying the desired font characteristics to the D of
DOCVARIABLE.

You also avoid the problem mentioned above of the data not actually being
inserted into the .Range of the bookmark.

Now for you other quest to get the data entered into the form for use in

Dim dlg As Dialog
Set dlg = Dialogs(wdDialogFileSaveAs)
With dlg
..name = strName & strNumber & "_" & strDocumentType & strDate & ".doc"
..show
End With

Assuming that this code is used after the .Values of the document variables
have been set, you would use

..name = ActiveDocument.Variables("varName").Value &
ActiveDocument.Variables("varNumber").Value & "_" & strDocumentType &
strDate & ".doc"



--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

[The internet seems to type out. Apologies if this becomes a double post]

Hi Doug,

Thank you for your response.

I agree with you that one box is better than a series.

That was my goal over a decade ago when I last made some documents (but
access to resources were limited, there was no internet, no mvp, ...) Now
over a decade later, learning about userform and seeing that it is
relatively
easy to do I wanted to go that route. Plus I didn't remember my old
autonew
ask setup.

So, I read and printed at least three userform tutorials yesterday. This
includes the mvp one you linked and the excellent one from Malcolm Smith
at
Dragon Drop. With that in hand I created my userform and it worked like a
charm (with a few bumps at first of course)

My problem with the userform is that according to the examples I read so
far, I can only use the entered data once.

Answer 1 -> Bookmark 1
Answer 2 -> Bookmark 2
etc

and it wasn't clear to me how I could go farther to get the value and go
to
the next steps.

On top of that I was at that point struggling with the fact that bookmarks
in the header did not get updated. I fell back on ASK / REF. Same problem.
By
then half a day later I discovered later you have to macro/vba into the
header to update the fields there.

Even though I am still on the ASK REF sidetrack, right now, I am most
interested in making the userform.

What would be your suggestion to have one answer repeated in two or three
places? Can I use a REF field or something instead of a bookmark?
According
to Malcolm Smith I should create more bookmarks, install them in the
template
and then in macro link them to the same value.

Also, you said I can use the .Text property of the controls on the
userform
to supply the information that I want.

Any tutorial or example you could link to that would cover that extra
step?
... I admit it has been a long day and I can't think straight right now. I
may have something printed some example that covers it, but I don't see it
in
front of me.

That way I could recreate what Jay just helped me with, but using a
userform.

Thank you in advance,

Frank

 

[frank dobbelaere]

Thank you very much, Doug, for your long answer.

With the extra information you gave I should have all the details together
to take it a step further. I printed this page and I will start to experiment
in the upcoming days.

For a few days the Autonew ASK REF should be fine. It is already a huge
improvement over the old manual way.

Thanks again,

And I will certainly reply here with positive news and/or questions.

 

[philip]

Hi,
Old post but I hope someone can help me.

I did exactly as mentioned in previous posts, but cannot (Word 2003) get the bookmarks in the save file name. Have tried it with the bookmarks in regular text, did not work.
Anyone knows how to get bookmarks from the header and from a textbox in the header into my file save name?

help is much appreciated.


Sub SaveFileAsTest()

Dim oStory As Range
Dim pFileName As String
Dim docTitle As String
Dim docNr As String
Dim docRev As String

With ActiveDocument
docTitle = .Bookmarks("DocumentName").Range.Text
docNr = .Bookmarks("Report").Range.Text
docRev = .Bookmarks("Rev").Range.Text
End With


pFileName = docNumber & "-" & docRev & "-" & docTitle

With Application.Dialogs(wdDialogFileSaveAs)

..Name = pFileName
..Show

End With

ActiveWindow.Caption = ActiveDocument.FullName
For Each oStory In ActiveDocument.StoryRanges
oStory.Fields.Update
If oStory.StoryType <> wdMainTextStory Then
While Not (oStory.NextStoryRange Is Nothing)
Set oStory = oStory.NextStoryRange
oStory.Fields.Update
Wend
End If
Next oStory
Set oStory = Nothing

End Sub

Hi,

I'm new at this macro / VBA (first post here) and this has been a pretty
exciting journey so far.

Using Word 2003 I am trying to write a couple macros to automate a few
repetitive document functions. I looked at some books in the book store
(which didn't cover the subject), have printed several good tutorials
referenced frequently and prominent in google ranking (I'm sure you know the
ones I found), searched quite a bit through the help, msdn, mvp sites, ...
and have analyzed some related samples, but can't find this detail.

Goal: With this macro I would like to automate and thus standardize the
suggested file save as name. (showing the dialog to allow the user control
over the destination; so not automatically saving the file without dialog ...
at least not for the time being, a further step might be to suggest saving it
with a modified save as path that includes a subfolder based on the name, but
first things first)

Background:
I created a new template.
By the way the title of the template is set to Note, Letter, Fax - which
will be referenced as well. (this is just the first template of 3 - 4 I'm
making).
Via ASK fields it asks a few questions (Name, Number, ...) activated by an
AutoNew macro.
REF fields show the answers in the resulting document (document & header,
extra macro piece added to make sure it updates the field results in the
header).

After finishing the document I would like to use a customized Save As dialog.

The special save as ...

Created a macro which
- looks at the document properties title
- grabs the current date
and then succesfully displays a dialog as follows:

Dim dlg As Dialog
Set dlg = Dialogs(wdDialogFileSaveAs)
With dlg
.name = "_" & strDocumentType & strDate & ".doc"
.show
End With

Now, before the underscore I would like to access the value of Name and
Number; and thus suggest this name:

.name = strName & strNumber & "_" & strDocumentType & strDate & ".doc"

So, my questions is, how can I get the values of { REF Name } and { REF
Number } into strName and strNumber?


Note: With this file name idea I can ending up with very long file names, so
I am using a shorthand date and using an abbreviation for the document type;
done earlier in the macro.

An additional question comes in if it is possible to maniuplate the strName
and have the initials from {REF Name} rather than the full first name and
last name?
Or maybe it should be two ASK questions FirstName and LastName? And take the
initial from both?

Of course an additional ASK for initials would be possible; but it should be
possible to do it automatically, I think.

Thanks in advance for your help and advice.

Frank

On Sunday, March 02, 2008 5:53 PM Jay Freedman wrote:
Hi Frank,

Each ASK field creates a bookmark in the document and stores the user's answer
in the bookmark. All the REF field does is repeat that value elsewhere in the
document.

In VBA, you can get the value of the bookmark's contents and assign it to a
string, like this:

Dim strName As String, strNumber As Number
With ActiveDocument
strName = .Bookmarks("Name").Range.Text
strNumber = .Bookmarks("Number").Range.Text
End With

Whether you use separate ASK fields (and thus separate bookmarks) for the
FirstName and LastName or a single ASK field for the Name is a choice that's up
to you. My preference would be for a single field, because users dislike
multiple boxes popping up, each of which takes an extra click or keystroke to
press the OK button.

If you have a single field, you can separate it into chunks separated by spaces
and grab the initials like this:

Sub demo()
Dim strName As String, strInits As String
Dim arrayName As Variant, pieceName As Variant

' get the value of the bookmark (whole name)
strName = ActiveDocument.Bookmarks("Name").Range.Text

' words separated by spaces go into an array
arrayName = Split(strName, " ")

' get the first letter of each word
For Each pieceName In arrayName
strInits = strInits & Left(pieceName, 1)
Next

' show us what you got...
MsgBox strInits
End Sub

It doesn't have a lot of smarts, though. For example, if someone enters "Ludwig
von Beethoven" in the ASK field, the initials will come out as "LvB".

--
Regards,
Jay Freedman
Microsoft Word MVP
Email cannot be acknowledged; please post all follow-ups to the newsgroup so all
may benefit.

On Sunday, March 02, 2008 8:57 PM frankdobbelaer wrote:
[The internet seems to type out. Apologies if this becomes a double post]

Hi Doug,

Thank you for your response.

I agree with you that one box is better than a series.

That was my goal over a decade ago when I last made some documents (but
access to resources were limited, there was no internet, no mvp, ...) Now
over a decade later, learning about userform and seeing that it is relatively
easy to do I wanted to go that route. Plus I didn't remember my old autonew
ask setup.

So, I read and printed at least three userform tutorials yesterday. This
includes the mvp one you linked and the excellent one from Malcolm Smith at
Dragon Drop. With that in hand I created my userform and it worked like a
charm (with a few bumps at first of course)

My problem with the userform is that according to the examples I read so
far, I can only use the entered data once.

Answer 1 -> Bookmark 1
Answer 2 -> Bookmark 2
etc

and it wasn't clear to me how I could go farther to get the value and go to
the next steps.

On top of that I was at that point struggling with the fact that bookmarks
in the header did not get updated. I fell back on ASK / REF. Same problem. By
then half a day later I discovered later you have to macro/vba into the
header to update the fields there.

Even though I am still on the ASK REF sidetrack, right now, I am most
interested in making the userform.

What would be your suggestion to have one answer repeated in two or three
places? Can I use a REF field or something instead of a bookmark? According
to Malcolm Smith I should create more bookmarks, install them in the template
and then in macro link them to the same value.

Also, you said I can use the .Text property of the controls on the userform
to supply the information that I want.

Any tutorial or example you could link to that would cover that extra step?
... I admit it has been a long day and I can't think straight right now. I
may have something printed some example that covers it, but I don't see it in
front of me.

That way I could recreate what Jay just helped me with, but using a userform.

Thank you in advance,

Frank

On Monday, March 03, 2008 4:11 AM Doug Robbins - Word MVP wrote:
Hi Frank,

To get the information to appear in multiple places in the document, there
are several methods. In each of these, I am going to assume that it is the
name of a person that you want to appear in multiple places and that on the
userform, you have a textbox control to which you have assigned the name
txtName.

1. You can have multiple bookmarks - Assuming that you have bookmarks with
the names "Name1", "Name2", etc., then in the code for the command button
click event in the user form (at least the way I do it, Malcolm Smith might
do it another way) you would have:

With ActiveDocument
.Bookmarks("Name1").Range.InsertBefore txtName.Text
.Bookmarks("Name2").Range.InsertBefore txtName.Text
'etc.
End With

2. Another way is just to have the one bookmark and use cross reference
fields at the other locations to get the text of the bookmark. For this to
work, you have to be sure that the text is actually being inserted into the
.Range of the bookmark and not before it. To get the text inserted into the
bookmark, when you insert the bookmark into the template, you must have a
space selected, so that if you turn on the display of bookmarks, it looks
like [] rather than a single |.

The other thing is that you have to use some code to update the fields in
the document so that the cross references are updated. If the cross
references are all in the body of the document, you can simply use:

ActiveDocument.Range.Fields.Update

But that will not update fields that are in the Header(s)/Footer(s) of the
document, and there might be multiple headers as they are a property of a
Section of which can have a First Page Header/Footer, a Primary
Header/Footer, and Odd Page Header/Footer and an Even Page Header/Footer
(though I think that one of these is considered as the Primary.

Now you can use code that updates the Range of all of the possible
headers/footers in the document as follows:

Dim i As Long
With ActiveDocument
.Range.Fields.Update
For i = 1 To .Sections.Count
With .Sections(i)
.Headers(wdHeaderFooterEvenPages).Range.Fields.Update
.Headers(wdHeaderFooterFirstPage).Range.Fields.Update
.Headers(wdHeaderFooterPrimary).Range.Fields.Update
.Footers(wdHeaderFooterEvenPages).Range.Fields.Update
.Footers(wdHeaderFooterFirstPage).Range.Fields.Update
.Footers(wdHeaderFooterPrimary).Range.Fields.Update
End With
Next i
End With

But as long as the user has the Update Fields at Print option set under
Tools>Options>Print, you can simply use:

With ActiveDocument
.PrintPreview
.ClosePrintPreview
End With

or, in case they don't have that option checked, you can check it for them
with

Options.UpdateFieldsAtPrint = True
With ActiveDocument
.PrintPreview
.ClosePrintPreview
End With

3. The third way is to have code in the command button click event, set
values of variables (document variables actually) to the data that is
entered into the form and then have that displayed in the document by the
use of { DOCVARIABLE [variable name] } fields. Assuming that you are going
to use a variable with the name "varName", in the command button click
event, you would use:

With ActiveDocument
.Variables("varName").Value = txtName.Text
' similar code for other variables
End With

Then in the document, where ever you want what was entered into the txtName
control on the user form to appear, you would insert docvariable fields

{ DOCVARIABLE varName }

Unlike bookmarks, each of which must have a unique name, you can have as
many of these { DOCVARIABLE varName } fields in the document as you like.
Being fields however, they do need to be updated in the same way as the
cross reference fields to the text of bookmarks would, so you either have to
update the fields in all of the possible .Ranges of Headers and Footers of
each Section, or resort to the .PrintPreview/.ClosePrintPreview routine
mentioned above.

One particular advantage of the use of Docvariable fields, though it does
also apply to the use of cross reference fields is that you can use the
\*charformat switch on the fields to control the way in which the text
appears by applying the desired font characteristics to the D of
DOCVARIABLE.

You also avoid the problem mentioned above of the data not actually being
inserted into the .Range of the bookmark.

Now for you other quest to get the data entered into the form for use in

Dim dlg As Dialog
Set dlg = Dialogs(wdDialogFileSaveAs)
With dlg
.name = strName & strNumber & "_" & strDocumentType & strDate & ".doc"
.show
End With

Assuming that this code is used after the .Values of the document variables
have been set, you would use

.name = ActiveDocument.Variables("varName").Value &
ActiveDocument.Variables("varNumber").Value & "_" & strDocumentType &
strDate & ".doc"



--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

message news:[email protected]...

 

[David Sisson]

Make sure the bookmarks encapsulate the data.

Insert->bookmark-> Select bookmark in question. If the text isn't
highlighted, then the bookmark doesn't encapsulate the data. If it is
just a blinking cursor, then the bookmark serves as the starting
position of the data.

For more about this see, http://word.mvps.org/faqs/macrosvba/InsertingTextAtBookmark.htm