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. |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Word Document Object on excel Sheet
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 |
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 |