PDA

View Full Version : Saving Autocorrect entries into different languages using VBA



VidarParry
09-16-2016, 11:43 PM
Hi all,
Another tricky one for you gurus out there! (This is also posted on MrExcel)

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.

Code:

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 , Display and Help languages both = English ]

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