Sunday, May 06, 2012

Embed Existing Word File to Spreadsheet using Excel VBA

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

4 comments:

  1. Excelent blog, congratulations !!

    Why i receive the error: Compile error, User-defined type not defined (regarding Dim oWd as document) ??

    Thanks a lot !!

    ReplyDelete
  2. Amando .. you need to add Microsoft Word Reference to the project. You can do it from Tools --> References

    ReplyDelete
  3. shweta10:12 AM

    This 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 ?
    I need to insert word doc in Cell(4,4)

    ReplyDelete
  4. Anonymous5:05 AM

    Hi,

    I 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.

    ReplyDelete

StumbleUpon
Share on Facebook
Related Posts Plugin for WordPress, Blogger...
Download Windows Live Toolbar and personalize your Web experience! Add custom buttons to get the information you care about most.