Need VBA solution for autotext replacement like Phrase Express or Robotype

I want a custom text expansion/autotext function for my Microsoft Office applications. I can't load outside programs such as PC Magazines Robotype, to my work computer because of IT policy. However, I do have VBA available.

I'd like to develop an autotext function with vba.  I know about the Word function for this task but I don't like how it's implemented.  I'd like a separate database for custom text expansion or boilerplate text expansion rather than having my custom stuff mixed in with the Word stuff.

I was thinking about an array of my custom abbreviations with replacements.  Better yet, the array would read from an Access table.

The trigger would be that when the spacebar is pressed after a defined autotext element/record, the replacement text would be typed immediately rather than completing a document and doing a find/replace.  This is how RoboType works.

I am willing to learn some VBA to do this, I need to know some general concepts as to how to get this done and then figure out the details as I go.
rreiss60Asked:
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.

Boyd (HiTechCoach) Trimmell, Microsoft Access MVPDesigner and DeveloperCommented:
I have done the same thing using this VBA code:

MS Access™ 97 / 2000 / 2007 Spell Checker / Dictionary Tool with Source Code

http://www.pcesoft.com/Access-Spell-Checker-Tool-Source-Code.html
You can download the source code for our MS Access™ Spell Checker for either the MS Access 97™, MS Access 2000™ or MS Access 2007™ versions for FREE. If you find the code useful, please use the Donate button below. Our Spell-Checker solves the problem of how to distribute the Spell-Checker ability in your applications (Access does not include a spell checker which runs in runtime mode. Our spell checker DOES run in runtime mode, giving your users a useful spell checker in your Access runtime distribution!). We offer a complete Access 97, Access 2000 or Access 2007 spell check solution, with source code!

The Access Team Blog also mentioned it here: http://blogs.office.com/b/microsoft-access/archive/2008/09/04/open-source-spell-check-for-the-runtime.aspx

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
Jacques Bourgeois (James Burger)PresidentCommented:
There is an AutoCorrect option in all Office applications that can do that.

It's in the Tools menu. Any changes you make to the list of AutoCorrect options is saved in a .acl file that is kept in the following directory on my Windows8 computer (probably at the same place since Vista): C:\Users\<username>\AppData\Roaming\Microsoft\Office.

This file can be distributed between computers and users.
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
for my Microsoft Office applications.
Do you mean for all your Office apps, or just for Access.

VBA works for specific applications, and for specific "documents" in those applications - that is, a VBA function I write in Acccess would work in that specific Access database. It would not function for other Access databases, unless I copied that code over to the second database. Similarly, it would also not work in an Excel  workbook, or a Word document, unless I copied that code over and set up that Workbook or Document to fire off that code based on some event.

In other words - VBA cannot run in a stand-alone mode. It must be run within the context of an application (after all, it's not known as Visual Basic for Applications for nothing).
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

rreiss60Author Commented:
Yes, I plan to use it with Microsoft applications.  I was hoping that the abbreviation and replacement text could reside in a table in Access and be called by the VBA running in Word.
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Word can connect into an Access database quite easily. Just include a reference to the DAO library, or the Microsoft Access Object library for 2007/2010, and use code like this to open the table:

Dim dbs As DAO.Database
Set dbs = OpenDatabase("Full path to your Access database")

If you do this when you open your Word document, then you'd have a connection to your Access database, and could use that "dbs" object to get the information from your table.

Of course, you'd have to do this for every document. You could create a Template, or modify the Normal template, to include this code. But it wouldn't work for document you receive from others, unless you copy/paste your "autotext" code into the relevant events for that document.
rreiss60Author Commented:
Thank you.
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 Access

From novice to tech pro — start learning today.