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. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 18 Times in 18 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.

  3. #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

  4. #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

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 18 Times in 18 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?

  6. #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 }

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 18 Times in 18 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!

  8. #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.

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 18 Times in 18 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

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 18 Times in 18 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

  11. #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
  •