Filling in forms automatically

[Karen]

I have to complete work orders that are sent to me. These work orders include
tables with a number of columns in which to enter a product code and
description.

Is there any way of setting up autotext or a macro so that when I type in
the product code in the Product ID column, the product code's description is
automatically entered into the Description column (there are hundreds of
product codes)?

The forms have been created using Word 2003

 

[Greg Maxey]

You could set up formfield "productCode" and "productDesc" and run a macro
on exit from the "productCode" field. Something like this:

Sub PCodeOnExit()
Select Case ActiveDocument.FormFields("productCode").Result
Case "1234"
ActiveDocument.FormFields("productDesc").Result = "Describe item 1234
here"
Case "9876"
ActiveDocument.FormFields("productDesc").Result = "Describe item 9876
here"
Case ""
ActiveDocument.FormFields("productDesc").Result = ""
ActiveDocument.FormFields("productCode").Result = ""
Case Else
MsgBox "The product code you entered is invalid"
ActiveDocument.FormFields("productDesc").Result = ""
ActiveDocument.FormFields("productCode").Result = ""
End Select
End Sub

 

[Karen]

Thanks for the swift response. There are quite a lot of product codes (over
100) so will this macro still work with that many (will be an awfully liong
piece of code).

Also, would I have to write this macro in every work order I receive or can
I copy it somehow from one form to another (I don't create the forms, I just
complete them)?

 

[Doug Robbins - Word MVP]

Obviously, your process needs automation, but it is a bit difficult for
someone who is in a position part way through the process.

Depending upon the arrangement of the work order form, it might be possible
for you to have an application that you run when one of the work orders is
the active document that would extract the data from the work order and use
it to populate controls on a userform and also have on that form a combobox
or list box that is loaded with the product codes and their descriptions
from an external source that makes it easy to maintain the content.

See the article "How to create a Userform" at:

http://word.mvps.org/FAQs/Userforms/CreateAUserForm.htm

and the following pages of fellow MVP Greg Maxey's website :

http://gregmaxey.mvps.org/Create_and_employ_a_UserForm.htm

http://gregmaxey.mvps.org/Populate_UserForm_ListBox.htm

That is just one of the ways that could be used to automate the process, but
I realise that you might be the tail that is wagging the dog.

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP, originally posted via msnews.microsoft.com

 

[Graham Mayor]

If you were to have your product codes and their associated descriptions in
a two column table (codes on the left, descriptions on the right) saved as a
document you could use a variation on one of Doug's old macros to insert the
appropriate description in your document.
With the cursor in the cell of your work order table that has the product
number, the macro below (run from a toolbar button) will insert the
description from the table document into the cell to the right of the one
containing the product number.

In the example the two column table document is saved as "D:\My
Documents\Word Documents\Code Table.docx" but you can change that to any
valid path (and it will work equally well with Word97-2003 DOC format).


Sub InsertDescr()
Dim ChangeDoc As Document, RefDoc As Document
Dim cTable As Table
Dim pCode As Range, pDesc As Range
Dim i As Long
Dim sFname As String
sFname = "D:\My Documents\Word Documents\Code Table.docx"
Set RefDoc = ActiveDocument
Set ChangeDoc = Documents.Open(sFname)
Set cTable = ChangeDoc.Tables(1)
RefDoc.Activate
For i = 1 To cTable.Rows.Count
Set pCode = cTable.Cell(i, 1).Range
pCode.End = pCode.End - 1
Set pDesc = cTable.Cell(i, 2).Range
pDesc.End = pDesc.End - 1
With Selection.Cells(1).Range.Find
.ClearFormatting
.Replacement.ClearFormatting
While .Execute(findText:=pCode)
Selection.MoveRight wdCell, 1
Selection.TypeText pDesc
GoTo Found
Wend
End With
Next i
Found:
ChangeDoc.Close wdDoNotSaveChanges
End Sub

http://www.gmayor.com/installing_macro.htm

--
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor - Word MVP

My web site www.gmayor.com

<>>< ><<> ><<> <>>< ><<> <>>< <>><<>

 

[Greg Maxey]

Here is an adaptation of Graham's code that may be easier to implement.
Create you reference document. Two column table with product code on the
right and description on the left (named C:\Reference.doc) in example below.

In the form document, assuming product code is the adjacent cell to product
description, type in the product code then run the macro:

Sub ExtractDescr()
Dim oSourceDoc As Document, oFormDoc As Document
Dim oTbl As Table
Dim pStr As String
Dim oCell As Cell
Dim oRng As Word.Range
Dim pFileName As String
pFileName = "C:\Reference.doc"
Set oFormDoc = ActiveDocument
Set oCell = Selection.Cells(1)
pStr = Left(oCell.Range.Text, Len(oCell.Range.Text) - 2)
Set oSourceDoc = Documents.Open(FileName:=pFileName, Visible:=False)
Set oTbl = oSourceDoc.Tables(1)
Set oRng = oTbl.Range
With oRng.Find
.Text = pStr
If .Execute Then
oCell.Next.Range.Text = Left(oRng.Cells(1).Next.Range.Text, _
Len(oRng.Cells(1).Next.Range.Text) - 2)
End If
End With
oSourceDoc.Close wdDoNotSaveChanges
End Sub

Basically the code finds the product code in the reference document and
insertst the adjacent description adjacent to the product code in the form
document.

 

[Karen]

Thanks everyone. That little macro worked (hooray).