excel97 - thesauras

Is it possible make available the Microsoft Word thesauras, in Microsoft Excel?

I expect the answer to be no, hence the offer of 50 points.



Gary
garystilAsked:
Who is Participating?
 
starlConnect With a Mentor Commented:
I'll see about the changes. I should be able to do #1, #2 - I'll have to look into, #3 may not be possible for 97, #4 definitely!

Gimme a few days - things are a bit hectic.
0
 
James ElliottManaging DirectorCommented:
no
0
 
James ElliottManaging DirectorCommented:
you could export text to word and back again.

Thanks

Jell
0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

 
bruintjeCommented:
Hi Garystil,

workaround is word automation

:O)Bruintje
0
 
James ElliottManaging DirectorCommented:
good point, would take a bit of doing though.

Jell
0
 
bruintjeCommented:
yeah i know, and got a job to perform also ;) later i'll give it a go
0
 
garystilAuthor Commented:
Bruintje, are you saying it might be possible?
0
 
starlCommented:
gary - check out this page:
http://www.wopr.com/vbapower/vbapower.htm
scroll down a ways and there's a download that will allow excel to use the thesaurus in word.
0
 
bruintjeCommented:
Hi starl, you mean the roll-your-own popup menu?
0
 
starlCommented:
the thesaurus pop menu... why?
0
 
bruintjeCommented:
just wondering, because if that's the one i can try it out ;)
0
 
garystilAuthor Commented:
I've installed the pop up theasuaras, but I can only access it from Word - I cannot access it from Excel.
0
 
bruintjeCommented:
there's a document there that describes some code in Word/VBA but i guess that can be used in Excel through the Word object, not sure here and i got a deadline so no time for testing such workarounds because i don't think it's just code and play it will be some try and fail first

:O)Bruintje
0
 
garystilAuthor Commented:
OK - will wait and increase points to 150  (.__,
                                           (.  '
0
 
starlCommented:
*argh* I misread that last night.. guess that's what I get for working when I'm so tired...

lemme see what I can do.
0
 
bruintjeCommented:
yeah starl the points are up so go ahead :)
0
 
starlCommented:
points, schmoints. Austin can take his points and shove it!
(no gary, I don't mean you :-D - I'm more than happy to try and help you!)
0
 
starlCommented:
ok - well, I'm onto something, just a few things to work out and it might work b/c from excel you can access the word object model... *sigh* if I knew more, it would be done just like that...

I also got "work" to do... I'll try and get it done by the end of the day...
0
 
bruintjeCommented:
Hi all,

Starl > i was just kidding..............about the points, i know why you're here and that's why i'm here also

but i'm going to call it a day.........it's not much for 22 hours, but a late burglar alarm and maybe some emergency call later tonight so my coding is worse then normal

i leave you with the code that HAS to be refined
it still DOES NOT work

-add the word dll to your references in Excel
-paste the code

Option Explicit

Public Sub AddPopupThesaurusMenu()
Dim ShortCutMenu As CommandBar
Dim ThesaurusMenu As CommandBarPopup
    'Get handle to "Text" shortcut menu
    Set ShortCutMenu = CommandBars("Edit")
    'Add thesaurus popup to the shortcut menu
    Set ThesaurusMenu = ShortCutMenu.Controls.Add( _
        Type:=msoControlPopup)
    With ThesaurusMenu
        .BeginGroup = True
        .Caption = "&Thesaurus"
        .OnAction = "AccessThesaurusMenu"
    End With
End Sub


Private Sub AccessThesaurusMenu()
Dim i As Long
Dim Slist As Variant
Dim selWord As String
Dim ThesLangID As Long
Dim ThesDict As Variant
Dim LookupWord As Word.SynonymInfo
Dim Ctrl As CommandBarControl
Dim ThesaurusMenu As CommandBarPopup
Dim oWord As New Word.Application
    System.Cursor = wdCursorWait

    'Get handle to popup thesaurus menu
    Set ThesaurusMenu = CommandBars.ActionControl

    'Remove any existing synonyms from the popup menu
    For Each Ctrl In ThesaurusMenu.Controls
        Ctrl.Delete
    Next Ctrl
   
    With ActiveCell
        'Select insertion point word
        'Deselect any trailing spaces
        Do While Right(.Text, 1) = Chr(32)
            .MoveLeft Unit:=wdCharacter, _
                Count:=1, Extend:=wdExtend
        Loop
        selWord$ = Trim(.Text) 'Store selection
    End With
    If selWord$ = "" Or selWord$ = vbCr Then 'No selection
        With ThesaurusMenu.Controls.Add( _
          Type:=msoControlButton, Temporary:=True)
            .FaceId = 463 'Exclamation icon
            .Caption = "(no selection!)"
        End With
        Exit Sub
    End If

    'Get language ID for active thesaurus dictionary
    Set ThesDict = oWord.Languages( _
        wdEnglishUS).ActiveThesaurusDictionary
    If ThesDict Is Nothing Then
        With ThesaurusMenu.Controls.Add( _
          Type:=msoControlButton, Temporary:=True)
            .FaceId = 1019 'Cancel icon
            .Caption = "(thesaurus not installed!)"
        End With
        Exit Sub
    End If
    ThesLangID = ThesDict.LanguageID

    'Lookup the selected word in Word's thesaurus
    Set LookupWord = SynonymInfo(Word:=selWord$, _
        LanguageID:=ThesLangID)

    'Add synonyms to the popup thesaurus menu if found
        'NOTE: for simplicity lookup first word meaning only
    If LookupWord.Found = True Then 'Synonyms exist
        On Error GoTo NoSynonyms
        Slist = LookupWord.SynonymList(1) 'first word meaning
        On Error GoTo 0
        For i = 1 To UBound(Slist)
            With ThesaurusMenu.Controls.Add( _
              Type:=msoControlButton, Temporary:=True)
                .Tag = Slist(i)
                .Caption = Slist(i)
                .OnAction = "SetSynonymToTagName"
            End With
        Next i
    Else 'No synonyms found
NoSynonyms:
        With ThesaurusMenu.Controls.Add( _
          Type:=msoControlButton, Temporary:=True)
            .FaceId = 487 'Information icon
            .Caption = "(no synonyms found!)"
        End With
    End If
 
    System.Cursor = wdCursorNormal
    Set oWord = Nothing
End Sub

Private Sub SetSynonymToTagName()
Dim CaseType As Long
Dim ReplaceSelSetting As Boolean
Dim SelectedCtrl As CommandBarButton
    'Get handel to selected control
    Set SelectedCtrl = CommandBars.ActionControl

    'Store current ReplaceSelection setting
    ReplaceSelSetting = Options.ReplaceSelection
    'Force ReplaceSelection to ON
    Options.ReplaceSelection = True

    'Store current capitalization state for selection
    CaseType = Selection.Range.Case

    With Selection
        'Replace selection with accessed control's tag
        .TypeText Text:=SelectedCtrl.Tag
        'Find newely inserted text and select it
        With Selection.Find
            .ClearFormatting
            .Text = SelectedCtrl.Tag
            .Wrap = wdFindStop
            .Execute Forward:=False
        End With
        'Restore capitalization state
        .Range.Case = CaseType
        'Deselect text
        .EndOf Unit:=wdWord, Extend:=wdMove
    End With

    'Restore original ReplaceSelection setting
    Options.ReplaceSelection = ReplaceSelSetting
End Sub

-the first part will add a menu item to the wrong menu "edit" can't find the popup now too tired
-on click on the new menuitem thesaurus there will be a check started on the activecell content
-still not wokring but it is as far as i can get it now

Starl > maybe a go ahead point for you to go on with

Bye and c y'all tomorrow or monday
Brian
0
 
starlCommented:
*GEE* that looks awfully familiar! copycat thief!

I've already modified the code quite a bit, but there's a huge drawback - you can't call a macro while a cursor is in a cell. NO WAY JOSE! so, I'm looking at a work around. Select a cell and it takes all that info and posts it in a form box, where you then select the word and run the thesaurus.. it's the only way... :-(
0
 
garystilAuthor Commented:
Thanks for your continuing efforts.
0
 
starlCommented:
well, gary - if you don't mind going in that direction, then I will finish the code. But I will be gone till Tuesday... 1/2 the code is at work - but I may be able to get the form done here at home...

if you agree, I will post the code here (b/c of the rules and all that) but I will make an add-in available to you for download.
0
 
Anne TroyEast Coast ManagerCommented:
I'd like to see that download too...

:)

~Dreamboat
www.TheWordExpert.com

Love that new word: schmoints
Love that old phrase: shove it!
0
 
starlCommented:
Dreamboat - was already planning on offering it to you when I was done :-D
0
 
starlCommented:
been working on it on/off today.. having a slight prob interfacing with word.

You're using the English (US) dictionary? At the moment, I have to hardcode that option in....I can change it for you if needed.
0
 
garystilAuthor Commented:
Starl, I think its using English US even though I tried to install English Australian.  Gary.
0
 
garystilAuthor Commented:
I've often wondered why Microsoft didn't include the thesauras in Excel, given that spelling and autocorrect are available. I assumed that it was causing too many problems and ultimately gave up. I can increase the schmoints if its proving difficult, but if it's proving to be impossible, let me know.    Gary
0
 
starlCommented:
sorry, gary - didn't get a notif for this (they ARE down).
anyway, I'm still working on this. Had a very bad week at work and since this is for 97, I can only do it there. I will continue to work on this. So far it's not impossible, just difficult - cross application object modeling doesn't always work in 97.
0
 
bruintjeCommented:
Hi starl, you can always ask me to give you a hand if you ever need one......i don't know how far you're into this already but if you need something in code or whatever leave a comment or send me a mail (it's in my profile)
0
 
garystilAuthor Commented:
Thanks for your efforts.  Gary
0
 
starlCommented:
no problem gary - I consider this an opportunity to advance my skills :-)
0
 
starlCommented:
gary - it's done, but with the form and all, I can't really post it here.. how do you post a form? So, I will provide a link to a place where you can download it. It'll take a while. I will post here again w/the link when the transfer is done.
0
 
garystilAuthor Commented:
I look forward to it starl! Hopefully "installation" will be idiotproof.
0
 
starlCommented:
I'm making it into an add-in. know what that is? real easy to do.
still working on a few cleanup details.
0
 
bruintjeCommented:
Hi starl, i really hate to see you leave EE

but any update on this one?

Brian
0
 
Anne TroyEast Coast ManagerCommented:
The ExcelThesaurus add-in is available for download at:

http://www.thewordexpert.com/downloads.htm#ExcelThesaurus

Please award to Starl.

:)
0
 
starlCommented:
Thanks Dream :-)

Gary - Dreamboat has kindly allowed me to use her site to hold the xla. In addition to the instructions at her site, you may have to "browse" to the location where you extracted the xla.

If you have ANY problems, please post to THIS question or at the download site - I no longer view question lists at this site - I am only posting to notifs I receive.
0
 
garystilAuthor Commented:
Hi Starl - well done. I've tested it and I'm happy as it is but I'd love to know whether the following modifications are possible.

1. Can I go straight to synonyms by clicking SHIFT F7 (as in MS WORD)
2. Can Thesauras window be made much smaller or even variable and saved at that size.
3. Can a minimize option (in addition to "close" that is there at present) be included, so its always on hand. Or perhaps even for the thesauras to always be "on top" when I return to excel.
4. Can the searched word in thesauras window remain highlighted after the search is requested so that I don't have to click back into that area to search for another word (I constantly look up words in my lyric writing).

If its all too hard or too messy, just let me know.
   
0
 
starlCommented:
gary - Would you please leave anymore comments at the download site? The site owner needs to keep track of what I do to the program. Also, I do not know how much longer I will be able to access this site.
0
 
starlCommented:
oh - and if you wouldn't mind closing this question...
0
 
Anne TroyEast Coast ManagerCommented:
Gary: www.thewordexpert.com/forum under TheWordExpert software please. THANKS!
0
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.

All Courses

From novice to tech pro — start learning today.