I need to develop a macro which works with relative reference for copying output from Excel Translator (specifically from English to Hindi as default) and pasting it in the cell where cursor is. I tried my own but it gives me error (similar to ?!???????) and then I cannot undo. Appreciate any help!
[Solved] Translator with Relative Reference
- Thread starter VVK
- Start date
Hello VVK
Welcome to ExcelDemy Forum. You wanted a macro that will work with relative reference for coping output from Excel Translator. You are right about the error mentioned error. I have studied the situation, but I cannot access the Translate command under the Language group in the Review tab using VBA.
However, I am delighted to inform you that I have developed another sub-procedure that will fulfil your requirement without using the Translate command. All you need to do is put the English text in cell A2. Later, select the cell where you want to see your output. Finally, after running the sub-procedure, you will get the intended output (Hindi text). It is yet to be mentioned that I am using the Google Translator API.
While investigating your problem, I found an article that will help you.
Excel VBA Sub-procedure:
Code:
Sub TranslateAndPaste()
Dim text_str As String, src_lang As String, trgt_lang As String
Dim s1 As Long, s2 As Long
Dim url_str As String, rsp As String
Const rslt_div As String = "<div class=""result-container"">"
Const url_temp_src As String = "https://translate.google.com/m?hl=[from]&sl=[from]&tl=[to]&ie=UTF-8&prev=_m&q="
src_lang = "en"
trgt_lang = "hi"
text_str = ThisWorkbook.Sheets("Sheet1").Range("A2").Value
url_str = url_temp_src & WorksheetFunction.EncodeURL(text_str)
url_str = Replace(url_str, "[to]", trgt_lang)
url_str = Replace(url_str, "[from]", src_lang)
rsp = WorksheetFunction.WebService(url_str)
s1 = InStr(rsp, rslt_div)
If s1 Then
s1 = s1 + Len(rslt_div)
s2 = InStr(s1, rsp, "</div>")
If s2 > s1 Then
ActiveCell.Value = Mid$(rsp, s1, s2 - s1)
End If
End If
End SubOUTPUT:
I am also attaching the solution workbook to help you understand better. Good luck!
Regards
Lutfor Rahman Shimanto
I have attached a sample wherein a few lines of the particular schedule have been translated while remaining need to be translated. The workbook is mostly in similar fashion across sheets / Schedules. So, the program effectively need sot translate at the same location. Would be super if you can have a see! Thanks
Dear VVK
Thanks for reaching out again. It is my pleasure that you like the idea I previously provided to fulfil your requirements. You are now interested in whether the provided Excel VBA sub-procedure can support making the Source Language Text dynamic.
I am delighted to inform you that I can definitely develop such an Excel VBA sub-procedure. Assume you want to select a cell that contains the English language text. After running an Excel VBA sub-procedure, you want to be able to get the translated Hindi language text within the selected cell.
Currently, I am developing the proposed sub-procedure. Meanwhile, stay with ExcelDemy Forum.
Regards
Lutfor Rahman Shimanto
Dear VVK
I am happy to inform you that I have succeeded in developing the proposed Excel VBA sub-procedure. To be specific, the sub-procedure I am going to present is more convenient than the proposal. It is yet to be mentioned that the sub-procedure can now work with the selected range. You can select multiple cells that contain the English text. Later, after running the sub-procedure, the selected range will contain the intended Hindi text.
Excel VBA Sub-procedure (Selected Range):
Code:
Sub TranslateAndReplaceInSelectedRange()
Dim src_lang As String, trgt_lang As String
Dim selectedRange As Range
Dim cell As Range
Dim url_str As String, rsp As String
Const rslt_div As String = "<div class=""result-container"">"
Const url_temp_src As String = "https://translate.google.com/m?hl=[from]&sl=[from]&tl=[to]&ie=UTF-8&prev=_m&q="
src_lang = "en"
trgt_lang = "hi"
Set selectedRange = Selection
For Each cell In selectedRange
If cell.Value <> "" Then
text_str = cell.Value
url_str = url_temp_src & WorksheetFunction.EncodeURL(text_str)
url_str = Replace(url_str, "[to]", trgt_lang)
url_str = Replace(url_str, "[from]", src_lang)
rsp = WorksheetFunction.WebService(url_str)
s1 = InStr(rsp, rslt_div)
If s1 Then
s1 = s1 + Len(rslt_div)
s2 = InStr(s1, rsp, "</div>")
If s2 > s1 Then
cell.Value = Mid$(rsp, s1, s2 - s1)
End If
End If
End If
Next cell
End SubOUTPUT:
Another Scenario: Suppose you want to translate all the English text within an active sheet into the Hindi text. In this scenario, you can run the following Excel VBA sub-procedure.
Excel VBA Sub-procedure (For Used Range):
Code:
Sub TranslateAndReplaceInUsedRange()
Dim src_lang As String, trgt_lang As String
Dim cell As Range
Dim url_str As String, rsp As String
Const rslt_div As String = "<div class=""result-container"">"
Const url_temp_src As String = "https://translate.google.com/m?hl=[from]&sl=[from]&tl=[to]&ie=UTF-8&prev=_m&q="
src_lang = "en"
trgt_lang = "hi"
Set selectedRange = ActiveSheet.UsedRange
For Each cell In selectedRange
If cell.Value <> "" Then
text_str = cell.Value
url_str = url_temp_src & WorksheetFunction.EncodeURL(text_str)
url_str = Replace(url_str, "[to]", trgt_lang)
url_str = Replace(url_str, "[from]", src_lang)
rsp = WorksheetFunction.WebService(url_str)
s1 = InStr(rsp, rslt_div)
If s1 Then
s1 = s1 + Len(rslt_div)
s2 = InStr(s1, rsp, "</div>")
If s2 > s1 Then
cell.Value = Mid$(rsp, s1, s2 - s1)
End If
End If
End If
Next cell
End SubThings to Keep in Mind:
- Before running the Excel VBA Sub-procedure for Used Range, ensure a spontaneous internet connection. Otherwise, you may get an error.
- Running the Excel VBA Sub-procedure for Used Range may take some time based on the total range used in the active sheet.
Regards
Lutfor Rahman Shimanto
Last edited: