+ Reply to Thread
Results 1 to 1 of 1

Synonyms translation in different languages

Hybrid View

  1. #1
    Registered User
    Join Date
    05-16-2016
    Location
    asd
    MS-Off Ver
    asd
    Posts
    1

    Question Synonyms translation in different languages

    Hello. I want to machine translate words, including all of their synonyms. I got the following code working with English to any language, but I can't seem to make it work with other languages (for example: German to English).


    Function TranslateCell(word As String, x As Integer, targetLanguage As String, sourceLang As String)
        Dim getParam As String, trans As String, translateFrom As String, translateTo As String
        translateFrom = sourceLang
        translateTo = targetLanguage
        Set objHTTP = CreateObject("MSXML2.ServerXMLHTTP")
        getParam = ConvertToGet(word)
        URL = "https://translate.google.com/m?hl=" & translateFrom & "&sl=" & translateFrom & "&tl=" & translateTo & "&ie=UTF-8&prev=_m&q=" & getParam
        objHTTP.Open "GET", URL, False
        objHTTP.setRequestHeader "User-Agent", "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)"
        objHTTP.send ("")
        If InStr(objHTTP.responseText, "div dir=""ltr""") > 0 Then
            trans = RegexExecute(objHTTP.responseText, "div[^""]*?""ltr"".*?>(.+?)</div>")
            Cells(x + 2, Columns.Count).End(xlToLeft).Offset(0, 1) = trans
        'Else
           ' MsgBox ("Error")
        End If
    End Function
     
    '----Used functions----
    Function ConvertToGet(val As String)
        val = Replace(val, " ", "+")
        val = Replace(val, vbNewLine, "+")
        val = Replace(val, "(", "%28")
        val = Replace(val, ")", "%29")
        ConvertToGet = val
    End Function
    Function Clean(val As String)
        val = Replace(val, "&quot;", """")
        val = Replace(val, "%2C", ",")
        val = Replace(val, "'", "'")
        Clean = val
    End Function
    Public Function RegexExecute(str As String, reg As String, _
                                 Optional matchIndex As Long, _
                                 Optional subMatchIndex As Long) As String
        On Error GoTo ErrHandl
        Set regex = CreateObject("VBScript.RegExp"): regex.Pattern = reg
        regex.Global = Not (matchIndex = 0 And subMatchIndex = 0) 'For efficiency
        If regex.Test(str) Then
            Set matches = regex.Execute(str)
            RegexExecute = matches(matchIndex).SubMatches(subMatchIndex)
            Exit Function
        End If
    ErrHandl:
        RegexExecute = CVErr(xlErrValue)
    End Function
    
    
    
    
    Sub Translation()
    '
    ' Macro1 Macro
    '
    ' Keyboard Shortcut: Ctrl+Shift+R
    '
    Dim MSWord As Object, oSynInfo As Object
    Dim vSynList As Variant
    Dim strWord As String
    Dim strFrom As String
    Dim strTo As String
    Dim word As String
    Dim targetLang As String
    Dim sourceLang As String
    Dim sourceL As String
    
    sourceL = InputBox("Choose the source language: http://www.bettersolutions.com/word/WRV283/LE215851411.htm")
    
    sourceLang = InputBox("Choose the language to translate from (Leave empty for Auto): https://msdn.microsoft.com/en-us/library/hh456380.aspx")
    
    targetLang = InputBox("Choose the language to translate to: https://msdn.microsoft.com/en-us/library/hh456380.aspx")
    If targetLang = "" Or targetLang = " " Then
    MsgBox ("Error, no language was inputed")
    End
    End If
    
    
     
     
    Set MSWord = CreateObject("Word.Application")
    Dim k As Integer
    Dim f As Integer
    f = 0
     
    Do Until IsEmpty(ActiveCell.Value)
    k = 0
    Do Until IsEmpty(ActiveCell.Value)
    ActiveCell.Offset(0, k).Select
     k = 0
    'Set New Word
    
    strWord = ActiveCell.Value
    Set oSynInfo = MSWord.SynonymInfo(word = strWord, LanguageID = sourceL) ------------/ This suppose to work with Other languages, but it doesn't 
    'Set oSynInfo = MSWord.SynonymInfo(strWord)   -------/ This works with English
    'Exclude small words
    If (Len(strWord) > 2) = True Then
    'If found synonyms
    If oSynInfo.Found = True Then
        For i1 = 1 To oSynInfo.MeaningCount
            vSynList = oSynInfo.SynonymList(i1)
            For i2 = 1 To UBound(vSynList)
                If InStr(vSynList(i2), " ") = 0 Then word = vSynList(i2)
                
     word = TranslateCell(word, f, targetLang, sourceLang)
            Next i2
        Next i1
        
    
    End If
    
    End If
    
    
     k = k + 1
     Loop
     f = f + 2
    If IsEmpty(ActiveCell.Value) Then ActiveSheet.Cells(1, 1).Select
    ActiveCell.Offset(f, 0).Select
     
     Loop
     
    MsgBox ("Done - Like a boss!")
    
    
    Set oSynInfo = Nothing
    Set MSWord = Nothing
    End Sub
    I was trying to send LanguageID = wdGerman, sourceLang = de, targetLang =en but it didn't work (in fact, it always returns untrue synonyms for some reason)

    Any ideas? I don't know how to code in VBA and most of what I got is copy-pasting and educated guesses
    Last edited by slavikhh; 05-24-2016 at 12:03 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Help. Extract all synonyms of a given word using VBA
    By robelozano in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-13-2015, 05:05 AM
  2. Dictionary of synonyms - Enhance without creating duplicate
    By wali in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 08-12-2015, 12:56 PM
  3. Help With Auto Generating Synonyms in a List
    By jahelmie in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 12-07-2013, 04:00 PM
  4. Enhance wordlist through synonyms in Col.D
    By wali in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-25-2008, 11:56 PM
  5. Replies: 1
    Last Post: 12-06-2006, 10:33 AM
  6. synonyms
    By SURESH in forum Excel General
    Replies: 3
    Last Post: 08-30-2005, 02:05 PM
  7. [SOLVED] synonyms
    By SURESH in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-30-2005, 01:05 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1