Word Macro to Collect Bates Numbers and Send them to Excel

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 .ClearFormatting .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 Wend .Find.MatchWildcards = False 'Politely return to normal End With ExitHandler: On Error Resume Next xlWbk.Close SaveChanges:=True If blnStartExcel Then xlApp.Quit End If Set xlWsh = Nothing Set xlWbk = Nothing Set xlApp = Nothing Exit Sub ErrHandler: MsgBox Err.Description, vbExclamation Resume ExitHandler End Sub