How to get the results of a function inserted into an Word (2003) doc? 1

[DayLaborer]

I have a function that returns a value, conceptually similar to this:

Public Function MyLittleFunction()
  MyLittleFunction = "I want this value in a particular spot in my MS Word document."
End Function

(The similarity is that it takes no arguments and returns a value.) I would like that value inserted at a particular spot in a MS Word document.

I am not sure what impact the following has on my question, but I figured it may be relevant. This Word doc is using "mail merge"; its data source is an Excel file. As far as I'm concerned, this function could go in either the Excel or the Word side of the process, although logically I would think it should be on the Word doc.

How can I have that value automatically appear in the Word doc without asking the user to click or do something?

Thanks,
Eliezer

 

[SkipVought]

Hi,

How did you propose for the user to designate exatly where to put this function, if, indeed, he could? I mean he/she is dragging mail merge values into the document. What means do you envision to use for this function?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Remou]

If this is mailmerge, why not have a field that will insert the function value as is normal in mailmerge? It is possible to set a cell in Excel to

=mylittlefunction()

http://lessthandot.com

 

[fumei]

a particular spot".

Well, in a Word document that could be a number of things.

A formfield is a particular spot.

ActiveDocument.Formfields("ThisOne").Result = _
     MyLittleFunction

A Bookmark is a particular spot.

ActiveDocument.Bookmarks("ThisOne").Range.Text = _
     MyLittleFunction

Or, as Remou suggests, have it going into a field.

Basically, the function simply returns a string. A string can be be placed anywhere you want it. You just have to define and explicitly state where.


Gerry

 

[DayLaborer]

Thank you, all! Gerry, I was thinking along your lines. I have this:

Private Sub Document_Open()
    ActiveDocument.Bookmarks("TEST3").Select
    Selection.Text = MyFunction()
End Sub

But the only problem is that it keeps adding onto that line every time the document is opened and subsequently saved. How can I change the code above to overwrite the text on the line with the value of the function?

Thanks!
Eliezer

 

[fumei]

1. do not use Selection. Unless you absolutely have to, and it is rare that you do, avoid Selection. Using Range is much better.

2. are you using Option Explicit? You should actually be getting an error with the code you have.

3. you need to use a Sub that replaces the bookmark text - which deletes the bookmark - and recreates the bookmark.

4. could you explain what it is you are actually trying to do? Your Function seems kind of pointless. I am sure you mean it to do something else. As it stands, there is no need for a function at all. It is simply a hard-coded string. Your Document_Open would just replace the string over and over again. You may as well use a constant...or nothing at all.

Here is a sample of how to do it. It uses the same name as you did, for the bookmark, ("Test3"). Please note that I explicitly defined the data-type of the Function (as a string). Unless you specifically want a Variant, it is better to define function As something.

Option Explicit

Function MyFunction() As String
MyFunction = "blah blah blah"
End Function


Private Sub Document_Open()
[COLOR=red]' calls the bookmark filling Sub with
' the bookmark Name, and return result of the Function[/color red]
   Call FillBM("Test3", MyFunction)
End Sub


Sub FillBM(strBM As String, strText As String)
[COLOR=red]' the first parameter is the bookmark name
' the second is the string to put in the bookmark[/color red]
Dim r As Range
Set r = ActiveDocument.Bookmarks(strBM).Range
With r
   [COLOR=red]' this puts the text in but DELETES the bookmark[/color red]
   .Text = strText
   [COLOR=red]' collapse range to the end and expand the range
   ' backwards the length of the input string[/color red]
   .Collapse 0
   .MoveStart Unit:=wdCharacter, Count:=-Len(strText)
End With
[COLOR=red]' recreate the bookmark[/color red]
ActiveDocument.Bookmarks.Add strBM, Range:=r
   
End Sub

Gerry

 

[DayLaborer]

What I am trying to do is straightforward. I have a long function that returns one value based on today's date. I want to insert it only once at a specific point (i.e. bookmark) in a Word doc. I want the value returned from the function to be on that line only once even if the doc is opened and saved more than once. In other words, I want the line to be cleared of all values and the new value (based on the new current date) to be inserted from the formula.

Make more sense now?

Thanks yet again,
Eliezer

 

[macropod]

Hi DayLaborer,

This Word doc is using "mail merge"; its data source is an Excel file.

OK, so in Word you use either a mergefield that gets the required date from your source file or use, say, a CREATEDATE field to get the date the merge is run. For an idea of the kinds of things you can do with dates in Word, , check out my Word Field Maths 'tutorial', at:

http://www.wopr.com/cgi-bin/w3t/showthreaded.pl?Number=365442

or

http://www.gmayor.com/downloads.htm#Third_party

In particular, look at the items titled 'Date and Time Calculations In A Mailmerge' and 'Use Date (And/Or Time) Comparisons To Vary Text'.

Cheers


[MS MVP - Word]

 

[DayLaborer]

Macropod, my function converts the date into the Hebrew date, i.e. January 7, 2009 -> 11 Teves 5769. The purpose of my function isn't really relevant to my question, though.

Anyone have any suggestions to what I asked yesterday (10 Teves ) at 18:30?

Thanks!
Eliezer

 

[fumei]

I want the value returned from the function to be on that line only once even if the doc is opened and saved more than once. In other words, I want the line to be cleared of all values and the new value (based on the new current date) to be inserted from the formula."

These two sentences contradict each other.

The first one states the value to be inserted ONCE - even if the doc is opened and saved more than once.

The second states the line to be cleared and the "new value (based on the new current date) to be inserted". Which means the value IS changed of the doc is opened and saved more than once.

You can not have it both ways. It is either inserted ONCE, or it is inserted with a new value (the current date).

Pick one.

I strongly recommend you look at macropod's field tutorial, as it is the best there is around.

In any case, you can use a field (which can be updated every time), OR you can use the filling bookmark procedure that changes the contents (the text) to the result of the function.

" I want to insert it only once at a specific point (i.e. bookmark) in a Word doc."

Regarding inserting it at a specific point the bookmark filling Sub does precisely that. If that is what you want to do, then...it will do that. It will insert (replacing) the result of the function into the bookmark.

However, if you have it in the Document_Open event it will do it each time the document is opened. NOT "only once".

So please clarify. Do you want:

1. " I want the value returned from the function to be on that line only once even if the doc is opened and saved more than once. "

Or,

2. "I want the line to be cleared of all values and the new value (based on the new current date) to be inserted"

Gerry

 

[DayLaborer]

Sorry, I'd like Option #2, please. And fries with it.

eliezer

 

[fumei]

Then use the code I posted. That is what it does.

On Document_Open (every document open), it replaces the current text of the bookmark with the result of the function.

If you want to see (as a test), try using an inputbox to get the string to use - rather than the hard-coded example in the function.

1. make a new document
2. put the code in the ThisDocument module.

Option Explicit

Function MyFunction() As String
MyFunction = Inputbox("Enter some text.")
End Function


Private Sub Document_Open()
   Call FillBM("Test3", MyFunction)
End Sub


Sub FillBM(strBM As String, strText As String)
Dim r As Range
Set r = ActiveDocument.Bookmarks(strBM).Range
With r
   .Text = strText
   .Collapse 0
   .MoveStart Unit:=wdCharacter, Count:=-Len(strText)
End With
ActiveDocument.Bookmarks.Add strBM, Range:=r  
End Sub

3. put some text somewhere (does not matter what, or where), and then bookmark it as "Test3" - since that is the name used in the code. But of course you can use any name you like...as long as it matches what you are using in the code!

4. save the file and close it.

5. open the file. You will get the inputbox. Enter some text, press Enter (or if you are a mouse person, click OK) and....voila! The bookmark text will be changed to the text from the inputbox, which came from the Function.

Done.

Gerry

 

[macropod]

Actually, the tutorial I meant to point DayLaborer was my Date Calc 'tutorial', at:

http://www.wopr.com/cgi-bin/w3t/showthreaded.pl?Number=249902

or

http://www.gmayor.com/downloads.htm#Third_party

However, since DayLaborer is working with Hebrew dates, there might not be much of use there except, perhaps, the topic 'Use Date (And/Or Time) Comparisons To Vary Text'.

Cheers

[MS MVP - Word]

 

[fumei]

Oh, and I forgot your fries. Would you like that super-sized?

Gerry

 

[SkipVought]

super-sized AND fat free!!!

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[DayLaborer]

Gerry, beautiful!!!

THANKS SO MUCH!!! [star]
Eliezer

 

[fumei]

Skip, Skip. That big Texas sky has warped your brain. What on earth is the point of fat-free fries??????

Gerry

 

[SkipVought]

...oh, the irony of it all...oh, the humainty!!!

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}