Mobile menu

Excel glossary: how to use a macro
Thread poster: Wilmer Brouwer

Wilmer Brouwer
Local time: 16:45
Member (2006)
English to Dutch
+ ...
Nov 27, 2007

I am currently preparing a very large glossary and have a problem that I am sure would be very easy to do with a macro and almost impossible to do without one so I am looking for some help to get me started.
The situation is that I have a bilingual glossary English > Dutch where English always has one entry and Dutch has all possible translations separated by comma's. So for example it has
"Garbage > Afval, Rommel"
What I would like to do in order to be able to use the glossary in Multiterm is to make a macro that would split the entry in Dutch after the comma and make a new entry with the second part and remove the comma, so that I get
"Garbage > Afval"
"Garbage > Rommel"
as separate entries. After that it should check if there are any comma's left and continue to split until that is the case. I used to make programs like this in Quick Basic so i know it should not be too difficult but unfortunately I am not very good at Visual Basic and making macros. Any help as to how to do this, which functions to use or a similar macro that already exists would be greatly appreciated.

Direct link Reply with quote

Fabio Descalzi  Identity Verified
Local time: 11:45
Member (2004)
German to Spanish
+ ...
Moving this thread... Nov 27, 2007

... to Office Applications forum

Direct link Reply with quote

Jabberwock  Identity Verified
Local time: 16:45
Member (2004)
English to Polish
No macros needed... Nov 27, 2007

I suppose someone comes up with a macro sooner or later, but it seems to me that you do not need macros at all.

You could try the following procedure (admittedly, its efficiency depends on what is the maximum number of Dutch synonyms, more than six or seven would be problematic).

First, export the file to comma-separated file, so that you have:
"Garbage,Afval, Rommel"

Import the file back into as many sheets as there are Dutch entries in the longest entry.

You should get:

Garbage > Afval > Rommel

Then simply delete the irrelevant columns: from the first sheet all the columns except the first and second one, from the second sheet the column two and all the columns from the fourth one to the last one etc.

Then combine all the sheets into one and sort the first column.

P.S. My VBA got rusty, but if you want, maybe I could write a script in Perl (provided the file is exported to csv).

[Edited at 2007-11-27 16:46]

Direct link Reply with quote

Cecilia Falk  Identity Verified
Local time: 16:45
English to Swedish
Word Nov 27, 2007

I would save the Excel file as text and then open and manipulate it in Word with search and replace - I think it should be quite easily done.

Best regards,

Direct link Reply with quote
Local time: 16:45
English to German
Use the Split function Nov 27, 2007

Since Excel has a built-in split function the macro is really simple:

'simple glossary macro
Sub WriteTD()

'get spreadsheet dimensions
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
LastRow = ActiveSheet.UsedRange.Rows.Count

'create output file handle
Set fs = CreateObject("Scripting.FileSystemObject")
Set a = fs.CreateTextFile("c:\Glossary.txt", True, True)

'output all terms
For i = 1 To LastRow

Source = Cells(i, 1).Text
Target = Cells(i, 2).Text
TargetList = Split(Target, ",")

For k = 0 To UBound(TargetList)
a.WriteLine "**"
a.WriteLine "[English]" & Trim(Source)
a.WriteLine "[Dutch]" & Trim(TargetList(k))
Next k

Next i

MsgBox "Done"
End Sub

This macro assumes that you alreay opened the text file with Excel and used ">" as the delimiter. So you should end up with a basic two-column glossary with the English string in the first column and the translated string in the second column. The macro will go through each line and write it to a Multiterm compatible text file in C:\Glossary.txt. If it finds a comma delimited string in the target field, it'll write multiple entries to the file.

You'll of course need to replace the rectangular brackets around [English] and [Dutch] in the code with angle brackets. (I tried using them but the Forum software thinks they're html tags and hides them.)


Direct link Reply with quote

Jerzy Czopik  Identity Verified
Local time: 16:45
Member (2003)
Polish to German
+ ...
Two side notes Nov 27, 2007

First, to use < and > signs in the forum, you have to use entities for them instead the pure sign from the keyboard. The corresponding entities are "& lt ;" for < and "& gt ;" for > (of course you will need to remove spaces from the entities).

Second, to create a Multiterm glossary containing synonyms, you should avoid creating a separated entry for each synonym. Ie you should not have:
"Garbage > Afval"
"Garbage > Rommel"
but instead: ""Garbage > Afval > Rommel" in three columns.
This means you have to have one column for English (labeled "English") and as many dutch columns (each of them labeled "Dutch"), as the maximum number of synonyms in your file is.
Then you just convert the Excel to Multiterm using MT Convert.
A description of how it should be done can be downloaded here.


Direct link Reply with quote

Wilmer Brouwer
Local time: 16:45
Member (2006)
English to Dutch
+ ...
Thanks Nov 28, 2007

Thanks everyone for the very helpful suggestions.
Jabberwock, unfortunately some words have about 30 possible translations. But I will keep that solution in mind for other glossaries, it's something I hadn't thought about. Thanks also for offering to write something in Perl, but thanks to the other solutions I won't need it.
Olaf, thanks for the macro, I haven't had time to try it yet but I will probably do so over the weekend. I will probably use it in the future to prepare glossaries for use with Wordfast.
Jerzy, thank you for your side notes, I used them in the end because I wanted to use this file in Multiterm. I just saved the file as a txt file, opened it with excel and separated by comma's and converted it to multiterm and that works. So thank you very much!

[Edited at 2007-11-28 17:15]

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 »

Excel glossary: how to use a macro

Advanced search

Protemos translation business management system
Create your account in minutes, and start working! 3-month trial for agencies, and free for freelancers!

The system lets you keep client/vendor database, with contacts and rates, manage projects and assign jobs to vendors, issue invoices, track payments, store and manage project files, generate business reports on turnover profit per client/manager etc.

More info »
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 »

All of
  • All of
  • Term search
  • Jobs