Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Word Document Object on excel Sheet
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Word Document Object on excel Sheet
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 "K" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Word Document Object on excel Sheet
Take a look at this:
http://word.mvps.org/FAQs/InterDev/C...WordFromXL.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--- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "Peter T" wrote: 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 "K" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Word Document Object on excel Sheet
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Word Document Object on excel Sheet
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" wrote in message ... 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Word Document Object on excel Sheet
Thanks lot Peter its working superb now
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Word Document Object on excel Sheet
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. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Word Document Object on excel Sheet
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 = " .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" wrote in message ... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
conversion of MS word document to MS excel Sheet | New Users to Excel | |||
Excel object in word document; some cell borders are too dark | Excel Discussion (Misc queries) | |||
Word Document object in Excel show only 1 page | Excel Discussion (Misc queries) | |||
Excel Object in Word Document | Excel Programming | |||
how to add excel sheet in word document as an appendix | New Users to Excel |