"Mirror" an Excel Workbook in another Language

**katkth7533** · 2014-08-21, 04:28 PM

I am trying to find the simplest solution for this problem. I have a workbook that is a standard form that uses links to other workbooks to populate and update it. The worksheet (Called "Pipeline") is locked and can not be edited; only viewed and printed. It was requested that the user of the worksheet, which is in English, have the ability "push a button" and the form convert to French. After several days of head scratching, hair pulling, and near tears, I can not come up with anything. My thought would be to create another workbook that mirrors the English version but have it defaulted to French. The user will push the button and it will open this new workbook. I will change all the headers and standard fields to French and only the imported/ linked data will change to French. How do I do that?? Is there a way to save or set this new workbook (we will call it "French Pipeline") to default to French language? I can not for the life of me find an automated solution for this.

It seems that if I change the language settings in Excel, it changes it for ALL workbooks and ALL of MS Office programs. Any ideas or vba code to do this?

**Bob Phillips** · 2014-08-21, 04:51 PM

You mention form, which I read as userform, but say the workbook is read-only. What exactly do they want to see in French? Labels and such, understood, but anything else?

**katkth7533** · 2014-08-21, 06:45 PM

Pipeline.pdf
Yes - the "form" or "report" is read only. It draws the information from links to other documents where I have multiple user forms set up. I have attached a pdf of the worksheet in question.

Everything that is highlighted in yellow are the standard headers. I will have the new French worksheet already set up with these in French. Everything outlined in green or red are the links. I don't need the links outlined in green translated as they will mostly be proper names. The bar graphs in the middle of the document are fine as is. I am looking to have the links/ data that is outlined in red translated. Those links are coming from multiple files/ user forms (up to 10 different workbooks). I plan would be to have a macro attached to a command button on the form that the user enables and it will open the French version of the worksheet.

**Bob Phillips** · 2014-08-21, 11:14 PM

You need to call into an online translator I would think. I found this online, which calls Google translate. I admit, it didn't work for me, but try it and see

Code:

Sub test()
    MsgBox TranslateText("What is this in French", "fr")
End Sub


Function TranslateText( _
    ByVal Source As String, _
    ByVal Language As String) As String
Dim IE As Object
Dim inputstring As String
Dim outputstring As String
Dim result_data As String
Dim CLEAN_DATA As Variant
Dim i As Long


    Set IE = CreateObject("InternetExplorer.application")


    inputstring = "auto"
    
    outputstring = Language


    IE.Visible = False
    IE.navigate "http://translate.google.com/#" & inputstring & "/" & outputstring & "/" & Source


    Do Until IE.ReadyState = 4
        DoEvents
    Loop


    Application.Wait (Now + TimeValue("0:00:5"))


    Do Until IE.ReadyState = 4
        DoEvents
    Loop


    CLEAN_DATA = Split(Application.Substitute(IE.Document.getElementById("result_box").innerHTML, "</SPAN>", ""), "<")


    For i = LBound(CLEAN_DATA) To UBound(CLEAN_DATA)
        result_data = result_data & Right(CLEAN_DATA(i), Len(CLEAN_DATA(i)) - InStr(CLEAN_DATA(i), ">"))
    Next


    IE.Quit
    TranslateText = result_data
End Function