Word Macro to Collect Bates Numbers and Send them to Excel

Word Macro to Collect Bates Numbers and Send them to Excel

August 28, 2015

A common task for a paralegal or litigation support professional will be to collect PDFs of bates numbers cited by an attorney in a memo or outline for a deposition or an oral argument.   If the memo or outline has many bates number cites, or even if it's just very lengthy, getting a list of all of the bates numbers may take a long time - whether you copy them one by one or copy the full text of the Word document and sort through it in Excel.  Use the macro posted to http://windowssecrets.com/forums/showthread.php/120884-Macro-to-extract-numbers-from-a-Word-doc by Stuart R (see the 9/4/2009 8:49 entry), to collect bates numbers automatically.


1.   Copy the macro from the windowssecrets.com page, or select it at the bottom of this night's post.  Go back to the Word document. 


2. Press ALT + F11 to enter Visual Basic.   Select the document you are editing in the Project window at the upper left and right click and select Insert .  .  . Module.   Paste in the macro.


3.   In the line which begins '.Text = ' enter the format of the bates number you want to collect,  You need to list the letter prefix exactly and then edit the part which reads '[0-9]{4}' so that the number in the second curved brackets is the number of digits used in the bates numbers you're looking for.   In this example our bates numbers are in this format JTS000000000 so you're going to edit the Visual Basic language so it reads:

.Text = "JTS[0-9]{9}"


4.  Close Visual Basic and go back to the Word document.  If you're in Word 2010 go to View . . . Macros and select 'NumbersToExcel'.  


5. The macro will prompt you to save the Excel file.  Do so in a location where you can easily find it. 


6. Open the Excel file, and you will find that all of the bates numbers have been listed in column A!







The video may not be visible on the home page of Litigation Support Tip of the Night.  Click the title for the tip's individual page, or see the video at:  https://www.youtube.com/watch?v=A4w-CI8LX3c&feature=youtu.be


Public Sub NumbersToExcel()
Dim xlApp As Object
Dim xlWbk As Object
Dim xlWsh As Object
Dim blnStartExcel As Boolean
Dim i As Integer

On Error Resume Next

Set xlApp = GetObject(, "Excel.Application")
If xlApp Is Nothing Then
Set xlApp = CreateObject("Excel.Application")
If xlApp Is Nothing Then
MsgBox "Cannot activate Excel!", vbExclamation
Exit Sub
End If
blnStartExcel = True
End If

On Error GoTo ErrHandler

Set xlWbk = xlApp.Workbooks.Add
Set xlWsh = xlWbk.Worksheets(1)

With ActiveDocument.Content
With .Find
.Text = "[0-9]{2}-[0-9]{4}"
.Replacement.Text = ""
.Forward = True
.Wrap = wdFindStop
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchAllWordForms = False
.MatchSoundsLike = False
.MatchWildcards = True 'Essential!
End With
While .Find.Execute
' Found string is selected; do stuff here
i = i + 1
xlWsh.Cells(i, 1) = "'" & .Text
.Find.MatchWildcards = False 'Politely return to normal
End With

On Error Resume Next
xlWbk.Close SaveChanges:=True
If blnStartExcel Then
End If
Set xlWsh = Nothing
Set xlWbk = Nothing
Set xlApp = Nothing
Exit Sub

MsgBox Err.Description, vbExclamation
Resume ExitHandler

End Sub

Please reload

Contact Me With Your Litigation Support Questions:


  • Twitter Long Shadow

© 2015 by Sean O'Shea . Proudly created with Wix.com