Please help with OLE object and text box content!

  • Thread starter Thread starter Jolene
  • Start date Start date
J

Jolene

Hi

How do I create a new embedded word doc with text in excel (i.e. it should
look like a text box in which I have written something).

Otherwise, how do you create a text box with text in VB code?

Thanks!
 
hi Jolene,
have a look at the AddTextbox-method in VBA-help, there`s an example:

Sub CreateTextbox()
Set myDocument = Worksheets(1)
myDocument.Shapes.AddTextbox(msoTextOrientationHorizontal, _
100, 100, 200, 50) _
.TextFrame.Characters.Text = "Test Box"
End Sub

you can create an embedded word-doc with
ActiveSheet.OLEObjects.Add(ClassType:="Word.Document")
but i don`t know how to put text in there.

i could give you a code that creates a word-doc with the text
and then embeds this word-doc in the sheet, if necessary.


stefan
 
Thanks Stefan! Would you please give me the code for the word doc as well? It
would really help a lot!
 
hi Jolene,

Sub CreateWordOleObj()
'Active workbook must have been saved, otherwise
'change wdFilename to an existing path
wdFilename = ActiveWorkbook.Path & "\test.doc"
If Dir(wdFilename) <> "" Then Kill wdFilename

Set wd = CreateObject("Word.Application")
wd.documents.Add
wd.activedocument.Paragraphs(1).Range.Text = "hello"
wd.activedocument.SaveAs wdFilename
wd.Quit

'ActiveSheet.OLEObjects(1).Delete
Set olewd = ActiveSheet.OLEObjects.Add _
(Filename:=wdFilename, _
Link:=False, _
DisplayAsIcon:=False)
olewd.Top = Range("A1").Top
olewd.Left = Range("A1").Left
olewd.Width = Range("A1").Width
End Sub


stefan
 
Stefan,

Now if you wanted to allow the user to save the embedded OLE Object, olewd,
to whatever filename they wanted how would you do that? I can't find a Save
or SaveAs method on OLEObjects
 
Back
Top