CODEWITHSUNDEEP
excelvbams-word
ferreimi
ferreimi

Reputation: 1

Display Calculated Date in French using VBA

I created a macro to calculate a date value then convert (for display on document) to French:

Sub FrenchFutureDate()
    Selection.TypeText Text:=Format(CDate(ActiveDocument.MailMerge.DataSource.DataFields("RREG_Registr_File_GSFTDateReceived").Value) + 30, "d, mmmm, yyyy")
    Selection.LanguageID = wdFrench
End Sub

NOTE: original / baseline date is being passed as a mailmerge value.

We would like to change the function to use the current date.

I swapped the mail merge field reference to date (see below).

Problem is the date no longer displays in French just English.

Sub FrenchFutureDate()          
    Selection.TypeText Text:=Format(Date + 30, "mmmm d, yyyy")
    Selection.LanguageID = wdFrench
End Sub

Upvotes: 0

Views: 727

Answers (3)

Gustav
Gustav

Reputation: 56026

You can use a function similar to this, just replace with the French month names and rename the function to, say, MonthNameFrench:

' Returns the English month name for the passed month number.
' Accepted numbers are 1 to 12. Other values will raise an error.
' If Abbreviate is True, the returned name is abbreviated.
'
' 2015-11-25. Gustav Brock, Cactus Data ApS, CPH.
'
Public Function MonthNameInvariant( _
    ByVal Month As Long, _
    Optional ByVal Abbreviate As Boolean) _
    As String
    
    Const AbbreviatedLength As Integer = 3
    
    Dim MonthName( _
        MinMonthValue To _
        MaxMonthValue)      As String
    Dim Name                As String
    
    If Not IsMonth(Month) Then
        Err.Raise DtError.dtInvalidProcedureCallOrArgument
        Exit Function
    End If
    
    ' Non-localized (invariant) month names.
    MonthName(1) = "January"
    MonthName(2) = "February"
    MonthName(3) = "March"
    MonthName(4) = "April"
    MonthName(5) = "May"
    MonthName(6) = "June"
    MonthName(7) = "July"
    MonthName(8) = "August"
    MonthName(9) = "September"
    MonthName(10) = "October"
    MonthName(11) = "November"
    MonthName(12) = "December"
    
    If Abbreviate = True Then
        Name = Left(MonthName(Month), AbbreviatedLength)
    Else
        Name = MonthName(Month)
    End If
    
    MonthNameInvariant = Name

End Function

It is taken from module DateText.bas in my project VBA.Date.

The constants can be found in module DateBase.bas.

Upvotes: 1

T.M.
T.M.

Reputation: 9948

As your question is also tagged as Excel you might consider to use the following function to get French month names:

Function mois()

  • a) Uses (an abbreviated brackets method of) formula evaluation to get a 1-based 2-dimensional array of all French month names (see formatting prefix [$-80C]) and
  • b) simply returns the month string indicated by the first argument indx, either in full length or abbreviated conform to system options (2nd argument abbrev).
Option Explicit

Function mois(ByVal indx As Long, Optional ByVal abbrev = False) As String
'Meth: Excel evaluation based on column sequence
'Purp: return French month name
'0) escape clause for invalid indices
    If indx > 12 Or indx < 1 Then Mois = "?": Exit Function
'a) get 1-based 1-dim array of french months name
    Dim months
    If abbrev Then
        months = [Text(Date(0,Column(A:L),1),"[$-80C]mmm")]
    Else    ' full length
        months = [Text(Date(0,Column(A:L),1),"[$-80C]mmmm")]
    End If
'b) return function result
    mois = months(indx)
End Function

Example call

    Debug.Print Mois(4)       ' ~~> avril
    Debug.Print Mois(4, True) ' ~~> avr.

Upvotes: 0

jonsson
jonsson

Reputation: 1301

As long as Word's date field formatting supports the language you want to use (French in your case) you can do it this way:

Sub InsertDatePlus30FR()
Dim f As Word.Field
Set f = Selection.Fields.Add(Selection.Range, WdFieldType.wdFieldQuote, """" & Format(Date + 30, "YYYY-MM-DD") & """ \@ ""MMMM, D, YYYY""", False)
f.Code.LanguageID = wdFrench
f.Update
' If you want to remove the field, uncomment the following line
' f.Unlink
Set f = Nothing
End Sub

(NB, when you construct a date in this way in Word, Word always interprets the "NNNN-NN-NN" format as YYYY-MM-DD, never YYYY-DD-MM - assuming it's a date).

Upvotes: 0

Related Questions