• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3148
  • Last Modified:

Change language to French in VBA

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
Joanne M. Orzech
Asked:
Joanne M. Orzech
  • 4
  • 4
  • 4
  • +1
5 Solutions
 
Erick37Commented:
You have to change the Language in Windows to French(Canadian)

Control Panel->Regional and Language Options->[Regional Options]
0
 
Joanne M. OrzechManager, Document Services CenterAuthor Commented:
Thanks Erick but is there any way to do that with VBA in Word?
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
Joanne M. OrzechManager, Document Services CenterAuthor Commented:
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
 
Éric MoreauSenior .Net ConsultantCommented:
Why don't you create a small function that will return the month name in french independently of Windows?
0
 
Erick37Commented:
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
 
Erick37Commented:
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
 
Joanne M. OrzechManager, Document Services CenterAuthor Commented:
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
 
Erick37Commented:
Thanks for reminding me!  Just in time :}
0
 
Joanne M. OrzechManager, Document Services CenterAuthor Commented:
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

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

  • 4
  • 4
  • 4
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now