Insert Existing File (Word Document) to Spreadsheet using VBA
Sub Insert_File_To_sheet()
Dim oWS As Worksheet ' Worksheet Object
Dim oOLEWd As OLEObject ' OLE Word Object
Dim oWD As Document ' Word Document Object (Use Microsoft Word Reference)
Set oWS = ActiveSheet
' embed Word Document
Set oOLEWd = oWS.OLEObjects.Add(Filename:="C:\VBADUD\Chapter 1.doc")
oOLEWd.Name = "EmbeddedWordDoc"
oOLEWd.Width = 400
oOLEWd.Height = 400
oOLEWd.Top = 30
' Assign the OLE Object to Word Object
Set oWD = oOLEWd.Object
oWD.Paragraphs.Add
oWD.Paragraphs(oWD.Paragraphs.Count).Range.InsertAfter "This is a sample embedded word document"
oOLEWd.Activate
End Sub
If you want to embed other document like PDF etc, you can do the same by
ActiveSheet.OLEObjects.Add Filename:= "C:\VBADUD\Sample_CH03.pdf", Link:=False, DisplayAsIcon:= False
Display embedded document as Icon
If you want to display the embedded document as an Icon set DisplayAsIcon property to True
Excelent blog, congratulations !!
ReplyDeleteWhy i receive the error: Compile error, User-defined type not defined (regarding Dim oWd as document) ??
Thanks a lot !!
Amando .. you need to add Microsoft Word Reference to the project. You can do it from Tools --> References
ReplyDeleteThis was very helpful for me. But I have one Question , what is to be done if it has to be embedded into a particular cell instead of oOLEWd.Top = 30 ?
ReplyDeleteI need to insert word doc in Cell(4,4)
Hi,
ReplyDeleteI got a problem with an embedded Word object in an Excel Sheet : I cannot limit the height of the object. If the user types more text than expected, the object expands its height, discarding the height fixed (400 in your example).
Is there a way to block this behaviour ?
Thanks in advance.