How to: Recreate method to import data into word document

[keepingbusy]

Hello all

Apologies if this is the wrong forum but not sure if it's a Microsoft Word (VB) or Visual Foxpro method.

We have been asked to assist with the following:

An application currently stores data in several tables (straight forward)
The FoxPro version is believed to be 6.0
The application has a grid that is populated with records from a particular table (customers)
Within the application is a facility to open a Microsoft Word document (Mike Lewis has already exaplained previously, how I can achieve this)
Once the Word document has been opened, there is a "Populate document" command button showing on the tools bar within word (Version 2003) that allows users to click on it, which in turn, populates the word document with the data from the selected table.
As this software does not belong to us, we are avoiding the use of Refox etc and the fact that the original author of this application is not known, we are not sure how this was originally set up.

I am aware that a word document can be created from Fox but the above method has been requested and I would also suggest that this is excellent method of being able to change the Microsoft Word document instead of making change to report form.

Your views and any guidance on where to start would be appreciated.

Lee

 

[JRB-Bldr]

So are you asking how to get M$ Word to make this happen or how to do it in a VFP application?

If within a VFP application, then, as Mike Lewis has already suggested, use VFP Word automation to populate the Word form. That's not too difficult.

If you want to do this within Word itself, then I'd recommend that you post your question to another forum specializing in Office or Word (maybe: forum68).

Good Luck,
JRB-Bldr

 

[white605]

They have most likely assigned a word macro to a word toolbar button or pulldown pad of a menu.

When you say

populates the word document with the data from the selected table.

Have they (1)poplulated a list box or something with the table names to pick from or (2)do you assume its coming from the table you had selected in the vfp prg just prior to opening word.

Each way presents a different problem, but if you dont have the source or hooks into the source, you will have to do it in vba.

The problem that will come up is how to pass which record or set or records you want to report on to the word vba. Its simple to do when you are automating word from within vfp, but i havent seen an example of how to pass the filter string from vfp to word when the macro in word is taking control of whats going on.

I will be curious to see how this is done if you manage it
wjwjr


This old world keeps spinning round - It's a wonder tall trees ain't layin' down

 

[TamarGranor]

As wjwjr says, it sounds like they've set up a custom button in Word. It's likely that the code behind the button uses mail merge to bring in the data. The tricky part in doing that is creating the connection between the document and the VFP data. It's one of those things that's easier to do on the fly than to set up once and leave.

Tamar

 

[white605]

Hi Tamar,
Is there a way that you know of to pass the ball(variable containing the requirments of the recordset) from vfp to word who is now completely in control of selecting the proper data to merge?
wjwjr

This old world keeps spinning round - It's a wonder tall trees ain't layin' down

 

[keepingbusy]

white605

(2)do you assume its coming from the table you had selected in the vfp prg just prior to opening word.

The data is coming from the vfp prg prior to opening word.

As mentioned, the application is open, a record is highlighted/selected from a grid, a command button is clicked whch opens the word document, near the top of Microsoft Word is a command button on the toolbar, which populates the word document when the button pressed with the details from the record in the VFP table.

Each way presents a different problem, but if you dont have the source or hooks into the source, you will have to do it in vba.

I suspected this would be the case.

The problem that will come up is how to pass which record or set or records you want to report on to the word vba. Its simple to do when you are automating word from within vfp, but i havent seen an example of how to pass the filter string from vfp to word when the macro in word is taking control of whats going on.

I agree and I'm still trying to grasp how Word can draw the information from a record within a table from VFP. Interesting this one, but someone has already done it as mentioned.

Tamar

.... it sounds like they've set up a custom button in Word. It's likely that the code behind the button uses mail merge to bring in the data. The tricky part in doing that is creating the connection between the document and the VFP data

I agree with you. So in view of the comments I'll look into this and hopefully post back when we find out some further information.

Thanks guys

Lee

 

[JRB-Bldr]

OK, you don't want to use ReFox to decompile the VFP code and modify the application for them.

But you could use ReFox to decompile a copy of the code to see what is occurring within the VFP Form they are using - just to enhance your own understanding of how "the data is coming from the vfp prg prior to opening Word"..
Perhaps the VFP 'command' button is running a SQL Query of the grid's table and outputting the single-record result to some destination which the Word macro then uses to perform some form of mail-merge.

Once you understand how the VFP Form 'command" button and the Word "Populate Document" button work together, you can strategize how to deliver to the customer what they want.

Good Luck,
JRB-Bldr

 

[white605]

I agree and I'm still trying to grasp how Word can draw the information from a record within a table from VFP. Interesting this one, but someone has already done it as mentioned."

If i read your posts correctly, only the current record is highlighted in the vfp program and thats all that gets put into the word doc.

They probably either rite a table with one record and use that as the merge source for the word mail merge or write a mail merge file directly

part of an old Word Perfect 5.1 Merge file creation prg from foxplus

*** create new file
stor fcrea(filename) to fno
stor ferror() to fer
*** check for error opening file
if fer#0
?chr(7)+[file creation error]
wait
retu
*** go ahead and rite the file
else
goto top
DO WHILE .NOT. EOF()
*   F1*******************LAST NAME
fchr=trim(Ln_inf)+mv
mv2=fwrite(fno,fchr)
*   F2********************FIRST NAME
fchr=trim(fn_inf)+mv
mv2=fwrite(fno,fchr)
*   F3*********************address
*
fchr=trim(add1_inf)+mv
mv2=fwrite(fno,fchr)
*   F4*********************city
fchr=trim(city_inf)+mv
mv2=fwrite(fno,fchr)
*   F5*********************state
*
fchr=trim(state_inf)+mv
mv2=fwrite(fno,fchr)
*   F6*********************zip
*
fchr=trim(zip_inf)+mv
mv2=fwrite(fno,fchr)
**********************end of record
*
fchr=trim(pre_inf)+mv
mv2=fwrite(fno,fchr)
**********************end of record
*
fchr=chr(5)+chr(10)
mv2=fwrite(fno,fchr)
*   END********************************
SKIP
ENDD
stor fclose(fno) to closed
endi

This old world keeps spinning round - It's a wonder tall trees ain't layin' down

 

[keepingbusy]

jrbbldr

OK, you don't want to use ReFox to decompile the VFP code and modify the application for them.

But you could use ReFox to decompile a copy of the code to see what is occurring within the VFP Form they are using - just to enhance your own understanding of how "the data is coming from the vfp prg prior to opening Word"..

That would be useful but I'm sure you will agree that the decompiling subject has been raised many times on these forums and I think its better placed to start from the bottom and work up.

white605

If i read your posts correctly, only the current record is highlighted in the vfp program and thats all that gets put into the word doc.

Yes.

I will try out your example when I return to our office later and post back.

Many thanks

Lee

 

[white605]

Lee,
I agree with jldbldr.
The example i posted will only work before foxpro calls word to run the merge. Somehow you are going to have to either create a file with the record you want to merge in it OR pass something to word as it opens and let its merge process filter your file while gathering the data from it.

IN either case you will need to modify the fox code so REFOX is the best and mabee the only idea in your case provided you are going to do this without user intervention.
wjwjr

This old world keeps spinning round - It's a wonder tall trees ain't layin' down

 

[JRB-Bldr]

"better placed to start from the bottom and work up."

I would most heartedly agree if you were creating this 100% on your own.

But since you also say "someone has already done it", I can only assume that you want to find out how they did it.

In that case, use ReFox to find out how someone else did it.

Then, with that understanding, you can either follow their model or take your own approach.

Good Luck,
JRB-Bldr

 

[TamarGranor]

Yes, you can have Word ask for the data once and then it's there. The basic idea is that write some VFP code that creates an Excel or even a Word document in the format needed for mail merge.

In older versions of Word, you could also set up the list of possible mail merge fields. I haven't tried since Word XP, so I don't know if that still works (and I haven't looked at my code for this since then either).

But it is all possible.

Tamar

 

[keepingbusy]

Thank you Tamar