Help Running Word from VFP

[dobber]

I'm running Word from VFP and I am attempting to change the default directory the users will retrieve and create Word documents in to be C:\AUDITS. I'm using the following code, but I keep getting an error (OLE error code 0x80020006:unknown name). Does anyone know what's wrong or a better way to do this?

*start Word
oWord = CreateObject('Word.Application')

*change default file path for Word
oWord.defaultfilepath = "c:\audits"

*show Word to the user
oWord.Visible = .T.

 

[foxdev]

DefaultFilePath is a property of the Options object, not the application object.

It would be more like:

oWord.Options.DefaultFilePath(wdDocumentsPath) = "c:\My Documents"

except replace wdDocumentsPath with the actual value that it represents (its a standard definition, but you can't use it from within VFP).

 

[Ropje]

hello dobber,
I don't have the answers to your question, but am very interested in the combination Foxpro/Word.
Do you do anything else, apart from starting Word?
I want to find a way to make a lot of information from my application available in Word, so I can sent letters to clients, make reports etc.
I am talking about a cursor with 200 fields, with al sorts of data.
The best way I can think of so far, is by making a delimited text file from the data, and use a Word-macro to merge.
But all this is very slow, and has it's limits.
Do you have any suggestions, ideas?

Greetings Rop

 

[foxdev]

Instantiating Word via its COM interface gives you much better control than macros and traditional mailmerge.

If you have Word 2000 installed, search your system for the file: VBAWRD9.CHM. It contains the entire object model and generic (read: VB) examples.

I use Word with its Custom Document Properties (properties you add in your Word template under File/Properties). Here's a cryptic example:

[tt]oWord = createobject("Word.Application&quot
assert VarType(oWord) = "O"

* -- mytemplate.dot contains custom doc properties
oWord.Documents.Add("c:\mytemplate.dot&quot

With oWord.ActiveDocument.CustomDocumentProperties

* -- set property values
.item("LetterDate&quot.Value = dtoc(date())
.item("Contact&quot.Value = "Fred Jones"
.item("Company&quot.Value = "Fred's Shop"
.item("Address1&quot.Value = "Suite 400"
.item("Address2&quot.Value = "1234 E. Main St"
.item("city&quot.Value = "Anytown"
.item("state&quot.Value = "US"
.item("zipcode&quot.Value = "78758"

EndWith

oWord.Selection.WholeStory
oWord.Selection.Fields.Update

oWord.ActiveDocument.SaveAs("c:\MyNewDoc.doc&quot

oWord.Quit
release oWord

[/tt]

 

[dobber]

To Robje:

I work for an organization that provides audit services for companies at their place of business. I am trying to develop a sytem for the auditors that will use VFP for the front end (menus, etc) and to store database type data. It will call up Word and allow the user to retrieve, edit, and store "canned" documents containing the narrative of the work done. It will also call up Excel and do the same for the types of data that are better handled in a spreadsheet environment.

I'm just beginning this project, so I'm not even sure exactly how I'm going to accomplish it. I have a background in programming using Foxpro 2.6 (dos), so all the Object type programming is very new to me and it will be awhile before I become even somewhat proficient at it. So I'm sorry but I don't have any suggestions for you at this time. Check back when I've learned a little more.

 

[TomasDill]

To Ropje:
I worked on 2-year large project with the same features. We choosed the most reliable and flexible way. We use document template with bookmarks, that will be later replaced by data. Special table stores information about wich bookmark should be filled by which data. These could be defined in special form for making new templates.
This application also allow to make new data sources for letters, run letter in background mode and schedule background printing or automatic letter creation when certain business actions occur. For example, you receive call from customer, fill some data, press ok - done. All documents will be created automatically, sent to Vendor by Fax and by EMail to customer.
Background working with Word requires quite reliable programming (background process should never hang up). So we solved a LOT of problems with Word, mail merge, Word configuring, document printing and so on. Solved most problems with Word hanging up during OLE automation session. Found more reliable way to launch Word OLE Automation instance and close it despite it hanged up, never leaving it in memory after application working.
Please, contact me when you have serious technical problems and questions.

 

[Ropje]

Thank you, Tomas, Dobber en Robert, for replying to my reply, which was more a question.
Tomas, I think I more or less understand your approach, but the problem with my application is that the user MUST be able to edit. We now provide means to make a "template letter" in Word, using an empty delimited file (only field names).
While browsing through the table with your clients in the application, you can push a "letters"-button, and select one of these selfmade templates. A wordmacro (I know nothing about) merges the fields from a cursor into the final letter, that can be edited and saved by the user at any desired location. The application has to keep track of which (template)letters were sent to which clients. It works more or less, but we are approaching the limits (cursor 256 fields, a lot of subqueries to collect all the data they want to use). Another problem is that the delimited file has to be 100%, so it becomes a very fragile construction when you want to "export" memo files containing all sorts of characters. Using your bookmark system would avoid that problem, I think? I would like to here more..

 

[TomasDill]

Yes, you're quite very right about these problems. That is why bookmarks replacing is quite a reliable way. See following notes.
1. Bookmarks replacing done in the VFP routine, not in the Word macro. We forget about Word macro only because very simple reason - maintenance. Imagine what may do users with Word templates? Right, edit them, save them, create new templates etc. On some stage user may not include macros or they will be altered by some way. But this is not good approach, because bookmarks replacing is strictly developer routine that always should be hardcoded. So, we put it into VFP. Many commands through OLE? Right, but see FoxDev articles about speed of VFP with OLE - it 5-6 times better than in VB. In our existing system it works perfect.
2. VFP limitation about 256 field in table is not a problem too, when you use bookmarks. You just may creat as many cursors with data as you want. Do not nee to import data somewhere - just replace bookmarks in Word by these data - done. Remember, that you will have table that 'maps' all bookmark names in Word template to the real data source name. So you will have records like bollowing:
Bookmark | DS field name
CompanyName | generaldata.cCompName
VendorName | Vendors.cName
VendorAddress| Vendors.mAddress
Customer | customers.cFirstName + ' ' + customers.cLastname
-----------------------------------
Simple routine makes replacements using 'evaluate' function for right column of above table. Than place selection in Word to the text of bookmark (Method 'GoTo') and than replace it by result (using transform universal function for non-character data).
In the Word template all Bookmarks are formatted by special way for users, so they do not delete them occasionally. When they delete some of bookmarks - nothing happen, data just will not be inserted (trap OLE error 'Bookmark not found').
3. Yes, memo replacement by bookmarks is not a problem. We even found a way to insert images into document by bookmark.
4. After bookmarks replacement you may do with document whatever you want. Preview it in Word and fix something (just show Word instance we're working with), immediately print it, save it to a temporary document file and send by EMail as attachment etc. And no problems or overhead with macros in Word or communication from Word to VFP to check what was done with document (it sent, saved or printed) just to track all client documents. This because all actions with documents done from VFP application - Printing, faxing, EMailing, saving for later printing with letterhead or printing on coverpage paper.

 

[aharrisreid]

TomasDill,

I know it is some time since you replied to this thread, but I am very interested in how you performed the bookmark replacement.

>>Than place selection in Word to the text of bookmark (Method 'GoTo') and than replace it by result <<

Could you (or anyone else who knows) please give me the VFP syntax for searching for the bookmark and inserting the appropriate text?

Any help would be appreciated.

Alan

 

[StewartUK]

Alan,

I use this syntax:-

WITH oDoc
IF oDoc.Bookmarks.Count>0
IF .Bookmarks.Exists(&quot;existing&quot
.Bookmarks(&quot;existing&quot.Select
WITH .Application.Selection
.MoveDown(_WDLINE, 1, _WDEXTEND) && each instance of this line extends the selection to include the blank line after the paragraph
.Delete
ENDWITH
ENDIF
ENDIF
ENDWITH

to delete bookmarks before printing, so if you edit that to something like

You would want something like:

WITH oDoc
IF oDoc.Bookmarks.Count>0
IF .Bookmarks.Exists(&quot;existing&quot
.Bookmarks(&quot;existing&quot.Select
.Application.Selection.Text = MyNewText
ENDIF
ENDIF
ENDWITH

Hope that helps,

Stewart

 

[aharrisreid]

Stewart, many thanks for the reply.

>>You would want something like:

WITH oDoc
IF oDoc.Bookmarks.Count>0
IF .Bookmarks.Exists(&quot;existing&quot
.Bookmarks(&quot;existing&quot.Select
.Application.Selection.Text = MyNewText
ENDIF
ENDIF
ENDWITH

Hope that helps,<<

It certainly does. A couple of further questions...

* Once the selected text is replaced with MyNewText, is the bookmark still in place so that when the next letter is called, MyNewText is replaced with MyNewNewText? In other words, is the bookmark text irrelevant, so long as the bookmark name doesn't change?

* Instead of selecting the bookmark and replacing the text, I have read about another method which goes to the bookmark and inserts the text.
oDoc.EditGoto (&quot;contact&quot
oDoc.Insert (Customer.Contact)
Would this have the same effect? Would this code only work if there if the bookmark has no text against it.

* This is my first venture into mailmerging VFP data with a Word document. The other method that I have been recommended is to set up mergefield fields instead of named bookmarks. Do you have any experience of this? Do you know the pros-and-cons of one method over the other?

Many thanks for your help.

Alan Harris-Reid
BaseLine Data Services (UK)

 

[StewartUK]

Alan,

I'm glad my suggestion helped.

Answering your questions
1. I would have thought the bookmark would remain. You can test this by, after creating the instance of word with CREATEOBJECT, setting oWord.Visible = .T.. You can then watch the changes as you run your code.

I would suggest that you run some code to replace the bookmark and then, in Word, click the Insert menu pad & bookmarks and (a) check the bookmark is still listed and (b) when you click the goto button, make sure that is still selects the block it should select. If the new text is of different length to what was there before, I would hope the bookmark would automatically change size.

2. I haven't come across this, but as I say you can experiment with Word visible alongside VFP and see what happens when you try running the EditGoTo & Insert

3. The way I have set up our letters (you may remember I work in-house) is that each letter has what are effectively mergefields. In fact they way I use them is that my VFP program uses Word's Find & Replace.

For example...
WITH oDoc.Application.Selection
*!* Replace placeholders WITH date, address block, salutation & gift info...
.Find.Execute(&quot;&dearhere&quot;,.T.,,,,,,,,ThisSalutation,1)
.Collapse(0)
*!* Change the place markers as appropriate. If the placemarkers aren't found, then nothing happens....
.Find.Execute(&quot;&GIFT&quot;,.T.,,,,,,,,GiftAmount,1)
.Collapse(0)
.Find.Execute(&quot;&ALLOC&quot;,.T.,,,,,,,,GiftAllocation,1)
.Collapse(0)
ENDWITH

...so in the letter are the mergefields (I call them placeholders) &dearhere, &GIFT, &ALLOC. You can see the long list of commas - the Find.Execute takes loads of parameters, most of which you can ignore but some are important and they're near the end (of course!).

The Execute code above is saying find &GIFT, the .T. means replace it, GiftAmount is what is put in place. I think the 1 at the end means only replace one instance.

I mentioned in my earlier posting that I actually use the bookmarks for deleting paragraphs I don't want in certain circumstances. This saves having 2 almost identical letter files. My code extends the bookmark to take in the blank line following - I don't know why I didn't just make the bookmark include the blank line, but there you go! If you should happen to try this, I recommend that you only extend the bookmark by 1 character rather than one line as shown in my code as this is more reliable.

I have also set up a process for mailmerging a letter to a list of contacts. For this I use Word's own mailmerging process. I actually got the syntax for this from another thread here on Tek-Tips. I'll see if I can find it after I've posted this.

I can also recommend the Hentzenwerke book on Office Automation.

Hope that all makes sense & helps you again. Get back if you want more guidance.

Stewart

 

[StewartUK]

Hi,

Actually it was an FAQ - faq184-2410 written by Danceman

I expect you know about Word's object model? I've found it very useful.

You get to it by opening Word, type Alt+F11. This brings up VB. Press F2 to get to Object Browser. Look in libraries for Word. If it's not there, click on Tools\References and check Word in the list of Available References.

I hope this helps. I'm happy to email you some of my code if that might help.

Stewart