Results 1 to 2 of 2

Working with an embedded Word object?

Threaded View

  1. #1
    Registered User
    Join Date
    06-04-2008
    Posts
    1

    Working with an embedded Word object?

    In a worksheet is an embedded Word object. When I click a button, I want the object's text to copy to cell A1. (I'm using Excel 2003.) Where Word had a new-line paragraph mark, I want a new line in Excel. And where Word had a tab mark, I want 5 spaces in Excel (couldn't think of what else to use).

    The code below shows what I came up with, but I'd like commentary on it. Is this the most efficient way? Any other suggestions?

    Thanks,
    Charles

    Sub GetWordText()
       Application.ScreenUpdating = False
       
       ActiveSheet.OLEObjects(1).Activate
       
       Dim oWrdApp As Word.Application
       Set oWrdApp = GetObject(, "Word.application")
       
       Dim rWordText As Word.Range
       Set rWordText = oWrdApp.ActiveDocument.Range
       
       With Range("a1")
          .Value = rWordText
          .Select
       End With
       
       Set oWrdApp = Nothing
       Set rWordText = Nothing
       
       Selection.Replace What:=Chr(9), _
       Replacement:="     ", LookAt:=xlPart, _
       SearchOrder:=xlByRows, MatchCase:=False
       
       Selection.Replace What:=Chr(13), _
       Replacement:=Chr(10), LookAt:=xlPart, _
       SearchOrder:=xlByRows, MatchCase:=False
       
       Application.ScreenUpdating = True
    End Sub
    Last edited by Charles M; 06-04-2008 at 01:59 AM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 0
    Last Post: 04-22-2008, 07:17 PM
  2. How can i show and embedded object as a link instead of an icon?
    By Dynelor in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 11-11-2007, 05:31 PM
  3. Macro for Inserting and Formatting Word Object
    By ChemistB in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 09-14-2007, 11:50 AM
  4. Excel Automated Mailmerge with an Embedded OLE Word Object
    By Kained in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-26-2007, 06:20 AM
  5. How can I see if an Object is Embedded or Linked
    By cpadude in forum Excel General
    Replies: 0
    Last Post: 04-11-2007, 10:09 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1