Solved

Change language to French in VBA

Posted on 2004-10-27
2,882 Views
Last Modified: 2007-12-19
I've created a template in English.  Now I need to create an identical one in French (Canadian).  How do I change the language in the VB script to show - for example - the dates in French?

This is the code I'm using and even though I set the default language in the template to French Canadian, the date October shows up rather than Octobre.

   frmXXX.TextBox1.Text = "Le " & Format(VBA.Date, "dd Mmmm, yyyy")

As always, many thanks in advance experts :)



0
Question by:Joanne M. Orzech
    13 Comments
     
    LVL 32

    Accepted Solution

    by:
    You have to change the Language in Windows to French(Canadian)

    Control Panel->Regional and Language Options->[Regional Options]
    0
     
    LVL 37

    Author Comment

    by:Joanne M. Orzech
    Thanks Erick but is there any way to do that with VBA in Word?
    0
     
    LVL 48

    Assisted Solution

    by:Mikal613
    0
     
    LVL 48

    Expert Comment

    by:Mikal613
    0
     
    LVL 48

    Expert Comment

    by:Mikal613
    0
     
    LVL 48

    Expert Comment

    by:Mikal613
    0
     
    LVL 37

    Author Comment

    by:Joanne M. Orzech
    So write to the registry and then do I change it back?  It's for a high ranking public official and I want to get it right.  Many thanks for your responses.
    0
     
    LVL 69

    Assisted Solution

    by:Éric Moreau
    Why don't you create a small function that will return the month name in french independently of Windows?
    0
     
    LVL 32

    Assisted Solution

    by:Erick37
    Just what I was going to suggest...

    Just hardcode the months so as to not rely on any system or locale settings:

    Option Explicit

    Dim Months(12) As String


    Private Sub CommandButton1_Click()

    TextBox1.Text = "Le " & _
                    Format(VBA.Date, "dd ") & _
                    Months(DatePart("M", VBA.Date)) & _
                    Format(VBA.Date, ", yyyy")

    End Sub



    Private Sub UserForm_Click()

    End Sub

    Private Sub UserForm_Initialize()
       
        Months(1) = "Janvier"
        Months(2) = "Février"
        Months(3) = "Mars"
        Months(4) = "Avril"
        Months(5) = "Mai"
        Months(6) = "Juin"
        Months(7) = "Juillet"
        Months(8) = "Août"
        Months(9) = "Septembre"
        Months(10) = "Octobre"
        Months(11) = "Novembre"
        Months(12) = "Décembre"
       
    End Sub
    0
     
    LVL 32

    Assisted Solution

    by:Erick37
    Or simply

    Option Explicit

    Private Sub CommandButton1_Click()

        TextBox1.Text = FrenchDate(VBA.Date)

    End Sub


    Private Function FrenchDate(dtDate As Date) As String
        Dim sMonths() As String
        Dim sData As String
        sData = ".Janvier.Février.Mars.Avril.Mai.Juin.Juillet.Août.Septembre.Octobre.Novembre.Décembre"
        sMonths = Split(sData, ".")
        FrenchDate = Format(dtDate, "dd ") & _
                    sMonths(DatePart("M", dtDate)) & _
                    Format(dtDate, ", yyyy")
    End Function
    0
     
    LVL 37

    Author Comment

    by:Joanne M. Orzech
    Very cool!  I will try it later and get back probably tomorrow....got to watch the lunar eclipse right now !  Blood red full moon!  Very rare :)  And it's almost Halloween too!

    Thanks much!!
    0
     
    LVL 32

    Expert Comment

    by:Erick37
    Thanks for reminding me!  Just in time :}
    0
     
    LVL 37

    Author Comment

    by:Joanne M. Orzech
    Thanks all for your comments and suggestions.  Everything in the template was working great as I said, except when I used the command:

        frmLtr.TextBox.Text = Format(VBA.Date, "mmmm d, yyyy")

    It still put the month in as English, even though I had changed the settings in Control Panel, and set the default language to French.

    So I ended up doing this:

        Selection.TypeText Text:="Le "
        Selection.InsertDateTime DateTimeFormat:="d MMMM yyyy", InsertAsField:= _
            False, DateLanguage:=wdFrench(Canada), CalendarType:=wdCalendarWestern, _
            InsertAsFullWidth:=False

    I appreciate all your assistance and am sure I'll be back for more.

    Best regards to all.
    JOrzech
    0

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Article by: Martin
    Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
    This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
    As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
    Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…

    913 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now