Populate Word Form from VFP Database

[HowardShachter]

I have what should be a common application but have not had much success in finding a solution posted to this or other newsgroups.

I want to create a form in MS Word 2003. The first entry is Sales Order Number. I then want to look up the Sales Order and Customer information in my VFP data files and populate the Name, Address, City and other similar fields in the Word form.

I know this is doable, but I am a novice in VBA. I can get around rather well in VFP, but that is not very relevant in this situation.

Thanks for your help.
Howard Shachter

 

[fumei]

I will show my ignorance here. I have no idea what VFP is. That being said, I do know Word. So, if you have data, and a way of reading it, then Word can certainly take data and put it into Form Fields - and other types of fields.

It can also handle logic so that when a user selects an item out of Sales order, it populates other Form Fields with other data.

If you are using Form Fields, give them a proper name. Word gives default names. Say, change DropDown1 to SalesOrderNum.

Dropdowns are originally cleared by the .ListEntries.Clear method.

They are populated by .ListEntries.Add Name:=

The other fields can be populated by logic. Depending on how many items you have in the source field (SalesOrderNum), a Select Case statement would be the easiest.

See Help on the methods mentioned, also on FormFields.

Gerry

 

[HowardShachter]

Thank you Fumei.

VFP stands for Visual Foxpro, a Microsoft database product. I need to know how to connect to the data source, extract the necessary information (through an SQL statement) and then how the data is stored in VBA (what to put after the equal sign for "Customer Name").

 

[fumei]

OK. I think you would use the DDERequest Method, to return a string. See Help on DDERequest Method.

Make the result a variable sCustName

The string would go into the the field. If you are using a Form Field, and it is a dropdown - see Help on DropDown, on FormFields etc. , but it would be something like:


ActiveDocument.Formfields("CustomerName"). _
DropDown.ListEntries Add Name:= sCustName

and then on to your next record.

Same goes with ActiveX controls, which can also be text, or dropdown.



Gerry

 

[jfhewitt]

Word will take data from an MS FoxPro file. Just select as your data source in merge.

 

[jdhilljr]

What version of VFP are you using? Why wouldn't you rather use a designed report in VFP it is fater than using word?

Here is a brief sample to format word.

This is only a portion of the procedure.

WAIT WIND "Creating Word Document..." NOWAIT
ox=CREATEOBJECT("word.application")
ox.documents.ADD
ox.activedocument.pagesetup.topmargin = (vtopmargin*72)
ox.activedocument.pagesetup.bottommargin = (vbotmargin*72)
ox.activedocument.pagesetup.leftmargin = (vleftmargin*72)
ox.activedocument.pagesetup.rightmargin = (vrightmargin*72)
GO TOP
ox.SELECTION.FONT.NAME = "Times New Roman"
ox.SELECTION.FONT.bold = .T.
ox.SELECTION.FONT.SIZE = 18
ox.SELECTION.paragraphformat.ALIGNMENT = 1
ox.SELECTION.typetext("Complimentary Ticket Detail Report")
ox.SELECTION.typeparagraph
ox.SELECTION.FONT.SIZE = 12
ox.SELECTION.typetext(IIF(refselect = 1," - by Show Date -,IIF(refselect=2,"- by Refund Date-","-by All Dates")))
ox.SELECTION.typeparagraph
ox.SELECTION.FONT.italic = .T.
ox.SELECTION.typetext(DTOC(DATE()))
ox.SELECTION.typeparagraph
ox.SELECTION.FONT.italic = .F.
ox.SELECTION.FONT.SIZE = 10
ox.SELECTION.FONT.bold = .T.
ox.SELECTION.typetext(rfilter)
ox.SELECTION.typeparagraph
ox.SELECTION.typeparagraph
ox.SELECTION.paragraphformat.ALIGNMENT = 3
ox.SELECTION.typeparagraph
ox.SELECTION.FONT.SIZE = 8
ox.SELECTION.paragraphs.tabstops.ADD(.75*72)
ox.SELECTION.paragraphs.tabstops(1).ALIGNMENT=0
ox.SELECTION.paragraphs.tabstops.ADD(2.25*72)
ox.SELECTION.paragraphs.tabstops(2).ALIGNMENT=0
ox.SELECTION.paragraphs.tabstops.ADD(3.75*72)
ox.SELECTION.paragraphs.tabstops(3).ALIGNMENT=0
ox.SELECTION.paragraphs.tabstops.ADD(4.5*72)
ox.SELECTION.paragraphs.tabstops(4).ALIGNMENT=0
ox.SELECTION.paragraphs.tabstops.ADD(4.875*72)
ox.SELECTION.paragraphs.tabstops(5).ALIGNMENT=0
ox.SELECTION.paragraphs.tabstops.ADD(5.125*72)
ox.SELECTION.paragraphs.tabstops(6).ALIGNMENT=0
ox.SELECTION.paragraphs.tabstops.ADD(5.75*72)
ox.SELECTION.paragraphs.tabstops(7).ALIGNMENT=0
ox.SELECTION.paragraphs.tabstops.ADD(7*72)
ox.SELECTION.paragraphs.tabstops(8).ALIGNMENT=2
ox.SELECTION.paragraphs.tabstops.ADD(7.125*72)
ox.SELECTION.paragraphs.tabstops(9).ALIGNMENT=0
This is in points - multiply by 72 to get inches
ox.SELECTION.typetext(REPLICATE("=",94))
ox.SELECTION.typeparagraph
ox.SELECTION.typetext("Show Date"+CHR(9)+"Show Name"+CHR(9)"Show Time"+CHR(9)+"Section"+CHR(9)+"Row"+CHR(9)+"Seat"+CHR(9)+"Ref. Date"+CHR(9)+"Type"+CHR(9)+"Amount"+CHR(9)+"Operator")
ox.SELECTION.typeparagraph
ox.SELECTION.typetext(REPLICATE("=",94))
ox.SELECTION.typeparagraph
vrestot = 0
vamttot = 0
DO WHILE !EOF()
ox.SELECTION.typetext(DTOC(showdate)+CHR(9)+ALLTRIMhowname)+CHR(9)+ALLTRIM(showtime)+CHR(9)+ALLTRIM(vsection)+CHR(9)+ALLTRIM(vrow)+CHR(9)+ALLTRIM(STR(vseat,2,0))+CHR(9)+DTOC(refunddate)+CHR(9)+ALLTRIM(pmttype)+CHR(9)+ALLTRIM(STR(amt,12,2))+CHR(9)+ALLTRIM(STR(opcode,4,0)))
ox.SELECTION.typeparagraph
vrestot = vrestot + 1
vamttot = vamttot + amt
SKIP
ENDDO
ox.SELECTION.typeparagraph
ox.SELECTION.typetext(REPLICATE("=",94))
ox.SELECTION.typeparagraph
ox.SELECTION.typetext("Total # Tickets Refunded: "+ALLTRIMSTR(vrestot,12,0))+CHR(9)+CHR(9)+CHR(9)+CHR(9)+CHR(9)+"Total Amount Refunded:"+CHR(9)+"$ "+ALLTRIM(STR(vamttot,12,2)))
ox.SELECTION.typeparagraph
ox.SELECTION.typeparagraph
ox.activedocument.SAVEAS(vspf)
ox.activedocument.CLOSE
ox.QUIT
RELEASE ox
WAIT WIND "Done! " NOWAIT

Jim

 

[baltman]

Howard,

Take a look at the several VFP Posts and FAQs regarding Word (and Office) automation in forum184 and forum1251, including faq1251-5150 Useful Word Automation examples and thread184-24509 Help Running Word from VFP.

I suggest you post any other questions you have in forum1251 (VFP- Automation), especially since you are more comfortable with VFP than VBA... but you may discover they are not as different as you might think.

Brian

 

[torturedmind]

i've done this a long time ago. i use vfp6 on win98se platform. here's how you should do it:

1. within vfp, copy the selected data to a designated folder or use a select - sql statement. eg.

COPY TO [C:\MY DOCUMENTS\DATSEL.DBF] FOR MyData.MyKey = YourChoice

or

SELECT * FROM MyData WHERE MyData.MyKey = YourChoice INTO TABLE [C:\MY DOCUMENTS\DATSEL.DBF]

2. when data is successfully copied, create/open a new/existing word document (i use msword 2000) and from there, click >tools>mail merge...>create> form letters...>active window.

3. click >get data>open data source..., click >files of type and choose MS Foxpro files then locate the dbf you copied earlier. you will then be asked to confirm the ODBC type to use. select Foxpro files - Word via ODBC (*.dbf). do take note that this will not work if you do not have the proper ODBC driver installed. please go to microsoft's site for the proper ODBC driver.

4. when you can see the edit main document button, you are now ready to populate your word document with your vfp data by using the insert merge field button.

all these can also be done using vfp code by using API routines or by vba. if you need help on this, please post your problem in the proper forum instead.

i sure hope this will help. peace!

kilroy
philippines

"Illegitimis non carborundum!"