Adding terms to a search & replace macro

[Ulodesk]

I found this macro on Allen Wyatt's Wordtips site. I have a multi-hundred-page document coming through soon, in which I would like to lower-case a list of words improperly capitalized. This macro handles only a single word, but takes care of instances in which the word begins a sentence; I have used the word "video". What adjustment would I need to enter more than one such term, to obviate having to make multiple single-word passes?


Is it also possible to exclude MS's built-in heading styles from the replacement, since the words would be properly capitalized there?
Thank you.

Sub ChangeCase2()
    With Selection.Find
        .ClearFormatting
        .Wrap = wdFindContinue
        .Forward = True
        .Format = True
        .MatchCase = False
        .MatchWildcards = False
        .Text = "video"
        .Execute
        While .Found
            Selection.Range.Case = wdLowerCase
            Selection.Range.Case = wdTitleSentence
            Selection.Collapse Direction:=wdCollapseEnd
            .Execute
        Wend
    End With
End Sub

[gmayor]

You will still need multiple passes for multiple words - see
http://www.gmayor.com/document_batch_processes.htm
and the facility to replace pairs in a table. If you set the wildcard option, whatever you put in the first column, with regard to case, is replaced with the entry in the second column - so e.g. replace Video with video, VIDEO with video etc.

[Ulodesk]

Thank you, sir, but security rules as my workplace don't allow us to import add-ins. I should have stated that in my original post. I thought that VBA might be able to run the same process in the macro on a list of words. None would be in all caps; only capitalized. The list of words is not that long, so I'll just have to run the macro several times, substituting the single word.

[slaycock]

Try the following

1. Ceate an excel workbook where column a of the first worksheet contains the words you want in lower case

2. Run the macro convert_excel_list_to_lower_case

3. Use the filepicker to select your excel file.

The macro assumes that you are only changing the case of the words as stated in your initial post. If you want to change words then a minor adaption will allow this to be done.

Option Explicit

Sub convert_excel_list_to_lower_case()


Dim xlApp                       As Excel.Application
Dim xlWB                        As Excel.Workbook
Dim xlWS                        As Excel.Worksheet
Dim wb_path                     As String
Dim index                       As Long
Dim find_range                  As Word.Range
Dim find_strings()              As Variant


    Set xlApp = New Excel.Application
    wb_path = get_workbook
    If wb_path = vbNullString Then
       Exit Sub
    End If
    
    Set xlWB = xlApp.Workbooks.Open(wb_path)
    Set xlWS = xlWB.Worksheets(1)
    
    find_strings = xlWS.Application.WorksheetFunction.Transpose(xlWS.Range("A1", xlWS.Cells(xlWS.Range("A1").End(xlDown).row, 1)))
    For index = LBound(find_strings, 1) To UBound(find_strings, 1)
        Set find_range = ActiveDocument.StoryRanges(wdMainTextStory)
        With find_range.find
            .ClearFormatting
            .Wrap = wdFindStop
            .Forward = True
            .Format = True
            .MatchCase = False
            .MatchWildcards = False
            .text = find_strings(index)
            .Execute
            Do Until Not .Found
                If InStr(find_range.Style, "Heading") = 0 Then
                    find_range.Case = wdLowerCase
                    find_range.Case = wdTitleSentence
                End If
                .Execute
            Loop
        End With
    Next

    set xlWS = nothing
    set xlWB = nothing
    set xlApp = nothing
End Sub

Function get_workbook() As String
Dim file_picker                             As FileDialog
Const first_picked_file                     As Long = 1


    Set file_picker = Application.FileDialog(msoFileDialogFilePicker)
    With file_picker
        .Title = "Select Workbook"
        .InitialFileName = ActiveDocument.Path
        
        If .Show = vbCancel Then
            get_workbook = vbNullString
        Else
            If InStr(.SelectedItems(first_picked_file), ".xlsx") = 0 Then
                get_workbook = vbNullString
                MsgBox "Oops. That file wasn't a workbook", vbOKOnly
            Else
                get_workbook = .SelectedItems(first_picked_file)
            End If
        End If
    End With
    
End Function

[Ulodesk]

Slaycok, thank you very much; this looks great, from what little I can make out in VBA. However, I have two questions:

2. Run the macro convert_excel_list_to_lower_case
Where do I find this macro?

3. Use the filepicker to select your excel file.
Is "filepicker" a pop-up from the convert_excel_... macro?

Thank you.

[slaycock]

Your questions demonstrate your unfamiliarity with VBA.

To run the macro you need to be able to install the macros in your normal template (or other template of your choice).

To do this you will need to enable Developer mode. This is done through file->options->Customize Ribbon

In the right hand column of the dialog box that pops up make sure that the Developer check box is ticked.

This will enable the Developer Tab on the word Ribbon

Select the Developer Tab and click Visual Basic - this will open the VBA IDE.

Copy and paste the code above to the IDE and then close the IDE or minimise it.

In Word, select the Developer Tab again if necessary and then click on Macros. This will present a list of macros that you can run. If you have copied the code correctly this will include the macro 'convert_excel_list_to_lower_case'.


The filepicker is a dialog box which pops up when you run the macro and allows you to navigate to, and select, your excel workbook. This is the workbook you will have created with a list of the words you wish to change to lower case.

[gmayor]

Frankly I am astonished that your company doesn't allow the use of add-ins from reputable suppliers, but allows you to mess around with VBA when you are unfamiliar with it.