none
Embed any file in MSWord from Excel, if path and filename is provided RRS feed

  • Question

  • hello

    as part of a low-level personal database management system, i am creating a userform in excel which records folder path & filename data and then uses this info to embed the specified file from the specified path into a word template. for each file  embedded, the macro creates a entry in the spreadsheet for recording the number of files attached so far.

    please don't suggest that i do it in MS word rather than in excel.

    i am in the process of learning how to accomplish from MS excel vba.

    any suggestions are most welcome and thank you for your thoughts.

    regards

    arunatex

    Tuesday, May 27, 2014 1:20 PM

Answers

  • You can certainly program Word vba from Excel if that's what you mean. e.g. at its simplest, you could create an Excel macro to embed an object (presumably an Excel file) into a Word document e.g. as follows and call that macro from your userform, passing to it the path and filename. i.e.

    EmbedExcelFile Me.TextBox1.Text, Me.TextBox2.Text. You will probably need some error correction and a check for the existence of the various files. Note that embedding a workbook object from file can take a while to run. I'll leave you to decide how you want the process recording in the worksheet.

    Option Explicit
    Private wdApp As Object
    Private wdDoc As Object
    Private oRng As Object
    Private Const strDocname As String = "D:\My Documents\Word documents\Lorem ipsum dolor sit amet.docx" 'The Word document

    Sub EmbedExcelFile(strPath As String, strFilename As String)
        On Error Resume Next
        Set wdApp = GetObject(, "Word.Application")
        If Err Then
            Set wdApp = CreateObject("Word.Application")
        End If
        On Error GoTo 0
        wdApp.Visible = True
        Set wdDoc = wdApp.Documents.Open(FileName:=strDocname, Visible:=True)
        Set oRng = wdDoc.Range
        oRng.collapse 0
        oRng.InlineShapes.AddOLEObject ClassType:="Excel.Sheet.12", _
                                       FileName:=strPath & strFilename, _
                                       LinkToFile:=False, _
                                       DisplayAsIcon:=True, _
                                       IconFileName:= _
                                       "C:\Windows\Installer\{91140000-0011-0000-0000-0000000FF1CE}\xlicons.exe", _
                                       IconIndex:=1, _
                                       IconLabel:=strFilename
    End Sub

    Graham Mayor - Word MVP
    www.gmayor.com

    Tuesday, May 27, 2014 2:03 PM

All replies

  • First you create a Word Application object. OPen the word template .

    Dim WdApp as Object

    dim wdDoc as Object

    set wdapp=Createobject("Word.Application")

    set wdDoc=wdapp.open(....)

    wddoc.InlineShapes.AddOLEObject filename:=".....")

    --------

    Read the help for AddOleObject method for other parameter specification.


    Best Regards,
    Asadulla Javed, Kolkata
    ---------------------------------------------------------------------------------------------
    Please do not forget to click “Vote as Helpful” if any post helps you and "Mark as Answer”if it solves the issue.

    Tuesday, May 27, 2014 2:02 PM
    Answerer
  • You can certainly program Word vba from Excel if that's what you mean. e.g. at its simplest, you could create an Excel macro to embed an object (presumably an Excel file) into a Word document e.g. as follows and call that macro from your userform, passing to it the path and filename. i.e.

    EmbedExcelFile Me.TextBox1.Text, Me.TextBox2.Text. You will probably need some error correction and a check for the existence of the various files. Note that embedding a workbook object from file can take a while to run. I'll leave you to decide how you want the process recording in the worksheet.

    Option Explicit
    Private wdApp As Object
    Private wdDoc As Object
    Private oRng As Object
    Private Const strDocname As String = "D:\My Documents\Word documents\Lorem ipsum dolor sit amet.docx" 'The Word document

    Sub EmbedExcelFile(strPath As String, strFilename As String)
        On Error Resume Next
        Set wdApp = GetObject(, "Word.Application")
        If Err Then
            Set wdApp = CreateObject("Word.Application")
        End If
        On Error GoTo 0
        wdApp.Visible = True
        Set wdDoc = wdApp.Documents.Open(FileName:=strDocname, Visible:=True)
        Set oRng = wdDoc.Range
        oRng.collapse 0
        oRng.InlineShapes.AddOLEObject ClassType:="Excel.Sheet.12", _
                                       FileName:=strPath & strFilename, _
                                       LinkToFile:=False, _
                                       DisplayAsIcon:=True, _
                                       IconFileName:= _
                                       "C:\Windows\Installer\{91140000-0011-0000-0000-0000000FF1CE}\xlicons.exe", _
                                       IconIndex:=1, _
                                       IconLabel:=strFilename
    End Sub

    Graham Mayor - Word MVP
    www.gmayor.com

    Tuesday, May 27, 2014 2:03 PM
  • I do this in Word.  I have several Word documents that are created for a product test.  All the documents are concatenated to create a report.  A couple of things I had problems with:

    Inconsistent styles.  For example, a heading 1 style in doc1 is defined differently in doc2.  When you cat them the first doc style overrides the second style.  I had to make all templates consistent.

    Orientation.  Some docs in landscape and some in portrait.  Need to insert section breaks.

    Margins.  Inconsistent margins.  Made consistent in templates.

    Table column widths.  Some table column widths were not defined in some docs so when you cat them they would change widths.  Fully defined all table column widths.

    Tuesday, May 27, 2014 2:50 PM
  • thanks Asad

    i shall give your solution a try.

    Wednesday, May 28, 2014 5:54 AM
  • hello 

    graham many thanks for your solution, it worked beautifully.

    the most suitable solution for my problem.

    thanks again for your time and thoughts.

    regards

    Arun
    • Marked as answer by arunatex Wednesday, May 28, 2014 6:14 AM
    • Edited by arunatex Wednesday, May 28, 2014 6:15 AM issue resolved
    • Unmarked as answer by Asadulla JavedEditor Wednesday, May 28, 2014 9:53 AM
    Wednesday, May 28, 2014 5:56 AM
  • thank you, your points will be most helpful since i am interfacing between word and excel and had no idea about the sort of problems i was going to face.

    thanks again.

    Wednesday, May 28, 2014 5:57 AM