VFP print Word doc with certain position of data field 2

[Eliott]

Hi there,
I have already forms printed in press-room (instead blank, plain paper) which I need to print out with data field on correct certain position (height from top, left, different font). Size of printed form is classic A4 (297x210 mm). I tried to solve it inside VFP 9 report but it was hard to measure and position more than 80 data fields scattered over form on different vertical and horizontal position - simply: fields wasn't on position where I thought it shall be (I measured form and entered on report each text field) during printing from VFP report. So, I decided to scan printed form and put inside Word doc as a picture and use it as "template" for more easiest positioning for each field. Now, I wish to fill them from VFP but don't know how to reference it; I planned to use Text Box control inside Word 2010, as it can be positioned very precise, but how to reference so many controls..?
Earlier I did similar things with table inside Word, but now not all data fields are positioned always on same doc's height or left position. Is here some kind person taht would help about it? Thank you.

There is no good nor evil, just decisions and consequences.

 

[Olaf Doschke]

If you write epressions in the text fields, that you can fill in via Textmerge(eg you write <<Date()>>" in a field) you can iterate and fill them via:

oWord = CreateObject("Word.Application")
oWord.Documents.Add("d:\templates\formxyztemplate.docx")
[b]For Each loShape in oWord.ActiveDocument.Shapes
   loShape.TextFrame.TextRange.Text = Textmerge(loShape.TextFrame.TextRange.Text)
EndFor[/b]
oWord.ActiveDocument.Save(...)

That's all. You might decide for Evaluate instead of Textmerge, but Textmerge includes evaluate in a sense, because you only need to put << and >> around an expression to evaluate it, and textmerge can therefore easily evaluate many expressions at once.

The difficulty in this is having all data at hand while the Textmerge function iterates all Text fields to feed the textmerge with values. Either you go the easy way and simply prepare all needed variables to reference in the Word Textfields via the code you write in there or you change the code to be more intelligent and know what data to get to merge into the textfields text, eg if <<Firstname>> is within the txtfield you'll first fetch the firstname into a variable or position on the correct record of your data and then execute the textmerge.

Anyway, this is a basic idea, but something working as is with as many fields and no matter how they change, provided you have the data at hand to fulfill the expressions written in the Word document template. The expressions might of course also be function calls, that actively pull the data in, eg <<GetFirstName()>>, then your application of course needs to provide these functions for textmerge to work.

Add error handling in case something goes wrong, ie someone edited the word document with an invalid expression, and make it some more intelligent and you have some universal Textfield-Fill-In-O-Matic.

Bye, Olaf.

 

[Eliott]

Hi Olaf,
thank you for your fast response. Maybe I wasn't unclear with my question. I need printing on special brochure that's already printed and looks like some kind of money check, just with more places where I need to put proper field data (name, lastname, amount...) but they aren't in same level (height from top and left distance) so I tried solve it by using table (1 row, 2 columns, or 1 row, 1 column) as I know how to fill them from VFP by using Word Automation, but positioning so many tables over image onto proper places was big pain, simply tables refused to sit down where I need them. So, due that I decided to use Text Box control, like nice and easy movable controls but I don't know what is syntax to put there wanted contents.

oword = CREATEOBJECT("Word.Application")
odocument = GETOBJECT("mytemp.dot")
oword = GETOBJECT(, "Word.Application")
oword.VISIBLE = .T. && Show MS Word
oword.ACTIVATE() && Bring it to the front
'
WITH oword.activedocument
	WITH .TABLES[1]
		.cell[1,1].RANGE.insertafter(jjj)
		.cell[1,2].RANGE.insertafter(kkk)
		.cell[2,1].RANGE.insertafter("good")
		.cell[2,2].RANGE.insertafter("bad")
...
	ENDWITH
ENDWITH
'
*oword.activedocument.shapes("Text Box 1").Textframe.Characters.Text="good" && I tried this way but nop
' Then I tried this way, but again no result
*oword.activeDocument.Shapes("Text Box 1").Select
*Selection.TypeText Text:="good"
*Finally tried this, but again no score:
oword.activeDocument.Shapes[0].Text="good"
...

Is there some way to put a text inside Text Boxes like I did with table (first table is Tables[1] etc...? Thanks.

There is no good nor evil, just decisions and consequences.

 

[Olaf Doschke]

Well, your idea was to use text fields, and my code will fill text fields by evalluating the text you put in there.
For a short demo take a new word doc, put in a text field, write <<Date()>> in it and run it through the loop, you end up with the current date showing in the text field after processing.

Bye, Olaf.

 

[Eliott]

OK Olaf, I'll give a try to your solution.
Thank you again.

There is no good nor evil, just decisions and consequences.

 

[Olaf Doschke]

Let me know, if you don' get it up and running.

Bye, Olaf.

 

[Mike Lewis]

Eliott,

You said:

"I tried to solve it inside VFP 9 report but it was hard to measure and position more than 80 data fields scattered over form on different vertical and horizontal position".

I would have thought it would be easier to solve that problem in VFP than to do it in Word. In VFP, every report field has a horizontal and vertical position. You know those positions on the paper form. You should be able to measure each one, and enter it in the relevant field properties.

I appreciate that there are more than 80 fields. So it will be very time-consuming and error-prone to do it all by hand. So how about automating it? Create a report with all the fields in place, without worrying about their positions. Then create a text file or a DBF which contains the actual horizontal and vertical position for each field.

Next, write a program which opens the FRX file as a table, scans the fields within it, and replaces the Vpos and Hpos fields with the actual values from your text file or DBF.

You can also take the same approach with the fields' heights and widths. In fact, with a bit of effort, you could probably create the entire report programmatically, although that would obviously be more difficult.

Just something to consider.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Olaf Doschke]

The real advantage of doing it in Word with the scanned in form as background is, you don't depend on the paper feed in exactly, the word text fields can be aligned to the graphic you print 1:1 and you see it before you print. If you have the positions for each field, you may also set position of the shape objects on the go. You'd only need to do this once for a dotx, though.

Bye, Olaf.

 

[Dan Freeman]

Yeah, but one of the downsides of using Word for this kind of layout is everything is approximate. Word accomplishes layout by querying the printer driver and if you change printers things can move around on you.

For pre-printed forms I generally prefer putting a scanned copy of the form on a report. After I've placed all the data fields I delete the image object (or set Print When = .f.).

 

[Olaf Doschke]

Well, dan

>I generally prefer putting a scanned copy of the form on a report.
From what I read this is what Eliot did within Word, and actually I see the moving around problem with both Word and FRX reports, if you don't print the scanned form plus data on empty sheets but need to print out on the preprinted forms. This kind of printing only worked for me last time with endless paper having sprocket hole edges for guiding them, so the only position you'd need to car for is the horizontal position.

Abyway, if you put the scanned forma as report background you can position your report fields as you could position your text fields in word. It's up to Elliot to decide for what he likes best. This kind of forms are not a thing for a typical VFP report though, if you ask me, because there is no detail band printed multiple times with a report controlling table or cursor, there is a single set of data filled into the whole form, a single record. It might even be more complex and such things I won't print with an frx. Elliot may clarify that, and make the best choice for his needs.

Bye, Olaf.

 

[Mike Lewis]

Dan, that's a good point about Word querying the printer driver. In addition, with a laser printer, the actual position of the print image on the page will vary slightly with different makes and models. This is because of the "unprintable area" at one or more edges of the page. So there is always the chance of the entire printed text being misaligned with the form.

Of course, this is regardless of what software does the actual printing.

If Eliott decides to adopt my suggestion, the solution would be to add a fudge factor to each Vpos and Hpos. Initially set that to zero. If the text doesn't line up correctly within the boxes, try adding or subracting a small amount to the fudge factor until it does. You only need to do this once for each make and model of printer. And it should be possible to give the end user a simple interface (perhaps with a pair of sliders) to control the process.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Olaf Doschke]

Mike, I do this with label sheets, where the positioning obviously also matters.

But in this case the problem of that positioning problem is already solved by having a scan of the form, so Elliott can print form+data on blank paper. It won't matter, if a make or model prints a few points off position, as both form and data will shift the same amount.

Elliott, please clarify, if I am indeed the only one here, who got that wrong.

Bye, Olaf.

 

[Dan Freeman]

One other comment ....

If you follow any Word support forum where the MVPs hang out, they'll try to talk you out of doing this job in Word. There are specialized form-filling software packages that make the task much easier because Word simply wasn't built to put text at fixed locations on a piece of paper.

 

[Olaf Doschke]

I don't get it, Dan. Actually no software can assure exact printing at certain paper positions, so the best thing to do is print form and data in one, eg have a pdf form you could fill with amyuni eg would be ideal. But printing the form+data within the same document, no matter if frx or word, you don't depend on paper feeding and printer exactness at all, both things will shift the same way.

Bye, Olaf.

 

[Dan Freeman]

Word wasn't designed to put specific bits of information in a specific spot on a page. <shrug>

It was designed to flow content across pages, and it's pretty good at that. Good enough, in fact, many publishers have dropped use of traditional desktop publishing apps for all but the most complex of books and just use Word to produce final layout.

Over the years, MS have (clumsily, IMO) added baffling new features to attempt meeting users' need to specifically place things. It's a grab-bag of features, none really complete or pleasing. When something is difficult to do in an application you might be using the wrong application. (And I say the same about VFP. <g>)

The only time I usually will use Word for pre-printed forms is when I'm creating an actual "form" (form fields that the user types in). (Again, that's the usual recommendation of the Word MVP's -- a very active and helpful community to tap into.)

 

[Olaf Doschke]

>The only time I usually will use Word for pre-printed forms is when I'm creating an actual "form"

Well, and while putting a scan of a preprinted form in the backround of a word doc is not exactly that, it has the same effect: The form and data is in the same document and you can print it without the fear of mispositioning.

Eliott wrote: "So, I decided to scan printed form and put inside Word doc as a picture"

He did say that in the initial post. And I still try to get this through to you.

It may not be the way to go, if you're supposed to print on the preprinted forms only, and a reproduction is not allowed, but it will work this way, no matter if with Word or if he embed the scan as background of an FRX. And of course this will not be printed on the preprinted forms, as this would reprint the preprint.

Bye, Olaf.

 

[Eliott]

Dear friends,
in meantime I tried to solve described problem. Shortly, it's a small app for printing form with student's grades. I scanned form and put in the CorelDraw, just to check are all fields aligned properly, and is there vertical difference among them. Sadly, I saw that not all grade fields aren't on same distance (based on vertical measurement in Corel by simply copy first field 15x) and due that I decided to use scanned copy as some kind of template. I tried to solve it by classic VFP report and on the way danfreeman described, but once made app doesn't allow to user additional set up and correction of printed data horizontally or vertically. Beside this, by using Word with Mail Merge feature (with Text Box as container and precise positioning objects) and Excel as data application I avoided installation process of VFP app, and in same time I provided to user manual editing of fields. If I could find out some VBA about this theme I would try to solve it also by little help of VBA for office. Yes, it's not so "automatic" or "smart" but it had more option to edit than by using VFP report. Sure, I tried to make an app where a user is able to see in the form printed template but wasn't able to solve problem with on screen (form) mimic report design and later their re-positions... so I left on Word solution. I wish I could make some app that would with behavior like some kind of report designer and with runtime position modifications ability but I'm not so smart and was short with time.
The solution Mike proposed also was interesting but inside report structure is more than 50 printing fields, and I stuck to find out which one is wrong positioned, and how to translate millimeters into dots and vice versa. If you have some better idea, I'm ready to try.
Again, thank you for your efforts to help me.

There is no good nor evil, just decisions and consequences.

 

[Mike Lewis]

The solution Mike proposed also was interesting but inside report structure is more than 50 printing fields, and I stuck to find out which one is wrong positioned, and how to translate millimeters into dots and vice versa.

First, you don't need to convert millimerers to dots. Within the FRX, the Hpos, Vpos and Width fields are all stored in units of one ten-thousandth of an inch. Since one inch is 25.4 mm, the conversion is a simple multiplication. (By the way, this is not affected by the choice of inches or centimetres within the report designer.)

Also, I wasn't suggesting that you need to find one field that is badly positioned. My idea was to take the original A4 form, place a ruler across it, mark of the left, top and width of each box (in millimetres), and write the name of the field in each box. Then, enter all that information into a spreadsheet, a DBF, a text file or whatever: enter the field name, left, top, width. Then write a program that reads that information and uses it to update the FRX. The program will use the field name to locate the relevant FRX record, and then overwrite the Hpos, VPos and Width fields in that record with the figures you have recorded (converting to units of one ten-thousandth of an inch as it does so).

I agree that it will be laborious and time-consuming. Whether you would want to do it depends in part on your personal preferences - and temperament. Personally, I would prefer to spend a couple of hours doing what I have described, rather than any of the other solutions suggested. But I wouldn't blame you if you couldn't face doing it.

EDIT: I originally said that the units were in thousandths of an inch. It should have been ten-thousandths of an inch. I have now corrected this.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Eliott]

Hi Mike,
I think I'll spend today afternoon to measure all fields and note all dimensions as you described in lest post of this thread. It seems promising, and in same time I'll push my "simply" solution for those who doesn't like installation anything on their computers, except recent Office package. So, later we'll compare result of both solution and make conclusion.
Thank you, thanks to kind Olaf and to Dan for constructive opinions and suggestion. I wish that instead Windows 10 I see on market VFP 10 with native support for MySQL or MS SQL, but it's not up to me
Thank you again, you're the best cyber crew.

There is no good nor evil, just decisions and consequences.

 

[Olaf Doschke]

It would be nice to see a comparison, especially since it's quite simple to put some DLLs to your EXE to make it work, also without any setup.
I don't think I would like to note positions of fields, if I have a scan and can simply visually drag fields to the place in the scan, but as Mike formulated it, that's personal preferences and temperament.

Bye, Olaf.