Solved

excel97 - thesauras

Posted on 2002-04-25
41
1,527 Views
Last Modified: 2008-03-17
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
0
Comment
Question by:garystil
  • 17
  • 9
  • 9
  • +2
41 Comments
 
LVL 12

Expert Comment

by:James Elliott
ID: 6968266
no
0
 
LVL 12

Expert Comment

by:James Elliott
ID: 6968270
you could export text to word and back again.

Thanks

Jell
0
 
LVL 44

Expert Comment

by:bruintje
ID: 6968317
Hi Garystil,

workaround is word automation

:O)Bruintje
0
 
LVL 12

Expert Comment

by:James Elliott
ID: 6968422
good point, would take a bit of doing though.

Jell
0
 
LVL 44

Expert Comment

by:bruintje
ID: 6968541
yeah i know, and got a job to perform also ;) later i'll give it a go
0
 

Author Comment

by:garystil
ID: 6968626
Bruintje, are you saying it might be possible?
0
 
LVL 8

Expert Comment

by:starl
ID: 6970300
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
 
LVL 44

Expert Comment

by:bruintje
ID: 6970321
Hi starl, you mean the roll-your-own popup menu?
0
 
LVL 8

Expert Comment

by:starl
ID: 6970339
the thesaurus pop menu... why?
0
 
LVL 44

Expert Comment

by:bruintje
ID: 6970371
just wondering, because if that's the one i can try it out ;)
0
 

Author Comment

by:garystil
ID: 6970640
I've installed the pop up theasuaras, but I can only access it from Word - I cannot access it from Excel.
0
 
LVL 44

Expert Comment

by:bruintje
ID: 6970645
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
 

Author Comment

by:garystil
ID: 6970919
OK - will wait and increase points to 150  (.__,
                                           (.  '
0
 
LVL 8

Expert Comment

by:starl
ID: 6971129
*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
 
LVL 44

Expert Comment

by:bruintje
ID: 6971143
yeah starl the points are up so go ahead :)
0
 
LVL 8

Expert Comment

by:starl
ID: 6971157
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
 
LVL 8

Expert Comment

by:starl
ID: 6971241
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
 
LVL 44

Expert Comment

by:bruintje
ID: 6972298
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
 
LVL 8

Expert Comment

by:starl
ID: 6972310
*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
 

Author Comment

by:garystil
ID: 6972642
Thanks for your continuing efforts.
0
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
LVL 8

Expert Comment

by:starl
ID: 6972659
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
 
LVL 22

Expert Comment

by:Dreamboat
ID: 6973731
I'd like to see that download too...

:)

~Dreamboat
www.TheWordExpert.com

Love that new word: schmoints
Love that old phrase: shove it!
0
 
LVL 8

Expert Comment

by:starl
ID: 6974265
Dreamboat - was already planning on offering it to you when I was done :-D
0
 
LVL 8

Expert Comment

by:starl
ID: 6982030
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
 

Author Comment

by:garystil
ID: 6982622
Starl, I think its using English US even though I tried to install English Australian.  Gary.
0
 

Author Comment

by:garystil
ID: 6990168
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
 
LVL 8

Expert Comment

by:starl
ID: 6991160
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
 
LVL 44

Expert Comment

by:bruintje
ID: 6991626
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
 

Author Comment

by:garystil
ID: 6992477
Thanks for your efforts.  Gary
0
 
LVL 8

Expert Comment

by:starl
ID: 6992697
no problem gary - I consider this an opportunity to advance my skills :-)
0
 
LVL 8

Expert Comment

by:starl
ID: 6999558
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
 

Author Comment

by:garystil
ID: 7000053
I look forward to it starl! Hopefully "installation" will be idiotproof.
0
 
LVL 8

Expert Comment

by:starl
ID: 7000144
I'm making it into an add-in. know what that is? real easy to do.
still working on a few cleanup details.
0
 
LVL 44

Expert Comment

by:bruintje
ID: 7019434
Hi starl, i really hate to see you leave EE

but any update on this one?

Brian
0
 
LVL 22

Expert Comment

by:Dreamboat
ID: 7019915
The ExcelThesaurus add-in is available for download at:

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

Please award to Starl.

:)
0
 
LVL 8

Expert Comment

by:starl
ID: 7019955
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
 

Author Comment

by:garystil
ID: 7020634
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
 
LVL 8

Accepted Solution

by:
starl earned 250 total points
ID: 7021880
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
 
LVL 8

Expert Comment

by:starl
ID: 7022296
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
 
LVL 8

Expert Comment

by:starl
ID: 7022297
oh - and if you wouldn't mind closing this question...
0
 
LVL 22

Expert Comment

by:Dreamboat
ID: 7022814
Gary: www.thewordexpert.com/forum under TheWordExpert software please. THANKS!
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Some time ago I was asked to create a VBA function that would calculate a check digit for an input number, using the following procedure: First, sum up all the individual digits in the number If that sum value has more than one digit, then sum up …
Using Word 2013, I was experiencing some incredible lag when typing.  Here's what worked for me....
The viewer will learn how to make their project stand out over others by learning how to change colors and shapes, add spaces, change directions, and add bullets to their charts.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

708 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now