Saving Autocorrect entries into different languages using VBA

[Steve Parry]

Hi all,
Another tricky one for you gurus out there!

The requirement is to have a number of predefined, user installable, corporate autocorrect entries available across a suite of Word documents (and preferably across all Excel and Word documents across the users systems, regardless of language settings).

Word documents are mostly set to English (Australia), as is the Windows language.

I have a macro written in Excel 2016 VBA that reads the corporate autocorrect entries from “Sheet1” and loads them into the application autocorrect ACL file. In Sheet1 Column A has the abbreviation and column B has the replacement text.

Sub UpdateAutocorrect ()
ver = "19 September 2016"
Dim sh1 As Worksheet, sh2 As Worksheet
Dim lastrow As Long
Dim shorttext As String, LongText As String
    Set sh1 = Worksheets("Sheet1")
    Set sh2 = Worksheets("Buttons")
    lastrow = sh1.Cells(Rows.Count, "A").End(xlUp).Row
    For RowNum = 1 To lastrow
        shorttext = sh1.Cells(RowNum, 1)
        LongText = sh1.Cells(RowNum, 2)
    On Error Resume Next ' because next line always gives an error 1004 even though it works
        Application.AutoCorrect.AddReplacement shorttext, LongText
    On Error GoTo 0
    Next RowNum
    MsgBox ("All new Autocorrect entries have been added to your Office Autocorrect dictionary." & vbcrlf & _
             " Version " & ver & " installed.")
End Sub

The issue is that Excel saves them into the MSO1033.acl file (WdEnglishUS) whereas Word uses MSO2057.acl (wdEnglishUK) for its autocorrect.
This in itself is bizarre because the language settings in both word and Excel are identical (see screenshot below) and wdEnglishUK is not anywhere to be seen!
[?How do I upload screenshot to the post?. Editing Language = English (Australia <default>, Display and Help languages both = English <default>]

Anyway, my thinking is to repeat the adding of the autocorrect entries in all of the various language files (US (1033), UK (2057) and AUS (3081)) to cover all bases, but I can’t find a way in Excel VBA to change the language or trick Excel into using the different ACL files.

Any assistance greatly appreciated as always!

Many thanks
…Steve</default></default>

 

[Steve Parry]

I've also posted this on VBAExpress forum.

 

[Steve Parry]

@Rob Henry. I can't PM you a reply as you're PM box is full.
Hi Rob.
Unfortunately we didn't fix it for Excel. Ended up with just an MSWord solution.
Cheers
...Steve

 

[Rob Henry]

Thanks Steve,
I only had the message to you in my sent box, and I guess that made my box full!
Sorry to hear you weren't able to find a solution that worked in Excel. I'm interested in a solution that works in all Office applications. I have some ideas that I will follow up on and will post here if I eventually find a solution.
Rob