Results 1 to 11 of 11
  1. #1
    Star Lounger
    Join Date
    Jun 2004
    Location
    Lambertville, New Jersey, USA
    Posts
    82
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Retrieve Excel data using VB code in MS Word doc (XP (2002))

    I have to write some VB code in a Word doc which will access an embedded Excel spreadsheet. I haven't got a clue on how to get started. I am proficient in writing VB code for MS Access applications but haven't had to access data in a spreadsheet before. I figured out how to "dim" an Excel Spreadsheet object in my code but that's it. Can some one point me in the right direction. THanks, Lance

  2. Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

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

    Re: Retrieve Excel data using VB code in MS Word doc (XP (2002))

    Set a reference to the Microsoft Excel 10.0 Object Library in Tools | References...

    You can use code like this:

    Dim objOLE As OLEFormat
    Dim xlWbk As Excel.Workbook
    Set objOLE = ActiveDocument.InlineShapes(1).OLEFormat
    objOLE.DoVerb wdOLEVerbOpen
    Set xlWbk = objOLE.Object
    Debug.Print xlWbk.ActiveSheet.Range("A1").Value
    xlWbk.Application.Quit
    Set xlWbk = Nothing
    Set objOLE = Nothing

    This assumes that the embedded workbook is the first inline shape.

  4. #3
    Star Lounger
    Join Date
    Jun 2004
    Location
    Lambertville, New Jersey, USA
    Posts
    82
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Retrieve Excel data using VB code in MS Word doc (XP (2002))

    Great. Thank you very much. That's exactly what I was looking for.

    Thanks again.

    Lance

  5. #4
    Star Lounger
    Join Date
    Jun 2004
    Location
    Lambertville, New Jersey, USA
    Posts
    82
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Retrieve Excel data using VB code in MS Word doc (XP (2002))

    Hans;
    I copied the code into the doc. It compiled OK but I got a run time error.
    The error is on line: Set xlWbk = objOLE.Object
    The error is: Run-time error '13': Type mismatch

    Any ideas?
    Thanks, Lance

  6. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 16 Times in 16 Posts

    Re: Retrieve Excel data using VB code in MS Word doc (XP (2002))

    How did you insert/create the embedded Excel object?
    What do you see if you press Alt+F9 to show field codes?

  7. #6
    Star Lounger
    Join Date
    Jun 2004
    Location
    Lambertville, New Jersey, USA
    Posts
    82
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Retrieve Excel data using VB code in MS Word doc (XP (2002))

    I used menu cmd Insert->Object
    When I hit Alt F9 I get: { EMBED Excel.Sheet.8 }

  8. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 16 Times in 16 Posts

    Re: Retrieve Excel data using VB code in MS Word doc (XP (2002))

    I'm afraid I don't understand then - the code works OK for me in Word 2002. Could you perhaps attach a small demo document with an embedded Excel worksheet and a macro? Thanks!

  9. #8
    Star Lounger
    Join Date
    Jun 2004
    Location
    Lambertville, New Jersey, USA
    Posts
    82
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Retrieve Excel data using VB code in MS Word doc (XP (2002))

    I've created a small doc file with the embedded spreadsheet. There is a Text Box that has code attached to the Change Event that will run your code.

    Thanks for your help.

  10. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 16 Times in 16 Posts

    Re: Retrieve Excel data using VB code in MS Word doc (XP (2002))

    Your Excel object is the second inline shape - the text box is the first one. So use

    Set objOLE = ActiveDocument.InlineShapes(2).OLEFormat

  11. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 16 Times in 16 Posts

    Re: Retrieve Excel data using VB code in MS Word doc (XP (2002))

    In addition to my previous reply: you could loop through the inline shapes until you find an embedded ole object:

    Dim sh As InlineShape
    For Each sh In ActiveDocument.InlineShapes
    If sh.Type = wdInlineShapeEmbeddedOLEObject Then
    ' code to process embedded object goes here
    ...
    Exit For
    End If
    Next sh

  12. #11
    Star Lounger
    Join Date
    Jun 2004
    Location
    Lambertville, New Jersey, USA
    Posts
    82
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Retrieve Excel data using VB code in MS Word doc (XP (2002))

    Right on the money! As soon as I changed the array value it worked. Just like you said it would.
    Thanks for the new code to loop through the shapes to find the one I want. That will be very helpful going forward.

    Thanks very much for your time and effort.
    Lance

Posting Permissions

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