PDA

View Full Version : Solved: How to Store Text for Building Documents



village_alchemist
08-12-2004, 09:03 AM
Hey all! :hi:

Looking for some recommendations from the Guru's here! I've been tasked with automating the creation of a document from selections/responses on a form. I've created the form in MS Word 2000 and it is complete and working. Now all I have to do is create the document based on the responses. I've got a question about how/where to store the formatted paragraphs that will be used in the final document.

1) Put all possible paragraphs in the template and delete what I don't need
2) Put the text in the VBA code somewhere
3) Keep the text in a separate document and paste in as needed
4) a better way??? :dunno

This document creator will need to be used by a number of people accross the country and they won't necessarily be logged into the company network, so I'm trying to keep the number of files that have to be distributed down. On the other hand, changes will need to be made to the text occasionally so ease of modifying text (by a non-programmer) is also a consideration.

The final document can't contain any macros since it will be sent to other people outside of the company.

Any suggestions? :help

Anne Troy
08-12-2004, 02:07 PM
Could you describe your final document more? What does it contain besides the data? Other stuff?

To begin without further help, you might want to look into INCLUDETEXT in help or on the web.

And, we have a macro that strips a file of all VBA, so you can use VBA, then strip before sending? Maybe?
http://www.vbaexpress.com/kb/getarticle.php?kb_id=64

But you should be able to get more help right here if you can describe that final doc a little better.

village_alchemist
08-12-2004, 05:50 PM
I'm not sure that includetext will help unless I change the filename or bookmark in the field constantly. I also don't know how many text "snipits" I'll need to add until after the user has completed the form. Hopefully I'll explain better below.

I saw that VBA stripper macro, that's cool! I could either use that or create a completely new document using the normal template.

So, more info on the actual document itself. The form prompts the user for some background information such as company, software, etc. Then on the second page of the form there are about 20 checkboxes for standard features that could be added to the customer's software. The user could pick any number of them, from 1 to all. Then the user will press the "Create Doc" command button and *poof*, the document should appear.

The document is set up with a background section where some of the text the user entered is placed.

Then there is a details section. This is where separate paragraphs are placed that describe each feature that the user selected on the form.

There is a customer responsibility section where some text may be placed. For instance, if we are adding another workstation to the system and the customer is supplying the hardware, we have a standard hardware configuration that is normally pasted in.

There are other sections that are similar, but the fun one is where I have to calculate the cost. Each checkbox has an associated cost and I need to be able to total up the total cost for all the selected checkboxes and insert the total.

I hope this helps!

Jacob Hilderbrand
08-12-2004, 06:19 PM
You could put the text into a text file and retrieve it as needed. But the users would have to keep the text file on their computer. Maybe a better option would be to declare your text as a constant in VBA then use it throughout as needed. Once the final doc is created, strip out the VBA code. Something like this perhaps:


Option Explicit

Public Const Text1 = "This is Text1"
Public Const Text2 = "This is Text2"
Public Const Text3 = "This is Text3"
Public Const Text4 = "This is Text4"

Sub Test()

Dim Choice As VbMsgBoxResult

Choice = InputBox("What Do You Want? (1-4)")
Select Case Choice
Case Is = 1
MsgBox Text1
Case Is = 2
MsgBox Text2
Case Is = 3
MsgBox Text3
Case Is = 4
MsgBox Text4
Case Else
MsgBox "Invalid selection, input a number from 1 to 4"
End Select

End Sub

Anne Troy
08-12-2004, 06:45 PM
Actually, this is the PERFECT method. Maybe you won't use INCLUDETEXT, but you *can* create a bunch of Word docs. Then, have option buttons, and if ticked, Doc1 is included, then Doc2, Doc 3 is skipped, etc.

I've got some code that does that, but it was for a (HUGE) HVAC company (gee, can you guess their name? It starts with H...), but maybe Jake can alter it to suit your purposes?

village_alchemist
08-12-2004, 07:38 PM
Thanks for the suggestions Dreamboat & DrJ! I've got about a week to get this running, so if I get coderblock I'll be posting again!

Steiner
08-13-2004, 12:42 AM
As I'm looking for such a thing myself, I'm very interested in this thread.
But unfortunately none of these solutions seem fitting:
DRJ: I need to store formatted text
Dreamboat: I need to have all options in 1 document

+ I need the possibility to restart the form and select other passages, so deleting the text won't be an option either.

So far I came up with having all text inside the document and just hiding the unecessary passages.

But I'm not too happy with this solution either, because some documents use print macros that hide/unhide parts of the document, so this collides with the hiding I implemented.

Jacob Hilderbrand
08-13-2004, 01:06 AM
Steiner

Maybe you would be better off to have a userform in Excel run through the choices and have the formatted data stored in a sheet. Then you can copy/paste the specified data into Word.

See here for reference in controlling Word from Excel.

http://www.vbaexpress.com/kb/getarticle.php?kb_id=81

village_alchemist
08-13-2004, 04:11 AM
Steiner,
My text is also formatted with both numbering and bullets. The problem I've run into with just a manual copy and paste (with the formatting in both docs being exactly identical) is that Word has a tendency to format the way *it* wants to, not the way *I* changed it to. So after pasting in the section, I have to go back and remove tab stops that are not in the style. Admittedly, I did change the formatting on some pre-defined styles, so I have to test this on user-defined styles.

I am also concerned about the hidden text, and I can't depend on my users keeping 20 files updated. I'm actually leaning toward having the template contain all of my formatted text and then creating a *new* document to copy the text into. Well, if I can get over this problem of Word re-formatting my text. Otherwise I'll probably go with deleting the sections I don't need.

The Excel idea is interesting though and that would also allow me to associate a cost with each feature.... Hmmmmmmm

Anne Troy
08-13-2004, 07:37 AM
No, no, no. LOL!!

DRJ or even Steiner, I am sure you guys can do this.

Make one main Word doc. It has a TOC, and all that.
Make 10 Word docs that contain the various pieces.
All the docs should be created using the same styles.
Analogy: I'm buying a car and there are 10 options. You are going to make me a manual that ONLY covers the options I'm buying.

So, open main doc, userform pops up.
There's a few textboxes to fill in.
There's 10 radio buttons for the options.
I tick 3 of them.
I hit Submit.

Each of the 3 Word docs is inserted into my main document (using Insert-File) at either the end of the main doc, and in the appropriate order, OR they are inserted at bookmarked locations in my main doc.

I'm certain this is what you guys want...

Anne Troy
08-13-2004, 08:10 AM
I found it!!
I'm pretty sure this was created by smozgur.
Just put the file and the folder both on your desktop, then open the doc.


Edit: The password is pumpkin

JOrzech
08-13-2004, 08:16 AM
I use AutoText....

village_alchemist
08-15-2004, 04:59 AM
Wouldn't the use of AutoText require that each user have the AutoText definitions in their local application?

Jacob Hilderbrand
08-15-2004, 05:15 AM
Hey Dreamy, that's a pretty nice file. Is it in the kb yet? :*)

JOrzech
08-15-2004, 05:49 AM
Wouldn't the use of AutoText require that each user have the AutoText definitions in their local application?
I simply store all the AutoText entries in the template itself.

Anne Troy
08-15-2004, 07:18 AM
Jake: Nope. I'm not the coder of that file...and wouldn't know the first thing...
Go ahead!
:)

But!
I'd rather we put TWO methods (even 2 kb entries). One that uses the documents, and one that uses autotext.

JO is right about using autotext and, for the life of me, I can't remember why autotext wouldn't work for the client for which that sample was created. I *think* it's because they had different people in charge of the individual documents and didn't want to have to touch the template every time 'cause it was used too much for such disruptions.

JOrzech
08-15-2004, 08:35 AM
Here's the code I use to insert the AutoText - this is how I assemble many of the templates at my firm:


ActiveDocument.AttachedTemplate.AutoTextEntries("Whatever").Insert _
Where:=Selection.Range, RichText:=True


BTW - you need to have the RichText:=True at the end, otherwise, you lose the styles.


HTH

village_alchemist
08-15-2004, 05:52 PM
I simply store all the AutoText entries in the template itself.
:thumb A big thumbs up to Joanne! And a big D'oh! from me. I started in that direction, but then abandoned it because the autotext showed up in my normal template. Amazing what happens when you use those drop down boxes....

Thanks for the "Quoter" snippit, Dreamboat. It reminded me that I need to password my code. Small world, my wife worked for AlliedSignal in Morristown, NJ when "H" purchased them.

Anne Troy
08-15-2004, 06:04 PM
My brother-in-law works for them now...

JOrzech
08-15-2004, 08:15 PM
Village_Alchemist.... so glad it worked out for you! We often don't see the forest for the trees! Those dang dropwdown boxes can be very confounding :yes

Steiner
08-16-2004, 04:32 AM
@Dreamboat: Hey, how I'm supposed to take this one:


DRJ or even Steiner :bawl

And as I said, I don't like the idea of having lot's of documents hanging around. But Joannes suggestion about the autotext sounds promising, I think I'll go for that one, thanks Joanne :rolleyes: !

Anne Troy
08-16-2004, 08:20 AM
Sorry, Steiner. No insult intended!
I just know that DRJ is really a WHIZ at code!

JOrzech
08-16-2004, 09:52 AM
@ Steiner

You're welcome! Glad to have been of some help....

JOrzech
08-26-2004, 03:29 PM
Anything else?

village_alchemist
08-27-2004, 12:54 PM
Hi Joanne!
No other questions on this thread. I actually used a combination of suggestions from different threads here to come up with the final solution. Everyone here has been very helpful! I'm actually using a combination of Bookmarks, AutoText, and CustomDocumentProperties.

One trick that I used was to be consistent between my form's control names, and the template's Bookmarks, AutoText, and CustomDocumentProperties names. This allowed me to use the control name (or part of it) to access the correct template item. This way as I loop through the controls on my form I don't have to have a separate test condition for each item.

Here is how my final solution works:

For text that the user enters that goes in a fixed position in the document, I used a TextBox for the user to enter the information and then I print it at a bookmark that is defined in the template.

CheckBoxes and OptionButtons add text to the document using AutoText entries that contain pre-formatted lines or paragraphs. These controls also access the CustomDocumentProperties which holds the 20 different values for the 20 different options. Following is a simplified snipit:


For Each ctlInForm In MyForm.Controls

Select Case TypeName(ctlInForm) 'MUCH more reliable than TypeOf function

Case "TextBox" 'value stored in .Text property
Selection.GoTo what:=wdGoToBookmark, Name:=ctlInForm.Name
Selection.TypeText ctlInForm.Text
Case "CheckBox", "OptionButton" 'value stored in .Value property
If ctlInForm.Value Then
Selection.GoTo what:=wdGoToBookmark, Name:=ctlInForm.Name
ActiveDocument.AttachedTemplate.AutoTextEntries _
(ctlInForm.Name).Insert _
where:=Selection.Range, _
RichText:=True

'add the time required for this item
iTotalTime = iTotalTime + _
ActiveDocument.CustomDocumentProperties(ctlInForm.Name)
End If
End Select

Next ctlInForm

JOrzech
08-27-2004, 02:26 PM
Very nice! Glad you got it all pulled together :)

fumei
08-29-2004, 11:31 PM
Could you not store the text as bookmarks (ranges that include the text). That way you could have a drop down (and I think an ActiveX combobox would be better. You could select whatever chucnks are appropriate and go from there. Or am I missing something....woudn't be the first time.

village_alchemist
08-30-2004, 05:32 PM
Each of the 20 or so selections on the form may generate paragraphs in multiple sections of the document. We've had this template set up so the user could just do an "Insert | File" for about 6 months and have had no end of missing text and extra text. Much better (and faster) for the user to click a CheckBox for the option they want and have the template validate the entries to be sure they don't contradict and also place the correct text in the correct positions.

JOrzech
08-30-2004, 05:56 PM
fumei may be correct.... but we all get into our own habits of coding that are comfortable for us. I don't use bookmarks as ranges... I simply add a bookmark and go to that bookmark and add the appropriate autotext entry. I don't know what the construction of your document is, but if it's just straight text I don't know why text boxes would be needed, you may make it simpler by using something like this:


Selection.GoTo what:=wdGoToBookmark, Name:="YourBookmarkName"
ActiveDocument.AttachedTemplate.AutoTextEntries("YourAutoTextEntry").Insert _
Where:=Selection.Range, RichText:=True


Maybe I'm the one missing something? In any event, it's working well for village_alchemist and he's blended a lot of suggestions into one working app, so I'm pleased. I guess there's always a better mousetrap :D

mdmackillop
08-30-2004, 06:07 PM
I note the thread is solved, but here's another approach using Excel to store your data; suitable for very large amounts of text options. Extract the files into C:\Amerge. Use the macrobutton (you may need to toggle this) to open a userform. Select the item in the listbox, double click to place it in the document. It's a Work In (occasional) Progress; suggestions welcome.
MD

JOrzech
08-30-2004, 06:39 PM
That's nice md! I personally think it's easier to check a checkbox on a VBA user form and have the autotext inserted... but it's a really neat little program. I seem to recall seeing something similar at one point for a document assembly program. You may be on to something $! Is there any limit to the descriptive language you can give to the modules that are inserted?

mdmackillop
08-31-2004, 12:09 AM
Hi Joanne,
Only to the extent that the text is displayed in the listbox. I don't think the listbox will wrap and display long descriptions. Happy to be wrong though.
MD

fumei
09-01-2004, 09:17 AM
Joanne's suggestion works fine. However here is what I did for a real situation. I work with documents that MUST have both english and french text. Rather, the user must have a choice of working in french or english.

I had to create a financial form in Word that would generate documents that had a huge bunch of details. The details were different based on whether the user was selecting a Journal Voucher transaction, a Cost Recovery transaction, whether the course that was being asked for was previously paid for out of someone else's budget - in other words lots of multiple choices.

Here is what I did.

ONE document, with multiple sections each one carefully bookmarked.

On open - First user form asked "English or French?".

From that (with screen updating off of course), the result picked out the range of the sections NOT applicable. if french selected, all english sections were removed, and vice versa.

This left the sections specific to the what type of transactions were possible (but only those for the language chosen).

The second user form displayed and the user selected the type of transaction. I did not use a third form. The controls on the form were set for invisible until user selected transaction type. When they do, all the controls appeared (Student name, course cost, supervisiors name, phone number, course start/end date, etc etc etc.) Meanwhile, as the type of transaction is now known, the code removed all the sections (using the bookmark ranges) that were not appropriate. If a JV transaction - the Cost recovery section was removed etc.

User typed in their information, pressed OK, all the information ran through a huge error trapping routine, and if passed, dumped the information into the appropriate formfields, in the appropriate section. Document automatically saved as FinancialRequest_username.

Original document remained intact and quite reuseable. Note this was a document, NOT a template. Therefore it did not require explaining that it had to be copied into a template folder, did not have to be called via File, New. They could just open the file, and everything ran. All code remained in original. All code stripped from the saved as.

Oh, and as further lock, when the document finished, it inserted a final bookmark that had a result "Done". If the saved as file is opened again, the Opening routine checked for the "Done" bookmark. If found, it bypassed the opening language choice and displayed another userform that allowed either editing or creating a new document. If editing was chosen, the document (the saved as) opened for editing, if create a new chosen, the saved as file would open the original (making it active), code in the original would pick up the savedas filename, close it and delete it.

On the original processing I disabled ALL exits, even the little X that closes the userform. The user had to complete the form, although i gave them the choice to exit completely. In which case the original document closed automatically, which no processing or changes.

Sounds more complicated that it was. Original document had 8 sections (four for each language) and always ended up with one - the section for the transaction type in the language of their choice.