Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Oct 2004
    Location
    USA
    Posts
    223
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Open embedded Excel sheet with VBA (Word 2000 SP3)

    I've learned so much since I started this project by reading other posts and getting answers from you fine folks! Thanks! Now that I have everything working fine I've added a new twist and have run into another "I wish I could". Maybe someone can help me.
    I now open an Excel workbook from a macro when a radio button is clicked in the Word document and display a dialog box with a map of the states in different colors showing sales regions. I ask the user to pick which region they are in and return that answer to the word Doc which I then use in conjunction with the radio button choice to display a value from a table. I'm so proud I got this to work!!!!
    Now I have complaints about keeping the two files (i.e. the Word Doc and the Excel workbook) together. I tried to embed the workbook into the document but I can't figure out how to open it within the VBA code. If I double click on the icon the workbook opens and the Web toolbar shows the name to be something like "c:myfoldermy_word_doc.doc!_1162814043". I tried using this name in my open statement but it returned an error message assuring me I had no idea what I was doing and that no file by that name could be found.
    There must be a way to do this.
    I tried to record a macro and open the embedded file manually so I could see how it was done but while in record mode the icon looks like a cassette tape unless I'm over a control button or on one of the tool bars. I had expected it to work like it does in Excel where everything is recorded. Bummer.
    Any suggestions????
    Thanks

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Open embedded Excel sheet with VBA (Word 2000 SP3)

    While recording a macro, you can still select the embedded worksheet with the keyboard, then use the Edit menu to edit it. But since you're then effectively working in Excel, Word's macro recorder only records starting the edit, not your actions in Excel. This is what I got. It assumes that the embedded worksheet is the first inline shape in the document:

    <code>ActiveDocument.InlineShapes(1).OLEFormat.DoV erb VerbIndex:=wdOLEVerbPrimary</code>

  3. #3
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Open embedded Excel sheet with VBA (Word 2000

    You can "launch" an embedded workbook using VBA in Word 2002, but I'm not sure how you effectively access it after that. Something for you to play with, assuming it works in Word 2000:

    Sub OpenEmbedInAssociatedApplication()
    Dim fld As Field
    For Each fld In ActiveDocument.Fields
    If fld.Type = wdFieldEmbed Then Exit For
    Next
    ' Open workbook in Excel
    fld.OLEFormat.Open
    ' These are alternatives
    'fld.OLEFormat.Activate
    'fld.OLEFormat.DoVerb
    ' Not sure what you do now... how do you refer to it?
    Set fld = Nothing
    End Sub

    Added: This code will bomb out horribly if there are no embedded fields. Sorry, that will have to be fixed later!!

  4. #4
    3 Star Lounger
    Join Date
    Oct 2004
    Location
    USA
    Posts
    223
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Open embedded Excel sheet with VBA (Word 2000 SP3)

    Thanks HansV. Nice suggestion, I should have tried the keyboard approach. I tried the record macro as you said and it returned a similar string except mine ended with "1" instead of the last variable name you have. I tried putting the statement in the macro but I can't figure out how to make it work. I keep getting an error that it can't find the item. I see there is another post, I'll try that one.
    Thanks
    Phil

  5. #5
    3 Star Lounger
    Join Date
    Oct 2004
    Location
    USA
    Posts
    223
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Open embedded Excel sheet with VBA (Word 2000

    Wow! That works. The first embed object I had was a JPEG of a logo which it opened in Microsoft Picture Editor. I change the code to look for the second one and it opened the workbook. I got the message warning me about the macros though and then displayed the open workbook. You're right, I'll have to figure out how to display the dialog now and see if I can suppress the message and display of the open workbook.
    This is definitely progress though!
    Thanks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •