Easier than a macro is to just use Word's mail merge feature (as if
you're writing a form letter and the addresses for all the people are in
Excel or wherever. Use Access or Excel to set up your data fields for
the container list, regardless of how many columns you need. Then go
into Word, create a template to wrap the XML around the merge fields,
and point to the Excel data source. Run the merge, then delete all the
extra page/section breaks it automatically adds (using search and
replace is fastest).

Elizabeth Perkes
Utah State Archives

>>> [log in to unmask] 6/12/06 2:05:09 PM >>>
We use a template in Word for any folks who key in data.  We also can
map 
Excel or other files to this template (basically, it's a 4-column 
table).  MSWord has its drawback, particularly to do with character 
encoding, but it has the advantage that everyone, from students to
temps to 
folks in the university offices that send us their folder lists, knows
how 
to use MSWord.

Warning about MSWord:  Turn off "autocorrect" options TWICE (once in
Tools, 
Autocorrect options, Autoformat-as-you-type, and once in Tools,
Autocorrect 
options, Autoformat).  These are non-unicode characters and they will
break 
your EAD.  You can play with "cut" and "paste as unicode text" when you
get 
folder lists that have been created with these turned on, but if you
are 
able to control the use of Word, turn these OFF.

About the Macro:
1) the macro requires a table with the following four columns and with
this 
precise text as the header in each column in this precise order (case
counts!)
Folder Title                 Physical extent                 Box 
Number                 Note
2) the macro works by selecting and copying the table, pasting the
table 
into a new document, inserting extra columns, converting the table to
text, 
recognizing the number of tabs between each kind of data, and inserting
EAD 
encoding
3) the macro will not work if
   a) a box number is lacking in any row
   b) a return character has been used in any cell
   c) a tab character has been used in any cell
4) this is plain text that can be pasted into any text editor
5) if the data you have does not conform to these 4 columns, then you
can 
edit it after the conversion
6) here is what you get for each row in the table:
<c level="item">
<did>
<unittitle>Some folder title</unittitle>
<physdesc> <extent> (some extent) </extent> </physdesc>
<container> Box some number</container>
</did>
<note><p> some note.</p> </note> </c>
7) if there is no extent, there is no ead tagging for extent
8) if there is no note data, there is no ead tagging for note
<c level="item">
<did>
<unittitle>Some folder title</unittitle>
<container> Box some number</container>
</did>
</c>
9) Before you run the macro, place your cursor just before the 'N' in
note 
(just to be on the safe side).

The list probably does not permit attachments, but if you ask me I will

send invidivuals the template and the macro.  Here is the macro as
text:
 ========================================================================

Sub macEAD4Columns()
'
' macEAD1 Macro
' Macro recorded 10/19/2004 by kate

'
     Selection.Find.ClearFormatting
     Selection.Find.Replacement.ClearFormatting
     With Selection.Find
         .Text = ""
         .Replacement.Text = ""
         .Forward = True
         .Wrap = wdFindContinue
         .Format = False
         .MatchCase = False
         .MatchWholeWord = False
         .MatchWildcards = False
         .MatchSoundsLike = False
         .MatchAllWordForms = False
     End With
     Selection.Find.ClearFormatting
     With Selection.Find
         .Text = "folder title"
         .Replacement.Text = ""
         .Forward = True
         .Wrap = wdFindContinue
         .Format = False
         .MatchCase = False
         .MatchWholeWord = False
         .MatchWildcards = False
         .MatchSoundsLike = False
         .MatchAllWordForms = False
     End With
     Selection.Find.Execute
     Selection.HomeKey Unit:=wdStory
     Selection.Find.ClearFormatting
     With Selection.Find
         .Text = "folder title"
         .Replacement.Text = ""
         .Forward = True
         .Wrap = wdFindContinue
         .Format = False
         .MatchCase = False
         .MatchWholeWord = False
         .MatchWildcards = False
         .MatchSoundsLike = False
         .MatchAllWordForms = False
     End With
     Selection.Find.Execute
     Selection.Tables(1).Select
     Selection.Copy
     Documents.Add Template:="Normal", NewTemplate:=False,
DocumentType:=0
     Selection.PasteAndFormat (wdPasteDefault)
     Selection.HomeKey Unit:=wdStory
     Selection.Find.ClearFormatting
     Selection.Find.Replacement.ClearFormatting
     With Selection.Find
         .Text = "folder title"
         .Replacement.Text = ""
         .Forward = True
         .Wrap = wdFindContinue
         .Format = False
         .MatchCase = False
         .MatchWholeWord = False
         .MatchWildcards = False
         .MatchSoundsLike = False
         .MatchAllWordForms = False
     End With
     Selection.EscapeKey
     Selection.Rows.ConvertToText Separator:=wdSeparateByTabs,
NestedTables:= _
         True
     Selection.Find.ClearFormatting
     Selection.Find.Replacement.ClearFormatting
     With Selection.Find
         .Text = "^t^t^t^p"
         .Replacement.Text = ""
         .Forward = True
         .Wrap = wdFindContinue
         .Format = False
         .MatchCase = False
         .MatchWholeWord = False
         .MatchWildcards = False
         .MatchSoundsLike = False
         .MatchAllWordForms = False
     End With
     Selection.Find.Execute Replace:=wdReplaceAll

'
' macEAD2 Macro
' Macro recorded 10/19/2004 by kate
'
     Selection.HomeKey Unit:=wdStory
     Selection.WholeStory
     Selection.ConvertToTable Separator:=wdSeparateByTabs,
NumColumns:=4, _
         NumRows:=6, AutoFitBehavior:=wdAutoFitContent
     With Selection.Tables(1)
         .Style = "Table Grid"
         .ApplyStyleHeadingRows = True
         .ApplyStyleLastRow = True
         .ApplyStyleFirstColumn = True
         .ApplyStyleLastColumn = True
     End With
     Selection.Find.ClearFormatting
     With Selection.Find
         .Text = "folder title"
         .Replacement.Text = ""
         .Forward = True
         .Wrap = wdFindContinue
         .Format = False
         .MatchCase = False
         .MatchWholeWord = False
         .MatchWildcards = False
         .MatchSoundsLike = False
         .MatchAllWordForms = False
     End With
     Selection.Find.Execute
     Selection.InsertColumnsRight
     Selection.InsertColumnsRight
     Selection.InsertColumnsRight
     Selection.Rows.ConvertToText Separator:=wdSeparateByTabs,
NestedTables:= _
         True
     Selection.Find.ClearFormatting
     Selection.Find.Replacement.ClearFormatting
     With Selection.Find
         .Text = "^t^t^t^t"
         .Replacement.Text = "</unittitle> <physdesc> <extent> ("
         .Forward = True
         .Wrap = wdFindContinue
         .Format = False
         .MatchCase = False
         .MatchWholeWord = False
         .MatchWildcards = False
         .MatchSoundsLike = False
         .MatchAllWordForms = False
     End With
     Selection.Find.Execute Replace:=wdReplaceAll
     Selection.ConvertToTable Separator:=wdSeparateByTabs,
NumColumns:=3, _
         NumRows:=6, AutoFitBehavior:=wdAutoFitContent
     With Selection.Tables(1)
         .Style = "Table Grid"
         .ApplyStyleHeadingRows = True
         .ApplyStyleLastRow = True
         .ApplyStyleFirstColumn = True
         .ApplyStyleLastColumn = True
     End With

'
' macEAD3 Macro
' Macro recorded 10/19/2004 by kate
'
     Selection.HomeKey Unit:=wdStory
     Selection.Find.ClearFormatting
     With Selection.Find
         .Text = "Box Number"
         .Replacement.Text = "</unittitle> <physdesc> <extent> ("
         .Forward = True
         .Wrap = wdFindContinue
         .Format = False
         .MatchCase = False
         .MatchWholeWord = False
         .MatchWildcards = False
         .MatchSoundsLike = False
         .MatchAllWordForms = False
     End With
     Selection.Find.Execute
     Selection.InsertColumns
     Selection.InsertColumns
     Selection.InsertColumns
     Selection.Rows.ConvertToText Separator:=wdSeparateByTabs,
NestedTables:= _
         True
     Selection.HomeKey Unit:=wdStory
     Selection.Find.ClearFormatting
     Selection.Find.Replacement.ClearFormatting
     With Selection.Find
         .Text = "^t^t^t^t"
         .Replacement.Text = ") </extent> </physdesc> <container> Box
"
         .Forward = True
         .Wrap = wdFindContinue
         .Format = False
         .MatchCase = False
         .MatchWholeWord = False
         .MatchWildcards = False
         .MatchSoundsLike = False
         .MatchAllWordForms = False
     End With
     Selection.Find.Execute Replace:=wdReplaceAll
     Selection.Find.ClearFormatting
     Selection.Find.Replacement.ClearFormatting
     With Selection.Find
         .Text = "<physdesc> <extent> () </extent> </physdesc>"
         .Replacement.Text = ""
         .Forward = True
         .Wrap = wdFindContinue
         .Format = False
         .MatchCase = False
         .MatchWholeWord = False
         .MatchWildcards = False
         .MatchSoundsLike = False
         .MatchAllWordForms = False
     End With
     Selection.Find.Execute Replace:=wdReplaceAll

'
' macEAD4 Macro
' Macro recorded 10/19/2004 by kate
'
     Selection.HomeKey Unit:=wdStory
     Selection.WholeStory
     Selection.ConvertToTable Separator:=wdSeparateByTabs,
NumColumns:=2, _
         NumRows:=6, AutoFitBehavior:=wdAutoFitContent
     With Selection.Tables(1)
         .Style = "Table Grid"
         .ApplyStyleHeadingRows = True
         .ApplyStyleLastRow = True
         .ApplyStyleFirstColumn = True
         .ApplyStyleLastColumn = True
     End With
     Selection.HomeKey Unit:=wdStory
     Selection.Find.ClearFormatting
     With Selection.Find
         .Text = "note"
         .Replacement.Text = ""
         .Forward = True
         .Wrap = wdFindContinue
         .Format = False
         .MatchCase = False
         .MatchWholeWord = False
         .MatchWildcards = False
         .MatchSoundsLike = False
         .MatchAllWordForms = False
     End With
     Selection.Find.Execute
     Selection.InsertColumns
     Selection.Rows.ConvertToText Separator:=wdSeparateByTabs,
NestedTables:= _
         True
     Selection.Find.ClearFormatting
     Selection.Find.Replacement.ClearFormatting
     With Selection.Find
         .Text = "^t^t"
         .Replacement.Text = "</container> <note><p> "
         .Forward = True
         .Wrap = wdFindContinue
         .Format = False
         .MatchCase = False
         .MatchWholeWord = False
         .MatchWildcards = False
         .MatchSoundsLike = False
         .MatchAllWordForms = False
     End With
     Selection.Find.Execute Replace:=wdReplaceAll
     Selection.HomeKey Unit:=wdStory
     Selection.Find.ClearFormatting
     Selection.Find.Replacement.ClearFormatting
     With Selection.Find
         .Text = "^p"
         .Replacement.Text = _
             ".</p> </note> </c>^p<c level=""item""><did> <unittitle>"
         .Forward = True
         .Wrap = wdFindContinue
         .Format = False
         .MatchCase = False
         .MatchWholeWord = False
         .MatchWildcards = False
         .MatchSoundsLike = False
         .MatchAllWordForms = False
     End With
     Selection.Find.Execute Replace:=wdReplaceAll
     With Selection.Find
         .Text = "</container>"
         .Replacement.Text = "</container> </did>"
         .Forward = True
         .Wrap = wdFindContinue
         .Format = False
         .MatchCase = False
         .MatchWholeWord = False
         .MatchWildcards = False
         .MatchSoundsLike = False
         .MatchAllWordForms = False
     End With
     Selection.Find.Execute Replace:=wdReplaceAll
     With Selection.Find
         .Text = ".."
         .Replacement.Text = "."
         .Forward = True
         .Wrap = wdFindContinue
         .Format = False
         .MatchCase = False
         .MatchWholeWord = False
         .MatchWildcards = False
         .MatchSoundsLike = False
         .MatchAllWordForms = False
     End With
     Selection.Find.Execute Replace:=wdReplaceAll

'
' macEADcleanup Macro
' Macro recorded 10/19/2004 by kate
'
     Selection.Find.ClearFormatting
     Selection.Find.Replacement.ClearFormatting
     With Selection.Find
         .Text = _
             "Folder Title</unittitle> <physdesc> <extent> (Physical 
extent) </extent> </physdesc> <container> Box Box Number</container>
</did> 
<note><p> Note.</p> </note> </c>^p"
         .Replacement.Text = ""
         .Forward = True
         .Wrap = wdFindContinue
         .Format = False
         .MatchCase = False
         .MatchWholeWord = False
         .MatchWildcards = False
         .MatchSoundsLike = False
         .MatchAllWordForms = False
     End With
     Selection.Find.Execute Replace:=wdReplaceAll
     Selection.Find.ClearFormatting
     Selection.Find.Replacement.ClearFormatting
     With Selection.Find
         .Text = "<note><p> .</p> </note>"
         .Replacement.Text = ""
         .Forward = True
         .Wrap = wdFindContinue
         .Format = False
         .MatchCase = False
         .MatchWholeWord = False
         .MatchWildcards = False
         .MatchSoundsLike = False
         .MatchAllWordForms = False
     End With
     Selection.Find.Execute Replace:=wdReplaceAll
     With Selection.Find
         .Text = "<c level=""item""><did> <unittitle>.</p> </note>
</c>^p"
         .Replacement.Text = ""
         .Forward = True
         .Wrap = wdFindContinue
         .Format = False
         .MatchCase = False
         .MatchWholeWord = False
         .MatchWildcards = False
         .MatchSoundsLike = False
         .MatchAllWordForms = False
     End With
     Selection.Find.Execute Replace:=wdReplaceAll
     With Selection.Find
         .Text = "<c level=""item"""
         .Replacement.Text = ""
         .Forward = True
         .Wrap = wdFindContinue
         .Format = False
         .MatchCase = False
         .MatchWholeWord = False
         .MatchWildcards = False
         .MatchSoundsLike = False
         .MatchAllWordForms = False
     End With
     Selection.Find.Execute

'
' macEADCleanup2 Macro
' Macro recorded 10/19/2004 by kate
'
     Selection.Find.ClearFormatting
     Selection.Find.Replacement.ClearFormatting
     With Selection.Find
         .Text = "<c level=""item""><did> <unittitle>^p"
         .Replacement.Text = ""
         .Forward = True
         .Wrap = wdFindContinue
         .Format = False
         .MatchCase = False
         .MatchWholeWord = False
         .MatchWildcards = False
         .MatchSoundsLike = False
         .MatchAllWordForms = False
     End With
     Selection.Find.Execute Replace:=wdReplaceAll
End Sub



At 02:42 PM 6/12/2006 -0400, Katy Rawdon-Faucett wrote:

>Dear colleagues,
>
>
>
>My institutions is beginning its first foray into marking up our
finding 
>aids into EAD. Were all reasonably EAD-literate, and marking up the 
>frontmatter should not (I say now&) be a problem.
>
>
>
>I was wondering, however, how other institutions handle the mark-up of

>their box and folder lists. Cutting and pasting text from our finding
aids 
>into Oxygen, which is what we are using for mark-up, just isnt going
to 
>cut it when we have collections with thousands of folder-level
entries. 
>Has anyone had any luck with using macros, or any other system, to
avoid 
>having to mark up a box list by hand? We cant all be spending hours 
>cut-and-pasting, can we?
>
>
>
>Thanks,
>
>Katy
>
>
>
>____________________
>Katy Rawdon-Faucett
>Archivist
>The Barnes Foundation
>300 North Latch's Lane
>Merion, PA  19066-1759
>Ph:  (610) 667-0290 ext. 1048
>Fax: (610) 664-4026
><blocked::mailto:[log in to unmask]>[log in to unmask]

>www.barnesfoundation.org/archives.html 
>
>

Kate Bowers
Collection Services Archivist
Harvard University Archives
Cambridge, MA 02138
voice:  (617) 495-2461
fax:    (617) 495-8011
email:  [log in to unmask]