Word Document Object on excel Sheet

[K]

I have an Word Document object " EMBED("Word.Document.12","") " on my
excel worksheet. How can I copy text from Word.Document Object onto
excel sheet by VBA. Is there some code like "Range("A1").value =
Word.Document.Object.Value". I just want to know how can i reference
something with Word Document Object on excel sheet by VBA. Please can
any friend help

 

[Peter T]

I don't know much about Word but this worked for me -

Sub test()
Dim sText As String
Dim ole As OLEObject, obj As Object, objWdSection As Object
Set ole = ActiveSheet.OLEObjects("Object 1") ' or say "Object 1"
Set obj = ole.Object
If TypeName(obj) = "Document" Then
Set objWdSection = obj.sections(1)
sText = objWdSection.Range.Text
MsgBox sText
End If
End Sub

Regards,
Peter T

 

[ryguy7272]

Take a look at this:
http://word.mvps.org/FAQs/InterDev/ControlWordFromXL.htm

This too:
http://addbalance.com/usersguide/fields.htm

And this:
http://gregmaxey.mvps.org/Word_Fields.htm

Finally, once you get the DocVariable fields set up in Word (hit Alt + F9 to
see all fields), run this code from Excel.
Sub PushToWord()

Dim objWord As New Word.Application
Dim doc As Word.Document
Dim bkmk As Word.Bookmark
sWdFileName = Application.GetOpenFilename(, , , , False)
Set doc = objWord.Documents.Open(sWdFileName)

With doc
..Variables("VarNumber1").Value = Range("VarNumber1").Value
..Variables("VarNumber2").Value = Range("VarNumber2").Value
'etc
..Range.Fields.Update
End With

'ActiveDocument.Fields.Update

objWord.Visible = True

End Sub

Note: This code runs in Excel; pushes Excel variables (assigned as Named
Ranges) to Word.

HTH,
Ryan---

 

[K]

Hi Peter, Thanks for replying. Your macro pretty much doing what i
need. Just another question that in Word Document Object which i have
on my excel sheet i have some coloured text and some text is in bold
and some text is in normal. How can i get same formated text on my
excel sheet. At the moment i getting result on a message box but it
would be greate if i get result on sheet and also in same format as it
appeares in word document object. Please help

 

[Peter T]

Sub test()
Dim rngDest As Range
Dim ole As OLEObject
Dim objDoc As Object ' Word.Document
Dim objWdRange As Object ' Word.Range

Set ole = Worksheets("Sheet1").OLEObjects(1) '

Set objDoc = ole.Object
' above failed for me once until once activating
' & deactivating the embedded object.
' then it always worked whatever the selection
' might need to look into that more

If TypeName(objDoc) = "Document" Then
Set objWdRange = objDoc.Content
objWdRange.Copy

' only to set the paste range to the former
' activecell on the destination sheet
Set rngDest = Worksheets("Sheet2").Range("A1")
rngDest.Parent.Activate
rngDest.Activate

rngDest.Parent.Paste ' paste to a worksheet object
End If

End Sub

You could, if you need to, get a lot more information about Paragraphs,
Sentences, Words etc before deciding what you want to copy

Regards,
Peter T

 

[K]

Thanks lot Peter its working superb now

 

[K]

Just last question Peter if you answer this for me then i'll be
successful on what i am doing. So far your code working perfectly if
i want to copy contents of Word.Document object onto excel Sheet range
in same format. What code i need if i want all contents of
Word.Document object which i got on excel sheet onto email body in
same format. I know this is tricky one as i am looking answer for
this one from very long time and i had little help from Ron's site but
not achived what i wanted as there is no clipboard paste system in
outlook 2007 by vba. Please be kind little more to me as i already had
you valueable time and advise me what should i be doing to get exact
result on email body as it appears on Word.Document object.

 

[Peter T]

Maybe a bit of sideways thinking - get Word to send it

Sub EmailWordOLE()
Dim ole As OLEObject
Dim objWd As Object ' Word.Document
Dim objWdRng As Object ' Word.Range
Dim objMailItem As Object ' Outlook.MailItem

Set ole = ActiveSheet.OLEObjects(1) ' or say "Object 1"
' if this fails need to activate/deactivate the embedded Wrd
Set objWd = ole.Object

Set objMailItem = objWd.MailEnvelope.Item
With objMailItem
.To = "(e-mail address removed)"
.Subject = "Embedded Word in Excel"
.Save ' to drafts
'.Send ' triggers "program is trying to send.." msg in Outlook
End With

End Sub

Regards,
Peter T

 

[K]

Thanks lot Peter for all your help , time and effort.
I havent achived what I am tring to achive.
Actually I created macro to send email to lot of people from excel and
I got (Active X control) TextBox in which I put text which I want to
appear in email body. What I do for that TextBox is perfect as I can
enable Multiline in it (which i needed as i copy text from different
source and paste it into TextBox) and also you can have scroll bars in
it but the only problem whith it is you can not format indivudal
text. Word doc object work greate but the only problem is that when i
paste text in it from different source it expands it self according to
the text size which i dont need and with TextBox in shapes when i copy
text in it then the text beomes merge and not appear as it in the
source. I need something which dont size itself to the text size and
have scroll bars and the text in it can be formated indivudaly. If
you have any suggestions please let me know. Thanks again for all
your help