excelstarter1
Board Regular
- Joined
- Jul 20, 2017
- Messages
- 81
Hey guys, I am Robert and new to this forum. As I am an Excel/VBA beginner, I hope you can help me with my problem. My head is already in pain
I am trying to build a VBA dictionary macro. What is the idea/problem: I have an Excel file which my company is using all the time to consolidate different information from e.g. subsidiary companies or customers. Our main Excel file is in English (and the content is always changing). However, we would like to have a macro to automatically translate all the content from the English Workbook to another language e.g. Spanish or Italian.
Example Table in this original English Excel File:
[TABLE="width: 500"]
<tbody>[TR]
[TD]Customer
[/TD]
[TD]C1
[/TD]
[TD]...
[/TD]
[TD]...
[/TD]
[/TR]
[TR]
[TD]Country
[/TD]
[TD]Denmark
[/TD]
[TD]...
[/TD]
[TD]...
[/TD]
[/TR]
[TR]
[TD]Name
[/TD]
[TD]...
[/TD]
[TD]...
[/TD]
[TD]...
[/TD]
[/TR]
[TR]
[TD]Item
[/TD]
[TD]...
[/TD]
[TD]...
[/TD]
[TD]...
[/TD]
[/TR]
[TR]
[TD]Price
[/TD]
[TD]100
[/TD]
[TD]...
[/TD]
[TD]...
[/TD]
[/TR]
</tbody>[/TABLE]
Basically the macro should load an Excel File which works as a dictionary (with column 1 containing English word, column 2 Spanish translation, column 3 Italian translation etc.). Then I want the macro to find and replace all the words/text in the original English Workbook (highlighted in blue in above table) with the Spanish or Italian Translation (from the dictionary file).
Basically it is a loop for each cell in the whole workbook (all worksheets) by comparing each cell content (text) with the dictionary and replacing the original content with the translation (from the dictionary).
Does anyone have an idea how to start??
I read a lot about the "Scripting.Dictionary" object, but I dont think this will work in my case as I have more than 2 columns in the dictionary (column 1: original English, colum 2: Spanish translation, column 3: Italian translation and so on).
Thank you very much in advance!!!
Best regards
Robert
I am trying to build a VBA dictionary macro. What is the idea/problem: I have an Excel file which my company is using all the time to consolidate different information from e.g. subsidiary companies or customers. Our main Excel file is in English (and the content is always changing). However, we would like to have a macro to automatically translate all the content from the English Workbook to another language e.g. Spanish or Italian.
Example Table in this original English Excel File:
[TABLE="width: 500"]
<tbody>[TR]
[TD]Customer
[/TD]
[TD]C1
[/TD]
[TD]...
[/TD]
[TD]...
[/TD]
[/TR]
[TR]
[TD]Country
[/TD]
[TD]Denmark
[/TD]
[TD]...
[/TD]
[TD]...
[/TD]
[/TR]
[TR]
[TD]Name
[/TD]
[TD]...
[/TD]
[TD]...
[/TD]
[TD]...
[/TD]
[/TR]
[TR]
[TD]Item
[/TD]
[TD]...
[/TD]
[TD]...
[/TD]
[TD]...
[/TD]
[/TR]
[TR]
[TD]Price
[/TD]
[TD]100
[/TD]
[TD]...
[/TD]
[TD]...
[/TD]
[/TR]
</tbody>[/TABLE]
Basically the macro should load an Excel File which works as a dictionary (with column 1 containing English word, column 2 Spanish translation, column 3 Italian translation etc.). Then I want the macro to find and replace all the words/text in the original English Workbook (highlighted in blue in above table) with the Spanish or Italian Translation (from the dictionary file).
Basically it is a loop for each cell in the whole workbook (all worksheets) by comparing each cell content (text) with the dictionary and replacing the original content with the translation (from the dictionary).
Does anyone have an idea how to start??
I read a lot about the "Scripting.Dictionary" object, but I dont think this will work in my case as I have more than 2 columns in the dictionary (column 1: original English, colum 2: Spanish translation, column 3: Italian translation and so on).
Thank you very much in advance!!!
Best regards
Robert