CODEWITHSUNDEEP
vbavbscriptms-word
Steve
Steve

Reputation: 579

VBScript, GetLocale, SetLocale - Other uses

Note: Contrary to assertions, this question is not a duplicate. My question is tagged as ms-word and the suggestions made point to an ms-excel answer, which in this case does not apply. Word VBA != Excel VBA.

I have a need where I have users in Germany generating English language Word 2016 documents but where the dates are coming through as European styled with German language month and day names. We need them to come through as English date styles and names.

I know of the Content Controls and Date Picker, though that isn't an option at this time (It may be in the future). Changing their local regional settings also isn't an option (obviously it's German). For reasons I won't go into here, we are restricted to .DOC format for legacy reasons.

Happened across the VBScript functions GetLocale and SetLocal. Works perfectly, in the context of VBScript...

Question is how might I be able to reference VBScript as a DLL and make use of that functionality in an Office VBA module? It's not built into Office VBA unfortunately.

I've tried referencing VBScript.dll from the SYsWow64 folder, but the only thing it exposes is VBScript_Global.GlobalObj and SetLocale, GetLocale aren't available (It is referenced in the latest documentation still, it isn't deprecated. https://msdn.microsoft.com/en-us/library/5xf99h19(v=vs.84).aspx).

Any help would be greatly appreciated.

Upvotes: 1

Views: 1668

Answers (1)

Steve
Steve

Reputation: 579

This was the answer, what I wrote.

Public Function printDateByLocale(inputDate As Date, inputlocale As String) As String
    Dim codeString As String
    Dim scriptControl As Object
        
    Set scriptControl = CreateObject("MSScriptControl.ScriptControl")
    
    codeString = "Function getDateByLocale(myDate, locale)" & vbCrLf & "SetLocale locale" & vbCrLf & "getDateByLocale = FormatDateTime(myDate, vbLongDate) End Function"
    
    With scriptControl
        .Language = "VBScript"
        .addCode codeString
        printDateByLocale = .Run("getDateByLocale", inputDate, inputlocale)
    End With
    
    Set scriptControl = Nothing
End Function

Upvotes: 2

Related Questions