Transform Excel into a translation tool using this VBA macro20 Aug 2014
A brief preface
This summer I did an internship at BSH Hausgeräte (aka Bosch and Siemens Home Appliances), where I built a lot of amazing tools on Excel using VBA (Visual Basic for Applications). I helped them automate monthly reports, which now take just a few seconds to complete. I would’ve contribute a lot more to the company if not for the corporate restrictions, according to which I wasn’t authorized to access code from other departments.
Anyway, while I signed NDA for the tools I’ve built for them, I developed tools to help my other routine, which included translating texts sent from our Istanbul headquarter to all subsidiaries in CIS and North Africa. I will share one of them below.
If you frequently translate texts, you know that a lot of phrases within a domain are repeated, and you probably have your own shortcuts to deal with them. I used MS Office’s standard Find and Replace tool to translate the most frequent repetitions.
My VBA macro automates this process as follows: you create an Excel document with original phrase in the first column and its translation in the corresponding cell in the second one, and save it. No column names, no anything else. This file will work as your translation database — you can always add a new phrase as a new row.
Then you run the script below in any MS Word file with the original text, and it goes over every phrase in the first column of your Excel database, finds it in the Word file and replaces it with its translation from the second column.
If you know a little Visual Basic, you will understand the code below:
Sub translation_tool() Dim objExcel As New Excel.Application Dim exWb As Excel.Workbook Dim exSh As Worksheet Set exWb = objExcel.Workbooks.Open("path_to_translation_database.xlsx") Set exSh = exWb.Sheets("Sheet1") For I = 1 To 100 Selection.Find.ClearFormatting Selection.Find.Replacement.ClearFormatting With Selection.Find .Text = exSh.Cells(I, 1).Value .Replacement.Text = exSh.Cells(I, 2).Value .Forward = True .Wrap = wdFindContinue .Format = False .MatchCase = False .MatchWholeWord = False .MatchWildcards = False .MatchSoundsLike = False .MatchAllWordForms = False End With Selection.Find.Execute Replace:=wdReplaceAll Next I exWb.Close End Sub
This translation database grows exponentially as you translate more text in your domain. With time it becomes so large that you will just press the button and will only need to slightly edit the resulting translated text.
Important there is one caveat though: you must add longer phrases before the shorter ones, because otherwise it will translate individual words first and won’t be able to recognize the phrase itself.
Here you go, enjoy. For collaboration press here.