how to use a word list in an MSWord macro
Thread poster: Frank van Thienen
Frank van Thienen  Identity Verified
Canada
Local time: 05:49
Dutch to English
Oct 1, 2011

Hi,

I have a spreadsheet with three columns, showing the various English spelling variants of words or phrases; the column headers are: US, UK-modern and UK-Oxford.
For example, one row may have "theater, theatre, theatre"
Another row "organize, organise, organize"
and so on, and so on, for a good 600 rows.

This is great as a reference tool, but I wonder if there is a simple way to turn this into a usable and easily updatable macro that will make global corrections in a document?

Rather than embedding all of this text into a macro, I'd like the macro to "call up" this list and to compare it with the document, basically looking for all the entries, then replacing the entry by the specified language variant.

Is it possible for a Word macro to call on a (closed) Excel spreadsheet and to use the contents in a global change operation in Word?

TIA,

Frank


Direct link Reply with quote
 
There are several ways Oct 2, 2011

of getting data from a saved Excel workbook and using it in a Word macro, like using the Excel object model from your macro to open the workbook and read the data or using the workbook as a data source. There is a Word add-in by Greg Maxey (a Microsoft Word MVP) here[^] that is capable of finding and replacing text using words from a predefined Excel list. As far as I know the addin searches for words from column A of the first sheet in the workbook and replaces them with corresponding words from column B and there is no way to change that (the source code is not available). This is not ideal for you as you wouldn't be able to specify what column to use for searching for words.

Direct link Reply with quote
 
Frank van Thienen  Identity Verified
Canada
Local time: 05:49
Dutch to English
TOPIC STARTER
thanks Oct 3, 2011

Hi Uros,

Thanks for the suggestion. I don't think the Greg Maxey suggestion will do what i want to do.
This was a bad time for me to pose this question, because I haven't had much of a chance to experiment (a sudden rush of work), but I'm wondering what VBA statements I need to access the columns in a specific worksheet, and then get the find/replace macro to run through the entire list?

Frank


Direct link Reply with quote
 

István Hirsch  Identity Verified
Local time: 14:49
English to Hungarian
Programs Oct 4, 2011

There are several programs which can perform batch replacements. You should import your UK column as Find column and UK-modern column as Replace column, for example. The program then can perform all Find/Replace operations according to the imported „dictionary” by pressing of a button, in several files/folders.

I think the final task is to produce a translation in one given version, say UK-Oxford, so I would merge the other two (US + UK-modern) to create the find column for the batch replacement.

When choosing a program, check that it works with your file type, for example, Textfinder works with text files, Advanced Find and Replace works with several files including doc files. It is also to be checked that it works with your OS.


Direct link Reply with quote
 
Just a smal example Oct 4, 2011

Here is an example VBA macro that opens an Excel workbook, loops through the cells of a specified column, searches the active Word document for words in each cell and replaces found words with corresponding words from another column:


Sub ReplaceFromList()


' Column A - US
intUS = 1

' Column B - UK-modern
intUKModern = 2

' Column C - UK-Oxford
intUKOxford = 3

' Spreadsheet location on the disk
strSpreadsheetPath = "C:\Data\Dictionary.xls"

Set objExcel = CreateObject("Excel.Application")
objExcel.DisplayAlerts = False

' If you change this to False
' Excel won't be visible while reading the data
objExcel.Visible = True

Set objWorkbook = objExcel.Workbooks.Open(strSpreadsheetPath)

' Assume the word lists are in the first workheet
Set objSheet = objWorkbook.Sheets(1)

' Which column to use for searching
Set colFind = objSheet.UsedRange.Columns(intUS)

' Column to use for replace
Set colReplace = objSheet.UsedRange.Columns(intUKModern)

' Find/Replace the words in the active document
Set objContent = ActiveDocument.Content
objContent.Find.MatchWholeWord = True

For i = 2 To colFind.Rows.Count
If colFind.Cells(i, 1) "" And colReplace.Cells(i, 1) "" Then
objContent.Find.Execute FindText:=colFind.Cells(i, 1), _
ReplaceWith:=colReplace.Cells(i, 1), Replace:=wdReplaceAll

End If
Next

objWorkbook.Close
objExcel.Quit

End Sub


This is just a quick example, I haven't included any error checking.


Direct link Reply with quote
 
Frank van Thienen  Identity Verified
Canada
Local time: 05:49
Dutch to English
TOPIC STARTER
it works! Oct 5, 2011

Uros C wrote:

Here is an example VBA macro that opens an Excel workbook



Fabulous! just what I was looking for. I'm fairly comfortable with VBA coding, but for some reason I've never needed to use an external object.
I've done some tweaking of your code, but you provided me with the solution I was looking for. Perfect!

I now have an Excel workbook with three worksheets, as suggested by Istvan, one for each language variant, and the VBA code consists of a Find/Replace function (most of which is Uros' code) which is called up by one of three macros, one for each language variant.

Thanks again!
Frank


Direct link Reply with quote
 

Tony M  Identity Verified
France
Local time: 14:49
Member
French to English
+ ...
Please share! Oct 5, 2011

Hi Frank!

Glad you found a solution to your requirements!

I've been following this thread with interest, as I too have a similar requirement.

As Uros was kind enough to share his utility with us, I wonder if you'd please consdier doing the same with your modified version? I know nothing at all about VBA programming, so would not be able to modify anything for myself, but it sounds as if you've now got it to a point where I would be able to use it without needing to delve into programming.

Thanks, in advance!

Tony


Direct link Reply with quote
 
Frank van Thienen  Identity Verified
Canada
Local time: 05:49
Dutch to English
TOPIC STARTER
here's the code . . . Oct 5, 2011

Tony M wrote:
Please share!


Sure thing, Tony. Here it is. Copy everything between the two lines of asterisks into your VBA editor. You'll have to edit the spreadsheet name and path, as well as the worksheets.

The spreadsheet needs to be in two columns, with a one-row header. Column A has the Find string, and column B has the Replace string. Any other format will require tweaking of the code.

It would be easy to add any number of Find/Replace lists by simply copying and editing one of the 6-line "Sub" routines.

The routine will give you the option to turn on Track Changes, which I highly recommend, because this kind of global Replace process is very risky. Context is important in quite a few cases.
An alternative to Track Changes is also built in: by activating the "strCheck" variable, below. Right now it is blank, but you can activate it by switching the starting single quote (which turns a line into a comment). Play with it (using a test doc) and you'll see what it does.

Good luck with this.
Frank

'***************************
Dim strSheet As String

Sub checkVariantUKmod()
'specify worksheet name
strSheet = "UKmod"
'run the Find/Replace routine
CheckVariantSpelling
End Sub

Sub checkVariantUKoed()
'specify worksheet name
strSheet = "UKoed"
'run the Find/Replace routine
CheckVariantSpelling
End Sub

Sub checkVariantUS()
'specify worksheet name
strSheet = "US"
'run the Find/Replace routine
CheckVariantSpelling
End Sub


Function CheckVariantSpelling()

'get user confirmation for this procedure
Dim askConfirm As Integer
askConfirm = MsgBox("Are you sure you want to run this procedure?", vbYesNo + vbExclamation, "Are you sure?")
Select Case askConfirm
Case vbNo
Exit Function
End Select

'turn on Track Changes?
Dim askTC As Integer
askTC = MsgBox("Do you want to turn on Track Changes (recommended!) before running this procedure?", vbYesNo + vbExclamation, "Turn on Track Changes?")
Select Case askTC
Case vbYes
With ActiveDocument
.TrackRevisions = True
.ShowRevisions = True
End With
End Select

'define string for manual checking after global replace
' strCheck = "CHECK=>"
strCheck = ""

'Spreadsheet location on the disk
strSpreadsheetPath = "C:\Users\Frank\Documents\Translating\English spelling variants.xlsx"

'specify column number for FIND string
intFind = 1

'specify column number for REPLACE string
intRepl = 2

Set objExcel = CreateObject("Excel.Application")
objExcel.DisplayAlerts = False

'Excel will(True) or won't(False) be visible while reading the data
objExcel.Visible = False

Set objWorkbook = objExcel.Workbooks.Open(strSpreadsheetPath)

'specify worksheet name
Set objSheet = objWorkbook.Sheets(strSheet)

'Which column to use for searching
Set colFind = objSheet.UsedRange.Columns(intFind)

'Column to use for replace
Set colReplace = objSheet.UsedRange.Columns(intRepl)

'Find/Replace the words in the active document
Set objContent = ActiveDocument.Content
objContent.Find.MatchWholeWord = True

'starting in row 2
For i = 2 To colFind.Rows.Count
If colFind.Cells(i, 1) > "" And colReplace.Cells(i, 1) > "" Then
objContent.Find.Execute FindText:=colFind.Cells(i, 1), _
ReplaceWith:=strCheck & colReplace.Cells(i, 1), Replace:=wdReplaceAll
End If
Next

objWorkbook.Close
objExcel.Quit

End Function

'***************************


[Edited at 2011-10-05 16:13 GMT]


Direct link Reply with quote
 


To report site rules violations or get help, contact a site moderator:


You can also contact site staff by submitting a support request »

how to use a word list in an MSWord macro

Advanced search






PerfectIt consistency checker
Faster Checking, Greater Accuracy

PerfectIt helps deliver error-free documents. It improves consistency, ensures quality and helps to enforce style guides. It’s a powerful tool for pro users, and comes with the assurance of a 30-day money back guarantee.

More info »
memoQ translator pro
Kilgray's memoQ is the world's fastest developing integrated localization & translation environment rendering you more productive and efficient.

With our advanced file filters, unlimited language and advanced file support, memoQ translator pro has been designed for translators and reviewers who work on their own, with other translators or in team-based translation projects.

More info »



Forums
  • All of ProZ.com
  • Term search
  • Jobs
  • Forums
  • Multiple search