Question Locked

FIlling out word document from VB6, but its really slow!

0 helpful
17 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

So I am tasked with filling out a legal document with data from our database. Roughly 650 fields. The word document is a protected document with form fields.

I figure I can fill it this way with a series of these commands.

oDocument.FormFields.Item(i).Result = "Field Data Goes Here"

However, this is painfully slow, about 5 fields a second. Is there any way I can speed this up, or a different way to fill them out?

17 replies

by tony hopkinson

you'd have to define them in the document though.

If i was guessing where the slow down is it would be having to count through the doc to find the item each time you write to it.

If there are local item collections by page or region etc.

Another thing to look at would be some sort of mail merge type manoeuvre

by slayer_

If I just open the document manually, I think its the totaling fields that are causing a slowdown. Maybe its possible to turn those off temporarily...

And I did try the bookmarks, most had values of text1, text2, etc. So I just iterated through the first 20 as a test, same slowness.

I calculated it takes about 2 minutes to fill out the 648 fields on the document.

Mail merge... have to google that.
Edit-
I did a brief google, is mail merge reliant on database access, because sadly that is not an option. The database would likely not have the most current data (Unsaved data) so it would still be in memory. I don't want to force the user to save their information before this will work.

by tony hopkinson

save to a temporary file, merge and go might do it.
I suspect it won't be much faster after your other comments though, might have to start thinking sideways on this one.

Perhaps have the form rejigged into a number of tables, and then insert them en bloc as it were.

Equally instead of setting fields you might be able to add them instead, you'll need some meta data, but it should be shed loads quicker.

by slayer_

How do you know where to insert the fields using a program? I guess you could use exact coordinates, but if a table size is increased, the whole document would shift. (I have not investigated other ways to do this)

Having the fields pre-placed for me is handy for this issue, and likely any revision to this document will have the same fields, so it would be a minimal, if any, rework to the code, most likely case would be I would have to change index values.

I was able to replace the insert fields with text as it runs through, which gradually speeds it up, but my ending product has to be a protected document with data entry fields.

I also thought maybe I can just, at the start of my run, remove the SUM from the offending fields, then manually insert the totals and when I am done, put the SUM back. I am not sure how to do this yet but we already have a similar interface into word that lets you drop a date formula (like Crystal reports, but for end users) and when you do, it asks and places that format on the document, ready for merging (like a fake mail merge, mostly done with Find & Replace).
I think I will try this when I get back to work on Monday.

Still, if you have other suggestions... Wish I could forward you the document to try for yourself.

by tony hopkinson

I looked into this sort of thing as a document based solution a couple of years back, discarded it, slow, flakey and near unmaintainable.

One of the things I did to try and make it work, was add a bookmark where I wanted the table, built it and chucked it in there, no different to setting some txt in that respect.

by slayer_

I proposed what I had and it was accepted but they still want it faster. It will save the user roughly 2 hours of work, BUT all things computerized must be instant or the users complain it is to slow. The time saved by doing this is a big plus vs the time spent waiting for it to happen. And a user doesn't need to sit and wait for it, that's what a coffee break is for. And since this method works if the document is in the background, the user can continue with other work.
It's really all in how the sales people and trainers sell it.

by tony hopkinson

Don't deserve all of them, but then I don't get all I deserve. :(
Two hours, lots of scope for optimisation there and always the nice fallback of something that works.

by slayer_

The darn thing has a combo box in it, how can I set it's value? And not a form field CBO, a VB Macro based CBO. So I need to process it's click event and everything.

I don't see an options in the "worksheet" object for accessing controls on an excel spreedsheet. Looking through the "Cells" object is sort of funny, but I see nothing useful to do this. An Excel.Controls() would be handy... Any thoughts?

by slayer_

Though I just had an idea, what if I tried to take the return value of the Sheet.Cell() call and assigned it to a blank combobox control, changed its value, and assign it back, maybe that would work.

by slayer_

Word is a POS.

I wrote this to cover it, makes it almost instant.

On Error Resume Next
oDocument.FormFields.Item(iIndex).Range.Fields(1).Result.Text = sText
If Err.Number <> 0 Then
Err.Clear
On Error GoTo 0
oDocument.FormFields.Item(iIndex).Result = sText
End If

The first longer one is a version that runs instant and has no length restrictions, but it randomly errors on some fields, so for those fields, I have it revert to the old way which was slower and has 256 character limit, but works.

Go figure, more object calls fixed the problem.

by mafergus

Yes word is a major POS. It's too bad you couldn't use a repoting package like crystal or active reports to generate it on the fly.

by slayer_

This document has a high chance of changing, and we needed ability for user to change and add data as needed. This process just prefills known fields into the document to save the user some work.

by mafergus

does that changed data get back into the database?

by slayer_

I have a different one using Excel that does pull information back in, but this particular document for CUDGC does not.

This discussion is closed to new replies.