#1
|
|||
|
|||
Adding terms to a search & replace macro
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. Code:
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 |
#2
|
||||
|
||||
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.
__________________
Graham Mayor - MS MVP (Word) (2002-2019) Visit my web site for more programming tips and ready made processes www.gmayor.com |
#3
|
|||
|
|||
Add-in
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.
|
#4
|
|||
|
|||
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. Code:
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 |
#5
|
|||
|
|||
Macro
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. |
#6
|
|||
|
|||
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. |
#7
|
||||
|
||||
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.
__________________
Graham Mayor - MS MVP (Word) (2002-2019) Visit my web site for more programming tips and ready made processes www.gmayor.com |
Tags |
search and replace |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
VBA code to compile one document based on multiple search terms | Hoxton118 | Word VBA | 4 | 04-04-2021 06:02 AM |
Best Practice for Indexing Multiple Word Terms and Sub-Terms | jhy001 | Word | 4 | 11-06-2017 02:08 PM |
Macro on Search and Replace | davidhuy | Word VBA | 1 | 12-19-2014 04:47 AM |
Onenote 2013 search is not identifying search terms correctly | Delta223 | OneNote | 0 | 08-12-2014 06:40 AM |
How do I search for terms in initial upper-case? | bertietheblue | Word | 5 | 05-02-2012 05:24 AM |