Macro add autocorrect entry

Hi everyone,

I am in need of a macro that converts a document to autocorrect entries in microsoft word/excel.
A customer of mine has a system with some barcodes.. He scans the barcodes and automatically word replaces these barcodes (which are actually numbers) in the text he wants.

I have told him he could just copy .acl files from one computer to another, but he doesn't want to do this, he want to add a new xls-document (over 4000 records).
The xls-document contains 2 columns: 1 value to be replaced, 1 value which is the result of the replacement.

Example:

A001: Circle with diameter of 2cm
A002: Cube with diameter of 2cm

He used to use "smarttools autokorrekteur". It's basically a word-document with a macro which translates this way:

value|result
A001|Circle with diameter of 2 cm
AOO2|Cube with diameter of 2 cm

But the problem is he also has results of 1 page, so he just scans the barcode and a whole document appears with tables, tabs, enters, different font-size, etc..

If I use this macro and use a table, I get an erro message..
Now was my question if someone knew a way to use another tool or rewrite te macro or anything.. The macro document is included in this post ;-) And only in german, can't read it either :-P

500 points for the one who can provide me a solution!

Thanks!
SmartTools-AK-Sichern-Laden.doc
Silencer001Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

TommySzalapskiCommented:
It's really simple. You just need this line of code
AutoCorrect.Entries.Add Name:="A001", Value:="Circle with diameter of 2 cm"

There are two ways to do it. You could write code to parse the Excel sheet and add all the entries that way, or you could use Excel formulae. In the spreadsheet add a column with this formula:
="AutoCorrect.Entries.Add Name:="""&A1&""", Value:="""&B1&""""
Fill it down, then copy that column into a macro and run it. That's it.
See attached example.
autocorrect.xls
0
Silencer001Author Commented:
Thanks for your information TommySzalapski ;-)

I have already found this code, but this doesn't work for formatted text. My customer has pages of text that he want in the autocorrect. So A001 would be replaced with 1 whole page with tables, bold text,...

And how do you best paste this into a macro? Isn't there a simpler solution to just click a button and the macro runs automatically in column C for instance?

I have found this macro code for a word-document:

Thanks already for your reply! I hope you can help me a little bit more!
Sub MultiAutoCorrectGenerator()

Dim oDoc As Document
Dim i As Integer
Dim Wrong As Range
Dim Right As Range

Set oDoc = ActiveDocument
Selection.Tables(1).Cell(1, 1).Range.Select
Selection.Collapse
For i = 2 To oDoc.Tables(1).Rows.Count
    If oDoc.Tables(1).Rows(i).Cells(1).Range.Characters.Count > 1 Then
        Set Wrong = oDoc.Tables(1).Cell(i, 1).Range
        Wrong.End = Wrong.End - 1
        Set Right = oDoc.Tables(1).Cell(i, 2).Range
        Right.End = Right.End - 1
        AutoCorrect.Entries.Add Name:=Wrong, Value:=Right
    End If
Next i
End Sub

Open in new window

0
GrahamSkanRetiredCommented:
This will add a table as an Autocorrect entry
Sub FormattedAutocorrectEntry()
    Dim tbl As Table
    Set tbl = ActiveDocument.Tables(1)
    AutoCorrect.Entries.AddRichText "A001", tbl.Range
End Sub

Open in new window

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Silencer001Author Commented:
Thanks for your reply GrahamSkan,

But the thing is that it is not always a table or bold or italic.. And I think this method just makes a table from every entry.. Is there just a way to ackwnoledge the formatted text and it this text how it is displayed in the cell of excel?

So just add formatted text (if it is formatted) and otherwise clear text..

Thanks
0
GrahamSkanRetiredCommented:
I only tried to show you the method for adding formatted Autocorrect entry in Word.

All text has some formatting in Word, so you can't test for 'No formatting'. You could test for the default font name, size, bold, italic etc,

Do you want to create an entry for every cell in a table?
Sub FormattedAutocorrectEntry()
    Dim tbl As Word.Table
    Dim i As Integer
    Dim rng As Word.Range
    Dim cl As Word.Cell
    
    Set tbl = ActiveDocument.Tables(1)
    For Each cl In tbl.Range.Cells
        Set rng = cl.Range
        rng.MoveEnd wdCharacter, -1
        AutoCorrect.Entries.AddRichText Chr$(Asc("A") + cl.ColumnIndex - 1) & Format$(cl.RowIndex, "000"), rng
   Next cl
        
End Sub

Open in new window

0
Silencer001Author Commented:
Yes, I have enclosed a document for how I want it to add..
I want it to be possible to add A001 en A002 in this example possibilities.docx
0
GrahamSkanRetiredCommented:
Your document has a two column table with A001 and A002 in the first column.

I guess that you want to store the contents or the cells in column 2 under the name in column 1.


Try this.


Sub FormattedAutocorrectEntry()
    Dim tbl As Word.Table
    Dim i As Integer
    Dim rng As Word.Range
    Dim r As Integer
    Dim strName As String
    
    Set tbl = ActiveDocument.Tables(1)
    For r = 2 To tbl.Rows.Count
        Set rng = tbl.Cell(r, 1).Range
        rng.MoveEnd wdCharacter, -1
        strName = rng.Text
        Set rng = tbl.Cell(r, 2).Range
        rng.MoveEnd wdCharacter, -1
        strName = rng.Text
        AutoCorrect.Entries.AddRichText strName, rng
   Next r
        
End Sub

Open in new window

0
Silencer001Author Commented:
Yes indeed that is what I want.

So if I add this macro to the document, this will add the formatted text with the tables and everything?

Is this also the same macro if you want to use it in Excel?

Thank you for your reply, I will test this immediatly!
0
Silencer001Author Commented:
This is not working. The text is too large to add I guess..

I am getting the following error message (translated from dutch to english)

Error 5488 during execution:
The autocorrect-fragment can't be added, because it is too long
0
GrahamSkanRetiredCommented:
It looks as if you have a nested table. These are often problematic. I am not using 2003 at the moment, but I'll have another look when I can reboot into my installation which does have 2003.

The macro would not work with Excel. The object model is completely different.
0
Silencer001Author Commented:
Thank you so much GrahamSkan! Your help is much appreciated!!
0
hirstmgCommented:
Hi there
I'm not sure this will help but it might be worth a try. A little while back I created an AutoCorrect Manager. It runs off a two column table in Word. You can push the table contents to normal.dot for formatted content or to the language acl files for textual content. It seems to handle some complex table structures in the second table cell ok soa  page of content in cell two may process ok.
Now if you copy the excel columns to a Word document that might be sufficient. It's a paid product but there is a free trial with limited functionality (5 items max) but that should be sufficient to test to see if it will do the job.
It is at http://www.clockworksoftware.com.au/Products/AutoCorrectManager.aspx
I'll be interested to see if it performs in this case
Cheers
Michael
0
Silencer001Author Commented:
Wow damn it works like a charm, thank you soooo much hirstmg!! Whoohoo :D

But now I still have a question.. My customer has a really big list of autocorrects and what I learned from this program is that they are probably stored in normal.dot because of the formatted text.

If I backed up the regular list then a lot of autocorrect entries wouldn't be shown.


Is there a way to backup this normal.dot?

He is using word 2003 or earlier at the moment and want to move to word 2010

Thanks again!
0
Silencer001Author Commented:
I went to the following path "C:\Users\User\AppData\Roaming\Microsoft\Templates" and found the normal.dot, but it was empty...

Hope you can help me further and thanks again!
0
hirstmgCommented:
Hi there

Second thing first. Normal.dot looks empty or blank but don't let that fool you. It actually contains all sorts of stuff hidden beneath that blank surface. It has definitions for styles, all sorts of default settings including your default language, default margins, etc. It also has the formatted AutoCorrects hidden behind the scenes. So while the normal.dot document looks blank it actually contains a lot of useful information that documents access when you create or edit them.

And now the first one. The AutoCorrect Manager backs up all the AutoCorrects - the defaults and your personal ones - they all get combined into a document for the formatted ones from normal.dot and a document for each of the languages installed. So the defaults AND your customers personal ones will get backed up.

This is because of the way AutoCorrect works - when you install Word you get a default set in normal.dot (a few formatted ones) and the default text ones in mso1033.acl (if US English is what you have) of which there are over 900.
When you add your own AutoCorrects they are added to normal.dot or mso1033.acl. The AutoCorrect Manager picks up all AutoCorrects in normal.dot and in mso1033.acl and puts them into tables in Word. When backing up it doesn't distinguish between default and personal, it takes the lot.

However if you make a table with just your personal ones in it you can use the AutoCorrect Manager to merge (or overwrite) them into Word's AutoCorrect.

Note  - i mentiojned US English above. if you had French or German or any other language installed the ACM will back those up too.

I hope that makes it a bit clearer.

Cheers
Michael
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Silencer001Author Commented:
Hirstmg, perfectly explained! I have figured this out when I was using this application and also a backup macro from microsoft which you can find in this post.

Thanks for the explanation and for all the others for contributing and looking for macro's and writing them themselfs.
0
Silencer001Author Commented:
Hirstmg had the solution, but want to thank Grahamskan for his contribution! Thanks everyone!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Word

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.