1

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.

Steve
  • 549
  • 6
  • 21
  • 2
    Possible duplicate of [VBA equivalent to VBScript's 'SetLocale' function?](http://stackoverflow.com/questions/31785613/vba-equivalent-to-vbscripts-setlocale-function) – user692942 Feb 08 '17 at 20:03
  • That question and response refers to Excel VBA and the Evaluate function, which Word VBA does not feature. My question is tagged as ms-word Thank you. – Steve Feb 09 '17 at 16:48
  • In which case the answer is No, sorry. Seen as though you are using VBA you should have access to the Windows APIs which VBScript doesn't allow. There isn't much you can't do with the Windows API. – user692942 Feb 09 '17 at 16:55
  • 2
    In Word, the language used for dates should reflect the language formatting applied to the document. Most likely, given what you're seeing, this is German. The trick will be to format these documents as "English". This is, unfortunately, not as simple a process as it should be. You'll find some tips here: http://homepage.swissonline.ch/cindymeister/LangFmt.htm – Cindy Meister Feb 09 '17 at 18:22
  • Thank you Cindy. That may prove helpful, though it is a bit dated now. – Steve Feb 09 '17 at 19:50
  • @StevenMpls so is programming in Word. – user692942 Feb 10 '17 at 09:38

1 Answers1

2

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
Steve
  • 549
  • 6
  • 21