[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Custom Dictionary or add words to a dictionary with VBA

Posted on 2011-10-31
43
Medium Priority
?
1,634 Views
Last Modified: 2012-05-12
Hello

I Have a Addin that copies the contents of a textbox (Or a InkEdit box) to a new worksheet then spell checks.
As this will be used over multiple machines is there anyway to have a custom dictionary in the excel addin - Or add words to the dictionary via VBA then remove them when the spellcheck is finished?

There are a number of common things like bx (Box), rl (Roll) that I need to exclude from being checked.

Thanks
0
Comment
Question by:p-plater
  • 27
  • 16
43 Comments
 
LVL 42

Expert Comment

by:dlmille
ID: 37059785
You'll want to explore custom dictionaries:  http://office.microsoft.com/en-us/excel-help/create-a-custom-dictionary-HP005198530.aspx

There may be a way to make your custom dictionary "land" on a shared drive, but I haven't found documentation with respect to whether Office would manage that like a multi-user database.

You'll need to copy the CUSTOM.DIC to a shared drive somewhere, and rename it, say MYDICT.DIC

Try this - create a custom dictionary (for Excel 2007+ Options->Proofing->Custom Dictionaries and add MYDICT.DIC, making it the top priority dictionary).  Then, on the other machines, add MYDICT.DIC in the same fashion.

I just did a quick test and was able to add something to the custom dictionary, then see it not flag an error on another machine using the same dictionary.  I've not seen documentation on actually SHARING a custom dictionary so performance would really depend on whether Excel read in the dictionary each time you did a spellcheck, or whether it was read in only the first time.

It might be possible to modify the prior code question to have it refresh the dictionary based on what's there at that point in time.

Give this a shot and see if you get similar results with the version you're running.

Dave
0
 
LVL 42

Expert Comment

by:dlmille
ID: 37059846
Let's see if anyone else contributes, here.  I could not find anything (aside from 3rd party tools) on sharing custom dictionaries.  However, we could modify your original code to have Excel refresh its view on the custom dictionary at the time of messagebox load.  Not sure the ramifications of making changes - perhaps it would say the file was locked at the time of "modify" if coincident with someone else's modification.  If we get no other contributions that are easier than this, I can take a look at modifying your code to do this.

I hope there's something else however that's better than this solution, re: multi-user custom dictionary.  

Your thoughts?

Cheers,

Dave
0
 

Author Comment

by:p-plater
ID: 37059856
Can I add words to the workstations dictionary BEFORE the spell check runs then REMOVE them after the check is complete?
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 42

Expert Comment

by:dlmille
ID: 37059918
You can probably programmatically add a DICTIONARY from a shared drive that your application would use as a supplimental dictionary, and remove it based on an event (e.g., like the file being closed).  So, if you're less concerned about muliple users needing to add to this dictionary and more concerned that they use a supplimental dictionary that's common to your app, then its probably a doable thing.

Dave
0
 
LVL 42

Expert Comment

by:dlmille
ID: 37059924
The other alternative might be to have that list of words in a hidden sheet that are added to a temporary dictionary on workbook open, and removed afterward.
0
 

Author Comment

by:p-plater
ID: 37059939
The other alternative might be to have that list of words in a hidden sheet that are added to a temporary dictionary on workbook open, and removed afterward.

That's it!!  -  Now How can I do it?
0
 
LVL 42

Accepted Solution

by:
dlmille earned 2000 total points
ID: 37060189
Just a note - remember that the points that you tee up for a question is an indication of importance, timliness/urgency needed, and sophistication.  Also, you don't have a POINTS budget - you can allocate what you deem fit according to the guidelines of E-E and (again) setting relevance/urgency for your question.  By understanding this, you can perhaps attract more experts to your questions, based on need/urgency.

Ok - on to the solution!  This was a bit of work (mostly testing it out), though not that many lines of code, lol - but I had some starting code to go with it.  

I think there's enough here to demonstrate the concept and you should be able to integrate into your app.

What does it do?  On workbook Open - it creates a temporary custom dictionary and loads it with the words in column A of the DictAdd tab.  On workbook Close - it removes the temporary custom dictionary and deletes the underlying file.

After opening the file, you should be able to go to Excel Options->Proofing->Custom Dictionaries and see that MYDICT.DIC exists, and you can review the few words added from the demo.

Here's the Code:

 
Sub removeFromDict()
Dim wkb As Workbook
Dim wks As Worksheet
Dim o_WordApp As Object
Dim o_ActCustDict As Object
Dim sTmpDict As String
Dim dic As Object
Dim sTmpFname As String

    Set wkb = ThisWorkbook
    Set wks = wkb.Sheets("DictAdd")
    
    Set o_WordApp = CreateObject("Word.Application")
    
    sTmpDict = wks.Range("A1").Value

    For Each dic In o_WordApp.Application.CustomDictionaries
        If dic.Name = sTmpDict Then
            dic.Delete 'remove temporary dictionary
        End If
        
    Next dic
    
    On Error Resume Next
    Kill sTmpDict 'removes temporary dictionary file
    On Error GoTo 0
    
    o_WordApp.Quit
End Sub
'Source: Inspiration from Ken Was:  http://www.mrexcel.com/forum/showthread.php?t=203048
'http://www.ozgrid.com/forum/showthread.php?t=157308
'Below routine modified to add new dictionary, set it as the active dictionary, then copy new words to it as the current custom dictionary
Sub addToDict()
Dim wkb As Workbook
Dim wks As Worksheet
Dim o_WordApp As Object
Dim o_ActCustDict As Object
Dim r_MyCell As Range, r_MyRng As Range
Dim s_MyWord$, s_ActCustDictNm$, s_MyMsg$
Dim l_LastRow&, l_StartRow&, n&
Dim sDict As String, rc As Boolean
Dim sTmpDict As String, chkError As Variant
Dim i As Long

     Set wkb = ThisWorkbook
     Set wks = wkb.Sheets("DictAdd")
     
     '********************************************************************
     'The Row that the"Add to Dictionary" Word List Starts in!
    l_StartRow = 2
     '********************************************************************
     
    l_LastRow = wks.Range("A" & Rows.Count).End(xlUp).Row
    Set r_MyRng = wks.Range("A" & l_StartRow & ":A" & l_LastRow)
    Set o_WordApp = CreateObject("Word.Application")
    
    On Error Resume Next
    Set o_ActCustDict = o_WordApp.Application.ActiveCustomDictionary
    chkError = o_ActCustDict.Name
    If Err.Number <> 0 Then
        Set o_ActCustDict = o_WordApp.Application.CustomDictionaries(1) 'Should be CUSTOM.DIC
    Else
        Set o_ActCustDict = o_WordApp.Application.ActiveCustomDictionary
    End If
    On Error GoTo 0
    
    s_ActCustDictNm = o_ActCustDict.Name
    
    'get path of current custom dictionary on local machine
    sDict = o_ActCustDict.Name

    'save current custom dictionary to sheet
    wks.Range("C1").Value = sDict

    'create path for new temporary dictionary on local machine
    sTmpDict = o_ActCustDict.Path & "\" & wks.Range("A1").Value
    
    'now make sure word adds go to the new temporary custom dictionary
    Set o_ActCustDict = o_WordApp.Application.CustomDictionaries.Add(Filename:=sTmpDict) 'should get created on the fly, if it doesn't exist
    s_ActCustDictNm = o_ActCustDict.Name
    
    'now add words to it
    
    For Each r_MyCell In r_MyRng
        If Not IsEmpty(s_MyWord) Then
            s_MyWord = r_MyCell.Value2
            rc = Application.CheckSpelling(s_MyWord, customdictionary:=o_ActCustDict.Name, IgnoreUppercase:=False)
            If Not rc Then
                n = n + 1
                AddWordToDict sTmpDict, s_MyWord
                s_MyMsg = s_MyMsg & s_MyWord & ", "
            End If
        End If
    Next r_MyCell


    o_WordApp.Quit
     
End Sub
 
Sub AddWordToDict(sDict As String, textVar As String)
    Dim FSO         As Object
    Dim MyFileRead  As Object
    Dim cdWord As String
    Dim wordFound As Boolean
     
    Set FSO = CreateObject("Scripting.FileSystemObject")
     
     'object.OpenTextFile(filename[, iomode[, create[, format]]])
     'ForReading      = 1     ; Open a file for reading only. You can't write to this file.
     'ForWriting      = 2     ; Open a file for writing.
     'ForAppending    = 8     ; Open a file and write to the end of the file.
     
    Set MyFileRead = FSO.OpenTextFile(sDict, 1, True, -1) 'Unicode=True'
     
    Do While Not MyFileRead.AtEndOfStream
        cdWord = MyFileRead.ReadLine
        If cdWord = textVar Then wordFound = True: Exit Do
    Loop
    MyFileRead.Close
    Set MyFileRead = FSO.OpenTextFile(sDict, 8, True, -1) 'Unicode=True'
     
    If Not wordFound Then MyFileRead.WriteLine textVar '("MacariusB")
     
    MyFileRead.Close
End Sub

Open in new window


And in ThisWorkbook module:
   
Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Call removeFromDict
End Sub

Private Sub Workbook_Open()
    Call addToDict
End Sub

Open in new window


See attached workbook solution.

Cheers,

Dave
spchkAddCustDict-r1.xls
0
 

Author Comment

by:p-plater
ID: 37103251
If I put the "Call addtoDict"  in the Open Workbook this works but I can't put it in a sub that initializes a Userform.

Public Sub Show_Userform
     Call addtoDict
     Userform1.show
end sub

It seems that Excel is not picking up the new Dictionary until the userform closes
Any way arround this?
0
 
LVL 42

Expert Comment

by:dlmille
ID: 37103550
I'm researching now.  Can you tell me the story about why the dictionary is added "in process" as opposed to "on open?"

MAYBE a DoEvents after Call addToDict??

Dave
0
 
LVL 42

Expert Comment

by:dlmille
ID: 37103617
I tried it two ways - like you did, and as part of UserForm initialize - with the userform up, I looked at the path: C:\Users\YOURUSERNAME\AppData\Roaming\Microsoft\UProof

and MYDICT.DIC was there, and the data was populated in it.

Let me ask you - did you try spellcheck, failed, then ran the userform again and tried spellcheck?  Did you ever get spellcheck to work?

Perhaps its all getting created, but for some reason its not active.  We may need another command...

Dave
0
 
LVL 42

Expert Comment

by:dlmille
ID: 37103620
Did it ever work for you on workbook open?  I'm trying to get that to work again and am getting some mixed results.
0
 
LVL 42

Expert Comment

by:dlmille
ID: 37103913
I believe I have it sorted, but needs some cleanup.  I've got to run to some meetings so will try to handle before tomorrow.
0
 
LVL 42

Expert Comment

by:dlmille
ID: 37105281
I found code that will work that will highlight misspelled words in the userform, for correction (rather than popup the dialog).  Do you want your users to be able to add words to the dictionary?  This could be a preference.

I bring that up, because there's a flaw with this code that we have right now. The dictionary never gets used unless you go to the Excel Options and click Proofing->Custom dictionarys, or you close Excel and reopen, after having added the dictionary.

I'm still working on it but wanted to offer a solution you might find as a preference (I'm still going to dog this - what I think is a MS bug).

Dave
0
 

Author Comment

by:p-plater
ID: 37110481
It works fine on workbook open.
Yes I want the Users to be able to add to the Dictionary.

I have edited the code slightly.

addToDict now deletes ALL the Dictionarys in CustomDictionarys THEN adds the Temp Dictionary.
This makes and words added go to the Temp Dictionary.

The Full story?
The VBA is a Addin that is on a network location that is Loaded on each Workstation.
When the Userform is Initialised I need a different custom dictionary depending on the Caption of the Userform.
When The userform is Unloaded I need the Std Dictionary Back (But the Addin and excel are still open even if invisible for fast access)

When I have it sorted that far I am going to store the words in a SQL database then make the Temp Dictionary on the Fly from words selected from a Select String depending on the Userform Caption.
0
 
LVL 42

Expert Comment

by:dlmille
ID: 37110842
ok - Well I always got addToDict to work as part of starting up the Userform.  Must be the add-in you just mentioned that was part of the wrinkle.

Do you have your solution, or do you want me to keep working it?  It would be useful if you would either provide your addin, or enough of the code so I can replicate what's going on and suggest rectification.

Cheers,

Dave
0
 

Author Comment

by:p-plater
ID: 37111157
ok

Attached is the addin File which I have stripped as much as I can.

Go to the mSetup Mudule and Run the DicTest sub.
A Userform should open Press the New Button.
Enter some words into the Description Box and the n Press the Spell Checker Button to spell check the Description.

It does not use the words from the Temp Dict.
Any words Added are sent to the Std custom Dict.

IF when the spell check box is open you press the options button it shows the temp dict as the default and the std one off THEN it uses the temp dict and added words go to the temp dict correctly!

Any Idea's?
Solve-Web.xlam
0
 
LVL 42

Expert Comment

by:dlmille
ID: 37111991
The REAL problem as I see it is dynamically adding a dictionary and then immediately using it (without changing proofing options as you say - when you do that via spell check button, THEN it works).

This code makes the Web.DIC the active dictionary - note, I did the call to addToDict THEN executed the SHOW command via the DicTest() sub, as opposed to doing it in the Userform_Show routine.

It might perform this way if you make it part of the Userform_Initialize() routine.

I did add some value - I modified the last few lines of the addToDict() routine to make the Web.DIC the active dictionary, and on the removeDict() routine, I properly deleted the WEB.DIC file and made the previously active dictionary the active dictionary.  Also,  I commented out the "onTop" routine for the moment, so I could go to the Uproof directory to see yes Web.DIC was created properly and contains the words.

So, everything is working correctly in this version (re: dictionary gets created, is added to the collection, and made active - all before the userform starts).  However, the spellchecker does NOT use the WEB.DIC until you either close and reopen Excel OR you go to Proofing Options.  I have logged a question on E-E about this "bug".  Unless, perhaps it works for you and not me - but from your last sentence, it appears you're seeing the same behavior.

Attached = 2 files -your add-in amended - note, I start it on workbook_open, so have Excel up and running before you start.  Also, my current working file on the issue I'm talking about - making the dictionary actually work after its created - I believe Excel may need to re-open the dictionary to actually use the new one - trying to figure out how to do that or exactly what step we're missing.  

From the TIPS I found on the WEB, they don't seem to have the problem, or never tested it in the dynamic way we're doing it.

PS - recall we have a fallback we can discuss if this fails - and that's to use the tip that DOES do the spellchecking - word by word, but only highlights the word int he text box that is in error (the user has no ability to add to the dictionary/ignore) - and spellcheck button has to be hit for each word.  I think this is a last  resort as we're going to suss this out!

Dave
Solve-Web-r2.xlam
checkSpellUserForm-r2.xls
0
 
LVL 42

Expert Comment

by:dlmille
ID: 37112199
@p-plater - I think I sorted it out.

Please test both of the attached, and see if they work for you.  It turns out I was testing on a system where I have Excel 2007 and Office 2010 loaded.  I've been running this from Excel 2007, but on a hunch, I tested some code on Excel 2010 and it worked!  Both of the attached files work for me, now.  Perhaps my "mods" to your add-in code will indeed work for you!

Dave
0
 

Author Comment

by:p-plater
ID: 37118034
Still can't get it to work.
How Can I send Keys to the Spell check box?
ie When the spellcheck box pops up I want to send it keystrokes to make it select the Custom Dictionary.
0
 
LVL 42

Expert Comment

by:dlmille
ID: 37118420
What I uploaded selects the custom dictionary automatically.  Its all working on my end.

When you say you still can't get it to work, I'm not sure what "it" is.  

Let's make sure we're using the same code at this point, OK?  Did you download the files I sent and test them?  

What is your environment - Office 2007 or 2010, etc?

Dave
0
 
LVL 42

Expert Comment

by:dlmille
ID: 37122534
I'm having trouble getting this to run in an Office 2007 environment, so working on that.  However, it is working perfectly in the Office 2010 environment.

What environment are you working in?

Dave
0
 

Author Comment

by:p-plater
ID: 37125800
I've only tested on 2010
The downloaded files work with the Workbook_Open Event but not when I change it to the Userform Initialise.

I will need it to run on 2007.

Is there away to send keystrokes to the Spellcheck Dialog box?
0
 
LVL 42

Expert Comment

by:dlmille
ID: 37125895
Yes there is, though it can be complicated.  I'm happy to share that intel with you.  First, please humor me.  Neither of the files I uploaded to you have workbook_open to load the dictionary.  When you downloaded both, and tested both, did either work?

Please advise - as both work in my Office 2010 environment.  I need to know if they work in your Office 2010 environment.

I'm also getting help on why its not working in Office 2007.

In the meantime, I'll look at key sequencing and post a sendkeys approach.

Dave
0
 

Author Comment

by:p-plater
ID: 37126623
The Solve-Web-r2.xlzm I Downloaded has a Workbook_Open Event that calls DictTest Which Loads the Dictionary then Shows my Userform.

The checkSpellUserForm-r2 is a three step process.
1. Load the Dictionary
2. Check the Spelling
3. Unload the Dictionary.

ZIf the Dictionary is Loaded with the Userform then Excel does'nt pick up the new dictionary untill the Userform is closed and re opened.
0
 
LVL 42

Expert Comment

by:dlmille
ID: 37127574
Having set them up this way :0 - solve-web loads on startup to save a few keypressess during tests.

Did either of the files work for you?  

Did the checkSpellUserForm -r2 work for you after you loaded the dictionary.

They both work in 2010 for me.  I would think they would both work or both not work in your office 2010 environment.

Please let me know then we can proceed down one plan or the other.

Dave
0
 

Author Comment

by:p-plater
ID: 37127804
Yes They Both Work.

I just need the Dictionary to Load when the USERFORM in loaded and then Remove the dictionary when the userform is terminated.
(The workbook (Addin) will open when Excel opens but I don't want the Dictionary to Load untill the User opens the USERFORM)
0
 
LVL 42

Expert Comment

by:dlmille
ID: 37128171
Just need to change the code on the add-in - I thought you would do that and test, sorry about that.  I took it out of workbook_open.  After loading the add-in just run the macro dicttest() which loads the dictionary and then the userform, cleaning up on userform exit.

Please test and advise.

Dave
Solve-Web-r3.xlam
0
 

Author Comment

by:p-plater
ID: 37128372
When I run that with the Open WOrkbook vcommented It does not use the Tempary dictionary words at all and any additions go to the Std dictionary

Ronald
0
 
LVL 42

Expert Comment

by:dlmille
ID: 37128385
Sorry for repetative question, but just for clarity.  Did you download the routine (which already has wkbook open commented out) and then run in the mSetup module the dicttest() routine?

Dave
0
 

Author Comment

by:p-plater
ID: 37129269
Yes
0
 
LVL 42

Expert Comment

by:dlmille
ID: 37129278
OK - let's interact here quickly, to get to the bottom of this one way or the other.  When you do that, does it work?  To test, just go and edit your CUSTOM.DIC and erase the words, and delete WEB.DIC.

Running under 2010, it does or does not work?  It is working on that version for me, so want to be very clear, as this could be an installation issue.

Dave
0
 
LVL 42

Expert Comment

by:dlmille
ID: 37129386
I just had a buddy try both workbooks - my userform, and your add-in via the dicttest() routine, and it works for him just fine as well.

Are you running XP, Vista or Windows 7.  The way we have it coded it won't work in XP, otherwise its OK.


I see no changes for the add-in I uploaded (with appropriate mods), but my version is updated as there was a couple problems you didn't note, but existed (I'd hardcoded the path to my dictionary!).  So my userform version is attached, just for completeness.

By the way, my buddy said you had very nice menu icons on top of your userform.  He's deleting it, but I needed to ensure I wasn't going crazy thinking it only worked for me!

Dave
checkSpellUserForm-r3.xls
0
 
LVL 42

Expert Comment

by:dlmille
ID: 37129463
FYI -- on your other request, I wanted to advise I could not get SendKeys to progress farther than pulling up the selection option (could not hit OK then cascade back to the user).

Here's the code, anyway:

Sub MySendKeys()
    Application.sendKeys "%FIP%C{ENTER}{ESC}", True 'alt FI = Excel Options, P for proofing, alt-C for custom dictionary, then enter, escape
End Sub

When I do these keystrokes, manually, in 2007, everything works.  Not sure what the hitch is for 2007, however, I'm still trying to understand why/if 2010 is not working for you - because if I figure out 2007, we still might be at opposing ends, lol

Dave
0
 

Author Comment

by:p-plater
ID: 37149379
Sorry Been on other things.

We are using XP so that must be the Problem?

Most of the Machines it will run on are 2007 and XP
0
 
LVL 42

Expert Comment

by:dlmille
ID: 37150812
Well, if its XP - that now makes a whole lot of sense.

Go to your Custom Dictionary in Excel and check the path to the files.  I think our app's paths are correct up to the last directory (but verify, to make sure).  It may be that all you need to do is change the folder ...\UProof  to ...\Proof.

let me know how it goes.

Dave
0
 

Author Comment

by:p-plater
ID: 37172435
None of the Code I have been working with has any app paths hard coded.

'create path for new temporary dictionary on local machine
    sTmpDict = o_ActCustDict.Path & "\" & DictName

Ronald
0
 
LVL 42

Expert Comment

by:dlmille
ID: 37172980
Search entire project for "UProof".

Let me know.  

PS -  I don't have computer internet access till after next Sunday as I'm on a cruise with family for Thanksgiving

But can test stuff

Dave
0
 

Author Comment

by:p-plater
ID: 37179906
I searched the Entire Project for "Proof" and got no results.
(All the file paths are dynamicly generated from the current custom dictionary)

Ronald
0
 
LVL 42

Expert Comment

by:dlmille
ID: 37179996
Ill test on a XP implementation when I return.

So right now you have two alternatives:

1. load the dictionary on workbook_open as originally proposed and seems that that works in all implementations we've tested.
2. Use the version proposed earlier as an alternative where miss-spelled words we're highlighted for the user in the Userform.  This version works as it doesn't depend on a dictionary to be officially setup, rather additional code is used to compare words against the temporary custom dictionary


Dave
0
 

Author Comment

by:p-plater
ID: 37180245
Hey

I think I have it solved!
Tackling it a different way.
As Excel only READS the text file when it CHECKS to spelling I have simply renamed the current custom dictionary to "Old" and made the Tempary dictionary the same name as the current dictionary was.

So
I Rename the custom dictionary to "Old" on userform initalise.
Remane it back to the original name and delete the Tempary dictionary on userform terminate.

(Enjoy your Holiday)

Ronald
0
 
LVL 42

Expert Comment

by:dlmille
ID: 37181265
Strange but good!

Sounds like a winner!

If it continues to work pls post your revised solution as it will be good for the Knowledgebase.  Seems like between you and I we've wrung this pretty well.  Too bad its not consistent between Office implementations


Dave
0
 

Author Comment

by:p-plater
ID: 37270232
Still working on the Solution.
You can close the question or wait abit longer till I can post the solution
0
 
LVL 42

Expert Comment

by:dlmille
ID: 37270253
You're the owner of the thread.  Only you can close, etc.

:)

Dave
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft has changed the look and feel of Azure AD and Microsoft account sign-in pages so that you will have a more unified look and feel when moving between the two interfaces.
Lost Word File? Eagerly, need it back? Read ahead; this File Recovery guide is for you.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question