pass data from foxpro to word document. 2

[Akourou]

hello,

I want to pass some data from fox pro to a word document.
I already have the sample word document and i want to be able to recreate it from foxpro replacing names and dates that exist in the word document.

is there somewhere on the net a tutorial for that.
I would also like to know what i have to do in the word document to enable it to accept the data.

thank you

 

[Mike Lewis]

It's always difficult to answer this kind of question without knowing what you already know. For example, are you familiar with Automation (note the captial A)? Do you know how to use CREATEBOJECT()? Do you undersand what an object model is?

If the above is all unfamiliar to you, you might do better to search for information on those topics (in a Visual Foxpro context, of course). Then come back here when you have a specific question.

A good place to start might be the VFP Help topics, "Working with Automation Servers" and "Automation Servers in Visual FoxPro". You could also look at some of the sample code that comes with VFP. See the Help topic, "Automation Solution Samples" for details.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[GriffMG]

I was thinking, does he mean a simple find and replace - or is he thinking about bookmarks with a begin/end and replacing the text in between?

Shows you what a nice ambiguous question can do on a Thursday evening...

B-)

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are !good for you. ​

 

[Dan Freeman]

Yeah, this is a very ambiguous question that MIGHT be answered by setting up a simple mailmerge in Word.

More feedback is needed for anyone to be of any help at all.

 

[Akourou]

There are some tables with guests and reservation info.
A voucher must be produced from this data.
The voucher is always the same, only guest name and reservation dates change.
The voucher must be issued once when the reservation in confirmed.
That is the case.
It cannot be solved with mail merge.

regards
Andreas

 

[Olaf Doschke]

It could be solved with mail merge, it might just be over the top, even if you generate the vouchers for all reservations of the day, but mail merge is of course capable to fill in placeholders and generate multiple documents, that's its main intent.

Instead of giving you code, here's the most general hint of what to do when doing something with word: Do it with word while recording a macro of it, then you can port that recorded VBA code to VFP with the following outset:

Local loWord
loWord = CreateObject("Word.Application")
loDocument = loWord.Documents.Open("C:\WordTemplates\Voucher.dotx")
* apply macro code here
loDocument.SaveAs("C:\Vouchers\xyz.pdf",17)
loWord.Quit()
Release loWord

You may even record opening the document and saving it as whatever filetype you want and get the necessary fileformat constant (here using 17 for PDF), at least the constant name you then may lookup here:

https://docs.microsoft.com/en-US/office/vba/api/word.wdsaveformat.

Now, just don't set the text cursor at the place to write to and manually write something, that macro recording would be quite useless, record a search&replace.

Let me make the assumption you have a doc file where name and date were filled in manually and you have ________________ within a text for that, then perhaps rather replace that with something unique like $customername$, $bookingdatebegin$, $bookingdateend$, so a search and replace can find these unique placeholders and not replace anything else in the text. Advantage: If you put in such placeholders mutliple times it's still just one search&replace per data field you want to fill in.

Obviously you need to:
1. Replace the filename of the template Voucher.dotx, this may also be a docx or dot or doc, doesn't matter.
2. Replace folders of templates and voucher output. Never just put in pure file names, Word is a separate process, it doesn't act on VFPs or your applications working directory or DEFAULT/CD path.
3. Adapt the Makro code a little. The VFP loWord object is the Application object in VBA code and loDocument is ActiveDocument. Perhaps post the code you end up and we may help with the translation.

Overall, if you do that in a loop over your data with SCAN...ENDSCAN you've done nothing less than programmed a specific mailmerge with own means, this is mainly what mail merge does, load data, for each row do a series of placement of data fields into the mail merge template and save that.

Otherwise, you have been pointed to the automation help chapter, there are whole books on the subject from Hentzenwerke, too.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[Mike Lewis]

Just to widen this discussion ....

Is there any reason that you must do this in Word? Can you not do it as a VFP report? That would be much simpler.

If you need to send the document in electronic format to your guests or clients, you could do a VFP report and then output it as a PDF. In any case, distributing a PDF is preferable to a Word document, for a number of reasons.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Olaf Doschke]

Presumable, because the Word template already exists.

If this is a feature starting from scratch I'd also consider a report solution, as a report is the straight forward way to put data on paper or into PDFs.

But if a word template exists... Today the word 2007 first introducing SaveAs in PDF format is over 10 years old, so I'm pretty sure this output option is available with Word automation, too (as in my sample code fileformat 17). With less need for recreating that voucher as a report. A voucher sounds like a single page, maybe even less, of course, that's also doable with a report including text and images, you could do TextMerge() to put in name and date or anything else in a text portion even before starting to print, but why change from the existing template? I'd consider that if no word is installed, but that would raise the question, how there is a word template.

Besides, a Word template also offers a much easier way to change the template text, graphic, any "static" portion. While you can MODIFY REPORT at runtime and allow customers to make such changes, it's easier to do that in Word, isn't it?

Bye, Olaf.



Olaf Doschke Software Engineering

https://www.doschke.name

 

[Akourou]

yes, the word template exists and i am not sure that i can have the same rich options for formatting the document from VFP report as i have from word.

 

[AndrewMozley]

For interfacing with Microsoft Office (Word, Excel, Outlook), I found this book very helpful :

‘Microsoft Office Automation with Visual Foxpro’, by Tamar Granor and Della Martin.

It is quite venerable (published in 2000) but it is very helpful and has a lot of worked examples.

Best wishes. Andrew

 

[GriffMG]

The components of your template that you wish to replace with information derived from your application - in what form are they?

Are you using some kind of escape sequence to locate them such as %%MyFieldHere%% or book marks to indicate a start and end for a section of narrative perhaps?

The former is god for 'one-off' find and replaces, the latter is good if you need to repeat the replacement (as the markers are not sacrificed by the replacement).



Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are !good for you. ​

 

[Steve-vfp9user]

I was given some advice by Mike Lewis and Olaf sometime ago about Word Automation (and by the way, I bought the book mentioned by Andrew "Microsoft Office Automation with Visual Foxpro by Tamar Granor and Della Martin" albeit second hand but still a great book)

I had a Word Document template that we wanted to use each time we created a quotation so maybe this will help you in your quest.

* I have changed some of the coding
* in an effort to make it more understandable

* mserverpath is self explanatory
* mqlonquote is the quote reference e.g. 1234SW taken from a field in the table

* First off, copy the template to a new doc

COPY FILE ALLTRIM(mserverpath)+"\longquotes\default\default.doc" ;
  TO ALLTRIM(mserverpath)+"\longquotes\"+ ;
  ALLTRIM(mqlongquote)+"\"+mqlongquote+'.doc'

LOCAL oDocument, oRange, oRange1
oWord = Createobject("Word.Application")
oDocument = oWord.Documents.Open(ALLTRIM(mserverpath)+ ;
  "\longquotes\"+ALLTRIM(mqlongquote)+"\"+mqlongquote+'.doc')

* mrecno is the record number

USE MYTABLE SHARED
GO mrecno

oRange = oDocument.Range()
oRange.Font.Name = "Arial"
oRange.Font.Size = 11

oRange.InsertAfter("Quote Ref: " + CHR(9) + mqlongquote)
oRange.InsertParagraphAfter()
oRange.InsertAfter("Date: " + CHR(9) + CHR(9)+ DTOC(mquotedate))
oRange.InsertParagraphAfter()
oRange.InsertAfter("Client: " + CHR(9) + CHR(9)+ PROPER(INVNAMET))
oRange.InsertParagraphAfter()
oRange.InsertAfter("Address: " + CHR(9) + ALLTRIM(INVCOMPN))
oRange.InsertParagraphAfter()
oRange.InsertAfter(CHR(9) + CHR(9)+ PROPER(INVADD01))
oRange.InsertParagraphAfter()
oRange.InsertAfter(CHR(9) + CHR(9)+ ALLTRIM(PROPER(INVADD02))+ ;
  " "+ALLTRIM(PROPER(INVADD03)))

* More fields here....

oWord.ActiveDocument.Save()
oWord.ActiveDocument.Close()

The above code creates a copy of the default.doc word file, added the relevant fields to it, saves a new documents then closes it.

Additional coding opens the document if required.

DECLARE INTEGER ShellExecute IN shell32.dll ;
  INTEGER hhdWin, ;
  STRING cAction, ;
  STRING cFileName, ;
  STRING cParams, ;
  STRING cDir, ;
  INTEGER nShowWin

lcFileName = mqlongquote+'.doc'

lcPath = ALLTRIM(mserverpath)+"\longquotes\"+ALLTRIM(mqlongquote)+"\"

ShellExecute(0, "open", lcFilename, "", lcPath, 1)

Some of the above has been gleaned from examples, research, advice from valued members in this forum and that amazing book!

Hope this helps

Thank you

Steve Williams
VFP9, SP2, Windows 10

 

[Olaf Doschke]

Griff has a good point about placeholders vs. bookmarks. Especially in Excel, a bookmark can also be a range and you might put in a method call as the bookmark name, VFPs macro substitution or eval() could be used that way, very strong püossbilities. Also in the aspect of using it multiple times, that means open the template once, apply data merge on bookmarks, save, redo with next row of data, because bookmark names don't change, whereas replacing placeholders you'd need to undo changes to go back to the original template and do the next search&replace with other data.

Again, I'd let this depend on what you have already. Bookmark names are not visible and less easy to maintain without a bit of office knowledge, placeholders can be very obvious. On the other hand, it's less likely a modification changes bookmarks unless the section with a bookmark is completely removed. In any case, the users will need to be educated and have instructions on how to maintain the template, so it continues to work.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[TamarGranor]

Thanks to those who recommended my book. I agree it would be helpful in this case. You can find some excerpts from it at

http://tomorrowssolutionsllc.com/conferencepapers.php.

Just filter on Automation.

Tamar