Please help with OLE object and text box content!
 

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!

Please help with OLE object and text box content!
 

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(msoTextOrientationHor izontal, _
100, 100, 200, 50) _
.TextFrame.Characters.Text = "Test Box"
End Sub

you can create an embedded word-doc with
ActiveSheet.OLEObjects.Add(ClassType:="Word.Docume nt")
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

On 7 Mai, 10:09, Jolene wrote:
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!

Please help with OLE object and text box content!
 

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

"stefan onken" wrote:

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(msoTextOrientationHor izontal, _
100, 100, 200, 50) _
.TextFrame.Characters.Text = "Test Box"
End Sub

you can create an embedded word-doc with
ActiveSheet.OLEObjects.Add(ClassType:="Word.Docume nt")
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

On 7 Mai, 10:09, Jolene wrote:
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!

Please help with OLE object and text box content!
 

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


On 7 Mai, 13:30, Jolene wrote:
Thanks Stefan! Would you please give me the code for the word doc as well? It
would really help a lot!

Please help with OLE object and text box content!
 

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

"stefan onken" wrote:

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


On 7 Mai, 13:30, Jolene wrote:
Thanks Stefan! Would you please give me the code for the word doc as well? It
would really help a lot!