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
Bookmarks