0

I have test script (in XML) and I need translation (from Excel sheet). I am searching for words in English from that Excel sheet and then translating them to German, French and Italian. I have problem with French words.

IMAGE

I am using Macro in VBA. When I am finding the corresponding word in French for English one, I am using function Find. When I find French word in Excel sheet, I am just saving int into String.

CODE

If intChoice <> 0 Then

strPath = Application.FileDialog( _
msoFileDialogOpen).SelectedItems(1)

Dim strFolderPath As String
strFolderPath = Left(strPath, Len(strPath) - 4)
Set fGermanOutputFile = fso.CreateTextFile((strFolderPath & "_German.xml"))
Set fItalianOutputFile = fso.CreateTextFile((strFolderPath & "_Italian.xml"))
Set fFrenchOutputFile = fso.CreateTextFile((strFolderPath & "_French.xml"))

    Open strPath For Input As #1


Do Until EOF(1)
    Line Input #1, strLine

    intLastFoundChar = 0

    strGermanLine = ""
    strFrenchLine = ""
    strItalianLine = ""

    For intI = 0 To (UBound(ArrStrOpeningTags, 1) - 1)

        intFoundString = InStr(strLine, ArrStrOpeningTags(intI))
        If intFoundString <> 0 Then
            intI = 3
        End If

    Next intI

    If intFoundString <> 0 Then

        For intJ = 0 To (UBound(ArrStrParamsToReplace) - 1)


            strLine = Right(strLine, Len(strLine) - intLastFoundChar)

            strStringToLookFor = (ArrStrParamsToReplace(intJ) & "=""")

            intFoundString = InStr(1, strLine, strStringToLookFor, vbBinaryCompare)

            If intFoundString <> 0 Then
                intStringSplitIndex = (intFoundString + Len(strStringToLookFor))

                strStringToLookFor = Right(strLine, Len(strLine) - intStringSplitIndex + 1)


                strDummyString = Left(strLine, intStringSplitIndex - 1)
                strGermanLine = strGermanLine & strDummyString
                strFrenchLine = strFrenchLine & strDummyString
                strItalianLine = strItalianLine & strDummyString

                intLastFoundChar = intLastFoundChar + intStringSplitIndex


                intFoundString = InStr(strStringToLookFor, """")

                If intFoundString <> 0 Then

                    strStringToLookFor = Left(strStringToLookFor, intFoundString - 1)


                    Set rngFoundString = rngEnglishDictionary.Find(strStringToLookFor)


                    If (rngFoundString Is Nothing) Then
                        Debug.Print "String " & strStringToLookFor & " not found!"


                        strGermanLine = strGermanLine & strStringToLookFor & """"
                        strFrenchLine = strFrenchLine & strStringToLookFor & """"
                        strItalianLine = strItalianLine & strStringToLookFor & """"
                    Else

                        intWordToReplaceIndex = rngEnglishDictionary.Find(strStringToLookFor).Row - rngEnglishDictionary.Row + 1


                        strGermanLine = strGermanLine & rngGermanDictionary(intWordToReplaceIndex) & """"
                        strFrenchLine = strFrenchLine & rngFrenchDictionary(intWordToReplaceIndex) & """"
                        strItalianLine = strItalianLine & rngItalianDictionary(intWordToReplaceIndex) & """"
                    End If

                    intLastFoundChar = intLastFoundChar + Len(strStringToLookFor)
                End If
            End If

        Next intJ               

    Else

    strGermanLine = strLine
    strFrenchLine = strLine
    strItalianLine = strLine

    End If                       

    fGermanOutputFile.WriteLine (strGermanLine & vbCrLn)
    fFrenchOutputFile.WriteLine (strFrenchLine & vbCrLn)
    fItalianOutputFile.WriteLine (strItalianLine & vbCrLn)

    strGermanLine = ""
    strFrenchLine = ""
    strItalianLine = ""         
Loop
End If
Stephen Rauch
  • 47,830
  • 31
  • 106
  • 135
Martin Zemánek
  • 25
  • 5
  • Welcome to the site! Can you post the code? A good [introduction is here](http://stackoverflow.com/help/how-to-ask) – Jimmy Smith Feb 06 '17 at 14:46
  • Thank you very much... Because I am really lost.I am new to VBA and even this code was hard for me. Now I don't know how to deal with this encoding problem. And btw, how I can put IMAGE here directly? (One moderator change it in my first post before) – Martin Zemánek Feb 06 '17 at 15:14
  • Happy to help. Initial glance, where you are using rngFrenchDictionary would be the place to focus on? Is this dictionary calling any external components? windows-1552 is a default encoding on a lot of older components. Can you give an example of characters not coming out correctly? – Jimmy Smith Feb 06 '17 at 15:40
  • @JimmySmith Can you please open link on IMAGE I put in post? As new member I can only post links... There i example of wrong interpretation of words – Martin Zemánek Feb 06 '17 at 15:51
  • @JimmySmith rngFrenchDictionary is actually the .Range type from excel sheet, which is defined as G9:G1000 and when I know number of row (intWordToReplaceIndex) I am puting string from this cell to string strFrenchLine. Wrong word on screenshot is "Très élevée" – Martin Zemánek Feb 06 '17 at 15:59
  • I think I figured it out. I overlooked something that's got me in the past. See my answer and try that out. – Jimmy Smith Feb 06 '17 at 16:06

1 Answers1

1

By default, CreateTextFile creates the files in ASCII encoding. In order to have it save as UTF-8, we have to set this when creating the text files,

Set fGermanOutputFile = fso.CreateTextFile((strFolderPath & "_German.xml"), True, True)
Set fItalianOutputFile = fso.CreateTextFile((strFolderPath & "_Italian.xml"), True, True)
Set fFrenchOutputFile = fso.CreateTextFile((strFolderPath & "_French.xml"), True, True)

1st is your file name, 2nd argument is True for "overwrite", and 3rd argument is True to use "Unicode" versus "ASCII". According to this

Jimmy Smith
  • 2,452
  • 1
  • 16
  • 19