Complicated Merge with Comments

[Jason L]

Hey all. I've been working on and off with a client that currently uses Wordperfect for most of their document functions. Well, since they are being asked to convert to Word, I am helping them try to get all their processes converted over. I thought I had resolved one major document process for them, but it turns out it's not working too well at all. Here is the situation.

1) File is created on an AS/400 and downloaded to a certain location on the network. It's given a rather long name like 012312213.234.doc. It's an ASCII file, I believe. Each file contains several records. These records are separated by pipes. These records need to be taken out of this format and saved into seperate files with the name of a particular field within a file.

It looks like something like this:

Field1
Field2
Field3
Field4
etc.

3) My macro grabs the third field while going to the end of record (the pipe) and saving each record in a seperate document named after this third field in each record.

4) The macro also takes each newly created file and inserts a header file at the top - this is for the merge that will later occur.

Here is the code for the first four steps:
Sub BRPTMacro()
' Updated 10/16/2003
' BRPTMacro Macro
' Macro recorded 7/18/2003 by jlogue

'This code opens the directory with the brpt file.
' It then opens the brpt file.

ChangeFileOpenDirectory "C:\wpdoc\conv\"
Documents.Open FileName:="brpt.doc"

'This code goes to the end of the file, deletes any of the garbage at the end.

Selection.EndKey Unit:=wdStory
Selection.TypeBackspace
Selection.TypeBackspace
' This code finds all commas and replaces them with asterisks. This is because I used commas
' as field delimiters.


'This code finds each tilda and replaces it with a line break.
With Selection.Find
.Text = "^w~"
.Replacement.Text = "~"
.Forward = True
.Wrap = wdFindContinue
End With
Selection.Find.Execute Replace:=wdReplaceAll

With Selection.Find
.Text = "~"
.Replacement.Text = "^l"
.Forward = True
.Wrap = wdFindContinue
End With
Selection.Find.Execute Replace:=wdReplaceAll
With Selection.Find
.Text = "|"
.Replacement.Text = ""
.Forward = True
.Wrap = wdFindContinue
End With
Selection.Find.Execute Replace:=wdReplaceAll
'The following code finds the certain code number in the document by looking for
' the sixth line down. It then moves over 12 character units and selects the entire
' number.


Dim fname As String, i As Integer, Source As Document, Target As Document
Set Source = ActiveDocument
For i = 1 To Source.Paragraphs.Count
Set Target = Documents.Add

Target.Range = Source.Paragraphs(i).Range
Selection.Find.Execute Replace:=wdReplaceAll
Selection.HomeKey Unit:=wdStory, Extend:=wdExtend
Selection.MoveDown Unit:=wdLine, Count:=6
Selection.MoveRight Unit:=wdCharacter, Count:=12, Extend:=wdExtend
fname = Selection


'After the files are separated, this code inserts the field files at the beginning
' and ending of each file.

Selection.HomeKey Unit:=wdStory
Selection.InsertFile FileName:="C:\wpdoc\conv\Fields2.doc"
Selection.EndKey Unit:=wdStory
Selection.TypeBackspace
Selection.InsertFile FileName:="C:\wpdoc\conv\Fields3.doc"
Selection.EndKey Unit:=wdStory
Selection.TypeBackspace


'This code saves the file according to the variable above (fname), and it stores it into
' the path below. To change where this file is saved, simply change the path.

Target.SaveAs FileName:="C:\wpdoc\conv\" & fname & ".doc"
Target.Close

Set Target = Nothing

Next i

Set Source = Nothing

ActiveDocument.Close SaveChanges = True




End Sub

5) Each newly created file also has another document inserted at the end called the Comments doc. This is a file containing more fields that have comments attached to each field. These comments are used to instruct the users on what information should be inserted. I made these fields into Macrobutton fields so they would only need to click and enter info.

6) After all the files are separated and saved, a user will typically open the document and enter information in the comment fields. He/she then takes the files and clicks another macro that converts the data into a merge source, opens the merge document and merges the data into the form. If this works successfully, woo hoo! Usually there are a ton of formatting problems and when they insert tables into a field, the table doesn't get merged correctly.

7) Here is my favorite part. After this file is merged a form, all the comments are naturally removed, but this same file needs to be used and added to by countless other people in different departments. People will need to open the files and use the comments to see where to add their information for their own forms.

To tell you the truth, I'm amazed it worked for awhile. The conversion and saving of the seperate files worked really well in my macro, but when users would need to interact with the comments fields, etc., and then use it again as a data source, well, that's where I got into trouble. It just became very complicated for new Word users.

I am open to using a mixture of Access, Excel and Word if anyone thinks it would help. These users are being asked to stop using Wordperfect by the end of the year, so they really need a workable solution. If you search my name, you will see that I've talked a lot about this issue in the past.

Thanks for any help you can give.

-Jason

 

[Peter Jamieson]

If you search my name, you will see that I've talked a lot about this
issue in the past.

OK, I remember quite a lot of stuff about dealing with WP type data files
with variable numbers of fields etc., but nothing about a substantial amount
of user maintenance of the data files and attempts to do user documentation
in the data files.

When you have multiple users maintaining data, you will inevitably end up
with damaged and unusable data unless the users all know exactly how
everything is supposed to work and are sufficiently competent and
disciplined to work to all the correct standards and correct any errors that
occur, or you impose the standards. Frankly, situations where everyone knows
what they are doing and always fix errors are extremely rare at best. So I'd
say you have to impose the standards, and the trouble with using a package
such as Word to store your datais that it is almost impossible to exert
complete control. So I'd say you really have to consider using an approach
that allows that control to be exerted. Traditionally, you'd use a database
package such as Access and do a lot of data analysis to ensure that you were
coping with all the known requirements. Another possibility if your users
are moving to Word 2003 might be to use its new XML facilities to do the
data maintenance. Using those kind of facilities might allow you to mix data
and your instructions/comments in a familiar way while allowing your
application to maintain the necessary level of control over data quality,
integrity etc. However, that approach looks like an awful lot of hard work
to me unless you are really comfortable with XML and XSL transforms, and I
haven't done enough of it to know how bulletproof you can make your
applications..

It sounds as if you're getting into a significant amount of development
effort here and that probably means that no-one is going to be able to give
you "the answers". It's difficult to do more than guess the sort of problems
you might be running into, or the number of Access tables you might need
(for example) without knowing a /lot/ more about exactly what requirements
you are trying to meet. I don't think anyone in these groups is going to be
able to give much more than vague pointers except to fairly specific
questions about how to achieve specific things in the packages you are
considering using. If I were doing this work, I wouldn't even be thinking
about the implementation tools at this point.

Just my 2 cents' worth.

--
Peter Jamieson

Hey all. I've been working on and off with a client that currently uses

Wordperfect for most of their document functions. Well, since they are
being asked to convert to Word, I am helping them try to get all their
processes converted over. I thought I had resolved one major document
process for them, but it turns out it's not working too well at all. Here
is the situation.

1) File is created on an AS/400 and downloaded to a certain location on

the network. It's given a rather long name like 012312213.234.doc. It's an
ASCII file, I believe. Each file contains several records. These records
are separated by pipes. These records need to be taken out of this format
and saved into seperate files with the name of a particular field within a
file.

It looks like something like this:

Field1
Field2
Field3
Field4
etc.

3) My macro grabs the third field while going to the end of record (the

pipe) and saving each record in a seperate document named after this third
field in each record.

4) The macro also takes each newly created file and inserts a header file

at the top - this is for the merge that will later occur.

Here is the code for the first four steps:
Sub BRPTMacro()
' Updated 10/16/2003
' BRPTMacro Macro
' Macro recorded 7/18/2003 by jlogue

'This code opens the directory with the brpt file.
' It then opens the brpt file.

ChangeFileOpenDirectory "C:\wpdoc\conv\"
Documents.Open FileName:="brpt.doc"

'This code goes to the end of the file, deletes any of the garbage at the end.

Selection.EndKey Unit:=wdStory
Selection.TypeBackspace
Selection.TypeBackspace
' This code finds all commas and replaces them with asterisks. This is because I used commas
' as field delimiters.


'This code finds each tilda and replaces it with a line break.
With Selection.Find
.Text = "^w~"
.Replacement.Text = "~"
.Forward = True
.Wrap = wdFindContinue
End With
Selection.Find.Execute Replace:=wdReplaceAll

With Selection.Find
.Text = "~"
.Replacement.Text = "^l"
.Forward = True
.Wrap = wdFindContinue
End With
Selection.Find.Execute Replace:=wdReplaceAll
With Selection.Find
.Text = "|"
.Replacement.Text = ""
.Forward = True
.Wrap = wdFindContinue
End With
Selection.Find.Execute Replace:=wdReplaceAll
'The following code finds the certain code number in the document by looking for
' the sixth line down. It then moves over 12 character units and selects the entire
' number.


Dim fname As String, i As Integer, Source As Document, Target As Document
Set Source = ActiveDocument
For i = 1 To Source.Paragraphs.Count
Set Target = Documents.Add

Target.Range = Source.Paragraphs(i).Range
Selection.Find.Execute Replace:=wdReplaceAll
Selection.HomeKey Unit:=wdStory, Extend:=wdExtend
Selection.MoveDown Unit:=wdLine, Count:=6
Selection.MoveRight Unit:=wdCharacter, Count:=12, Extend:=wdExtend
fname = Selection


'After the files are separated, this code inserts the field files at the beginning
' and ending of each file.

Selection.HomeKey Unit:=wdStory
Selection.InsertFile FileName:="C:\wpdoc\conv\Fields2.doc"
Selection.EndKey Unit:=wdStory
Selection.TypeBackspace
Selection.InsertFile FileName:="C:\wpdoc\conv\Fields3.doc"
Selection.EndKey Unit:=wdStory
Selection.TypeBackspace


'This code saves the file according to the variable above (fname), and it stores it into
' the path below. To change where this file is saved, simply change the path.

Target.SaveAs FileName:="C:\wpdoc\conv\" & fname & ".doc"
Target.Close

Set Target = Nothing

Next i

Set Source = Nothing

ActiveDocument.Close SaveChanges = True




End Sub

5) Each newly created file also has another document inserted at the end

called the Comments doc. This is a file containing more fields that have
comments attached to each field. These comments are used to instruct the
users on what information should be inserted. I made these fields into
Macrobutton fields so they would only need to click and enter info.

6) After all the files are separated and saved, a user will typically open

the document and enter information in the comment fields. He/she then takes
the files and clicks another macro that converts the data into a merge
source, opens the merge document and merges the data into the form. If this
works successfully, woo hoo! Usually there are a ton of formatting problems
and when they insert tables into a field, the table doesn't get merged
correctly.

7) Here is my favorite part. After this file is merged a form, all the

comments are naturally removed, but this same file needs to be used and
added to by countless other people in different departments. People will
need to open the files and use the comments to see where to add their
information for their own forms.

To tell you the truth, I'm amazed it worked for awhile. The conversion

and saving of the seperate files worked really well in my macro, but when
users would need to interact with the comments fields, etc., and then use it
again as a data source, well, that's where I got into trouble. It just
became very complicated for new Word users.

I am open to using a mixture of Access, Excel and Word if anyone thinks it

would help. These users are being asked to stop using Wordperfect by the
end of the year, so they really need a workable solution. If you search my
name, you will see that I've talked a lot about this issue in the past.