none
Insert an attachment in Excel sheet through vba RRS feed

  • Question

  • Hi,

    I have one excel sheet with a command button. I want to embed some word documents in the excel sheet(in Sheet1) once I press the command button. I have some word document in the "C:\Users\Deb\Desktop\Test(Folder)" which I want to embed in my excel sheet and the name of the files are File1.docx,File2docx,Fil3.docx and so on.. In the excel sheet I have the File names of the word files in B row. I want once I press the command button All the document files will be embedded in the excel sheet.

    For example:

    If I have File1.docx in cell B1 then corresponding file will be embedded in E1 again

    If I have File2.docx in cell B7 then corresponding file will be embedded in E7 again

    If I have File3.docx in cell B10 then corresponding file will be embedded in E10.

    Basically all the file names of the word files will be present in the excel sheet(in row B). What All I need is to find the correct file names from the excel sheet and embed the corresponding files.

    Thanks.

    Saturday, May 9, 2015 5:59 AM

Answers

  • Sub Test()
      Dim OO As OLEObject
      Dim R As Range
      Dim Path As String
      
      Path = "C:\Users\Deb\Desktop\Test(Folder)\"
    
      'Visit each cell
      For Each R In Range("B1", Range("B" & Rows.Count).End(xlUp))
        If IsEmpty(R) Then GoTo Skip
        'File exists?
        If Dir(Path & R) <> "" Then
          'Embed
          Set OO = ActiveSheet.OLEObjects.Add( _
            Filename:=Path & R, Link:=False, DisplayAsIcon:=True, _
            IconFileName:=Application.Path & "\WINWORD.EXE", _
            IconIndex:=0, IconLabel:=R.Value, _
            Left:=R.Offset(, 1).Left, Top:=R.Top)
        End If
    Skip:
      Next
    End Sub
    

    • Proposed as answer by ryguy72 Saturday, May 9, 2015 4:58 PM
    • Marked as answer by Deb_chatt Thursday, May 14, 2015 9:19 AM
    • Unmarked as answer by Deb_chatt Thursday, May 14, 2015 10:29 AM
    • Marked as answer by Deb_chatt Friday, May 15, 2015 1:32 PM
    Saturday, May 9, 2015 7:24 AM
  • Hi Deb,

    I think you have confused me a bit. :-) I have to apologize, I did not understand you, resp. read what you have written, correctly.

    When the list of the files (that should be embedded) is in a different file, you have to refer to the cells in that file. And you can also have another different file as destination, doesn't matter.

    At the end it's the same, all you have to do is to refer to the right objects.

    Andreas.

    Sub EmbedFiles()
      Dim OO As OLEObject
      Dim R As Range
      Dim Path As String
     
      Dim ImportList As Range
      Dim Destination As Worksheet
     
      'Refer to the destination sheet (doesn't matter where it is)
      Set Destination = Workbooks("test1.xlsx").Sheets("Sheet1")
     
      'refer to the cells that contain the file names (doesn't matter where it is)
      With Workbooks("test1.xlsx").Sheets("Sheet1")
        Set ImportList = .Range("B1", .Range("B" & .Rows.Count).End(xlUp))
      End With
     
      'Path can be also anywhere
      Path = "C:\Users\Deb\Desktop\Test(Folder)\"
     
    '  'Just a different sample, if when you want to select the list with the files:
    '  If Not TypeOf Selection Is Range Then
    '    MsgBox "Select some cells"
    '    Exit Sub
    '  End If
    '  With ActiveWorkbook
    '    'Path of this file
    '    If .Path = "" Then
    '      MsgBox "Save this file into the same folder that contain the Word file(s)"
    '      Exit Sub
    '    End If
    '    Path = .Path & "\"
    '    'This sheet
    '    Set Destination = .ActiveSheet
    '    With Destination
    '      Set ImportList = Selection
    '      'Reduce to the used range (Selection might be a column!)
    '      Set ImportList = Intersect(.UsedRange, ImportList)
    '      If ImportList Is Nothing Then
    '        MsgBox "Selected cells did not contain anything"
    '        Exit Sub
    '      End If
    '    End With
    '  End With
    
      'Visit each cell
      For Each R In ImportList
        If IsEmpty(R) Then GoTo Skip
        'File exists?
        If Dir(Path & R) <> "" Then
          'Embed
          Set OO = Destination.OLEObjects.Add( _
            Filename:=Path & R, Link:=False, DisplayAsIcon:=True, _
            IconFileName:=Application.Path & "\WINWORD.EXE", _
            IconIndex:=0, IconLabel:=R.Value, _
            Left:=R.Offset(, 1).Left, Top:=R.Top)
        End If
    Skip:
      Next
    End Sub

    • Marked as answer by Deb_chatt Friday, May 15, 2015 1:32 PM
    Wednesday, May 13, 2015 10:02 AM
  • Thanks for the reply. I'm getting Subscript Out of range error message in the following line while executing the code.

    Set Destination = Workbooks("test1.xlsx").Sheets("Sheet1")


    Hi Deb,

    I bet the file is not open. You can open the file with e.g.

    Workbooks.Open "C:\Users\Deb\Desktop\Test1.xlsx"

    but if the file is already opened and you run that line, you have another issue.

    Andreas.

    • Marked as answer by Deb_chatt Thursday, May 14, 2015 9:18 AM
    • Unmarked as answer by Deb_chatt Thursday, May 14, 2015 10:29 AM
    • Marked as answer by Deb_chatt Friday, May 15, 2015 1:31 PM
    Wednesday, May 13, 2015 12:57 PM
  • However I don't want to make visible test1.xlsx file.

    Create a second instance of Excel.

    Andreas.

    Private Sub CommandButton1_Click()
      Dim OO As OLEObject
      Dim R As Range
      Dim Path As String
    
      Dim ImportList As Range
      Dim Destination As Worksheet
      Dim App As Application
      Dim Wb As Workbook
      
      Set App = CreateObject("Excel.Application")
      Set Wb = App.Workbooks.Open("C:\Users\Deb\Desktop\Test1.xlsx")
      Set Destination = Wb.Sheets("Sheet1")
      With Destination
        Set ImportList = .Range("B1", .Range("B" & .Rows.Count).End(xlUp))
      End With
    
      Path = "C:\Users\Deb\Desktop\Test(Folder)\"
    
      'Visit each cell
      For Each R In ImportList
        If IsEmpty(R) Then GoTo Skip
        'File exists?
        If Dir(Path & R) <> "" Then
          'Embed
          Set OO = Destination.OLEObjects.Add( _
            Filename:=Path & R, Link:=False, DisplayAsIcon:=True, _
            IconFileName:=Application.Path & "\WINWORD.EXE", _
            IconIndex:=0, IconLabel:=R.Value, _
            Left:=R.Offset(, 1).Left, Top:=R.Top)
        End If
    Skip:
      Next
      Wb.Close True
      App.Quit
    End Sub
    

    • Marked as answer by Deb_chatt Thursday, May 14, 2015 9:18 AM
    • Unmarked as answer by Deb_chatt Thursday, May 14, 2015 10:28 AM
    • Marked as answer by Deb_chatt Friday, May 15, 2015 1:31 PM
    Thursday, May 14, 2015 8:49 AM
  • Excel was unable to open the file by repairing the unreadable content.

    It's related to your Word files, the file size is 0 bytes (inside the Excel file).

    Andreas.

    • Marked as answer by Deb_chatt Friday, May 15, 2015 1:31 PM
    Friday, May 15, 2015 7:33 AM
  • File1.docx is present in cell B1 of Test1.xlsx file then corresponding file is embedding in E1. But if I want it to embed it in the next row what exactly I need to change in my code?

    You can set the Left and Top property to any position you like, just refer to the related cell:

    Set OO = Destination.OLEObjects.Add( _
            Filename:=Path & R, Link:=False, DisplayAsIcon:=True, _
            IconFileName:=Application.Path & "\WINWORD.EXE", _
            IconIndex:=0, IconLabel:=R.Value, _
            Left:=R.Left, Top:=R.Offset(1, 0).Top)

    Andreas.

    • Marked as answer by Deb_chatt Friday, May 15, 2015 1:31 PM
    Friday, May 15, 2015 9:49 AM

All replies

  • Sub Test()
      Dim OO As OLEObject
      Dim R As Range
      Dim Path As String
      
      Path = "C:\Users\Deb\Desktop\Test(Folder)\"
    
      'Visit each cell
      For Each R In Range("B1", Range("B" & Rows.Count).End(xlUp))
        If IsEmpty(R) Then GoTo Skip
        'File exists?
        If Dir(Path & R) <> "" Then
          'Embed
          Set OO = ActiveSheet.OLEObjects.Add( _
            Filename:=Path & R, Link:=False, DisplayAsIcon:=True, _
            IconFileName:=Application.Path & "\WINWORD.EXE", _
            IconIndex:=0, IconLabel:=R.Value, _
            Left:=R.Offset(, 1).Left, Top:=R.Top)
        End If
    Skip:
      Next
    End Sub
    

    • Proposed as answer by ryguy72 Saturday, May 9, 2015 4:58 PM
    • Marked as answer by Deb_chatt Thursday, May 14, 2015 9:19 AM
    • Unmarked as answer by Deb_chatt Thursday, May 14, 2015 10:29 AM
    • Marked as answer by Deb_chatt Friday, May 15, 2015 1:32 PM
    Saturday, May 9, 2015 7:24 AM
  • Wow!  Very cool!!  Andreas, by chance would you know how to download a bunch of embedded files from a bunch of Word files and save each embedded file to the same folder where the Word doc is?  Let's assume all embedded files are Word files, Excel files, and Adobe files.  Can you think of a way to do this?

    I had to do this a few weeks ago.  It was very tedious.


    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

    Saturday, May 9, 2015 5:00 PM
  • Thanks Andreas for your help!! It's working as it should. One last question i.e. Could you please let me know what changes do I need to make in the code If I press the button from one excel and the docx file will be attached in another excel. For example: I am pressing the button from an excel workbbok which is located at "C:\Users\Deb\Desktop\Test.xlsm" and I want to embed the docx files in another workbook which is placed at "C:\Users\Deb\Desktop\Test1.xlsx".

    Thanks!!

     
    Saturday, May 9, 2015 7:13 PM
  • If I press the button from one excel and the docx file will be attached in another excel.

    Open both files, copy the macro below into the file that contains the docx file(s), activate the destination workbook and execute the macro.

    Andreas.

    Sub CopyEmbeddedObjects()
      Dim OO As OLEObject
      Dim S As Shape
    
      'For each object
      For Each OO In ThisWorkbook.ActiveSheet.OLEObjects
        'Embedded?
        If OO.OLEType = xlOLEEmbed Then
          'Copy
          OO.Copy
          With ActiveWorkbook.ActiveSheet
            'Into this file
            .Paste
            If Val(Application.Version) < 12 Then
              'Get the last shape
              Set S = .Shapes(.Shapes.Count)
            Else
              'Search the first non-comment shape from the end
              Dim i As Long
              For i = .Shapes.Count To 1 Step -1
                Set S = .Shapes(i)
                If S.Type <> msoComment Then Exit For
              Next
            End If
          End With
    
          'You can move it anywhere, e.g. same position as in original file
          S.Left = OO.Left
          S.Top = OO.Top
        End If
      Next
    End Sub
    

    Sunday, May 10, 2015 8:15 AM
  • how to download a bunch of embedded files from a bunch of Word files

    Well, unfortunately the OLEObject has no SaveAs method, with VBA we can only execute "verbs".

    We can execute the "Open" verb and the "Primary" verb (which depends on your system and can be anything, same verb is executed when you double click on such a file within the Windows Explorer).

    After we have executed the Open verb, the object property inside the OLEObject becomes valid and we can access the Word / Excel file as usual and save it to disk.

    Sub SaveEmbeddedObject()
      Dim OO As OLEObject
      Dim i As Long
    
      'For each object
      For Each OO In ThisWorkbook.ActiveSheet.OLEObjects
        'Embedded?
        If OO.OLEType = xlOLEEmbed Then
          i = InStr(OO.progID, ".")
          If i = 0 Then
            i = Len(OO.progID)
          Else
            i = i - 1
          End If
          Select Case Left$(OO.progID, i)
            Case "Word", "Excel"
              OO.Verb xlVerbOpen
              OO.Object.SaveAs 'Put_The_Filename_Here
              OO.Object.Close False
          End Select
        End If
      Next
    End Sub
    

    AFAIK it is not possible to detect the original file name (we can see that on screen, but I can't find it inside the object model). So you have to choose the file name by yourself.

    That means for PDF files, we are not able to get them out.

    Andreas.

    Sunday, May 10, 2015 8:48 AM
  • Hi Andreas,

    Thanks for your reply. I think you didn't get me correctly. I don't want to copy docx files from any workbook. What I want is to copy all the docx file from a folder and attach them in  a separate workbook.

    For example:

    I am using a workbook Test.xlsm and it is located at "C:\Users\Deb\Desktop\Test.xlsm". The command button is present in the Test.xlsm workbook and If I press the button another workbook Test1.xlsx will be activated(BUT NOT VISIBLE TO USERS) and all the docx file which are available in the below path will be attached in the Test1.xlsx file.

    Path where docx files are available:

    "C:\Users\Deb\Desktop\Test(Folder)"-- Path to docx file

    "C:\Users\Deb\Desktop\Test(Folder)"-- Path to Test1.xlsx

    The format of the Test1.xlsx is as follows:

    Column B: File Name and Column E should be for Attachment.

    If I have File1.docx in cell B1 of Test1.xlsx file then corresponding file will be embedded in E1 again

    If I have File2.docx in cell B7 of Test1.xlsx file then corresponding file will be embedded in E7 again

    If I have File3.docx in cell B10 of Test1.xlsx file then corresponding file will be embedded in E10.

    The operation will be occurred in Test1.xlsx file but the macro will be executed from Test.xlsm file followed by clicking on a command button.

    Thanks!!

     

     

    Monday, May 11, 2015 8:38 AM
  • I think you didn't get me correctly.

    I think I do. ;-)

    I've written 2 answers, one for you and one for rguy72, which has hijacked your post a little. It is not easy to see in this forum which answer belong to whom. I'll do it better next time and write the recipient's name also.

    Have a look above, 3 post earlier for Sub CopyEmbeddedObjects, that is for you.

    Andreas.

    Monday, May 11, 2015 9:16 AM
  • Hi Andreas,

    I have seen your code(CopyEmbeddedObject) and it seems like it is copying the files from the excel itself and paste the embedded files into the sheet in which they are belongs to. But my requirement is 

    I am using a workbook Test.xlsm and it is located at "C:\Users\Deb\Desktop\Test.xlsm". One command button is present in the Test.xlsm workbook and If I press the button another workbook "C:\Users\Deb\Desktop\Test1.xlsxwill be activated(BUT NOT VISIBLE TO USERS) and all the docx file which are available in the below path will be attached in the Test1.xlsx file.

    C:\Users\Deb\Desktop\Desktop(Folder)--Folder where docx files are available.

    Also, in your code I didn't find any reference where you are calling C:\Users\Deb\Desktop\Test1.xlsx  file name. Please note that files will be embedded in Test1.xlsx and macro will be run from Test.xlsm files. This is the only change I want and rest will be similar as your first post.

    Monday, May 11, 2015 5:30 PM
  • Excuse me, but it is really so hard? ;-)

    Again: Open both files, copy the macro below into the file that contains the docx file(s), activate the destination workbook and execute the macro.

    The macro works as you requested, it seems that you don't understand how to refer to objects?

    ThisWorkbook refers always to the workbook which contains the macro
    ActiveWorkbook refers always to the workbook which has the selection
    Workbooks("MyFile.xlsx") refers always to the already opened workbook MyFile.xlsx

    and this code

      Dim Wb1 As Workbook, Wb2 As Workbook
      Set Wb1 = Workbooks.Open("C:\Users\Deb\Desktop\Test1.xlsx")
      Set Wb2 = Workbooks.Open("C:\Users\Deb\Desktop\Test.xlsm")

    opens both files you mention, and Wb1 and Wb2 refers to the relate file.

    And it is also possible to refer to workbooks in other instances of Excel (which is of course more complicated).

    So you can exchange "ThisWorkbook" and/or "ActiveWorkbook" and/or with any of the objects above, that doesn't matter!

    But you would get an error if you try to open the file if it is already opened!

    I don't know which file is opened or closed or where you want to place the macro.
    "If I press the button another workbook" can mean many things, I don't know where the button is and I don't want to know that either.

    Should I write several dozens lines of code only to investigate the situation?
    Even if I do that, such a code is not helpful for you, it would only irritate you!

    IMHO the best for you is a sample that shows the basics and works (with an easy as possible precondition).
    When you got the sample working, you can adapt it to your specials needs afterwards.

    And my code is really simple, we are talking about Copy & Paste, that's all, so please...

    Andreas.
    Tuesday, May 12, 2015 10:03 AM
  • Hi Andreas,

    I have tried everything you mentioned but still no luck.. :(

    I have uploaded my excel files in one drive and using the following link you can access those excel files.

    https://onedrive.live.com/redir?resid=6EBB3882E85BEDFD!109&authkey=!AKogQ6VnrmKEFJc&ithint=folder%2cxlsm


    Tuesday, May 12, 2015 8:13 PM
  • Hi Deb,

    I think you have confused me a bit. :-) I have to apologize, I did not understand you, resp. read what you have written, correctly.

    When the list of the files (that should be embedded) is in a different file, you have to refer to the cells in that file. And you can also have another different file as destination, doesn't matter.

    At the end it's the same, all you have to do is to refer to the right objects.

    Andreas.

    Sub EmbedFiles()
      Dim OO As OLEObject
      Dim R As Range
      Dim Path As String
     
      Dim ImportList As Range
      Dim Destination As Worksheet
     
      'Refer to the destination sheet (doesn't matter where it is)
      Set Destination = Workbooks("test1.xlsx").Sheets("Sheet1")
     
      'refer to the cells that contain the file names (doesn't matter where it is)
      With Workbooks("test1.xlsx").Sheets("Sheet1")
        Set ImportList = .Range("B1", .Range("B" & .Rows.Count).End(xlUp))
      End With
     
      'Path can be also anywhere
      Path = "C:\Users\Deb\Desktop\Test(Folder)\"
     
    '  'Just a different sample, if when you want to select the list with the files:
    '  If Not TypeOf Selection Is Range Then
    '    MsgBox "Select some cells"
    '    Exit Sub
    '  End If
    '  With ActiveWorkbook
    '    'Path of this file
    '    If .Path = "" Then
    '      MsgBox "Save this file into the same folder that contain the Word file(s)"
    '      Exit Sub
    '    End If
    '    Path = .Path & "\"
    '    'This sheet
    '    Set Destination = .ActiveSheet
    '    With Destination
    '      Set ImportList = Selection
    '      'Reduce to the used range (Selection might be a column!)
    '      Set ImportList = Intersect(.UsedRange, ImportList)
    '      If ImportList Is Nothing Then
    '        MsgBox "Selected cells did not contain anything"
    '        Exit Sub
    '      End If
    '    End With
    '  End With
    
      'Visit each cell
      For Each R In ImportList
        If IsEmpty(R) Then GoTo Skip
        'File exists?
        If Dir(Path & R) <> "" Then
          'Embed
          Set OO = Destination.OLEObjects.Add( _
            Filename:=Path & R, Link:=False, DisplayAsIcon:=True, _
            IconFileName:=Application.Path & "\WINWORD.EXE", _
            IconIndex:=0, IconLabel:=R.Value, _
            Left:=R.Offset(, 1).Left, Top:=R.Top)
        End If
    Skip:
      Next
    End Sub

    • Marked as answer by Deb_chatt Friday, May 15, 2015 1:32 PM
    Wednesday, May 13, 2015 10:02 AM
  • Hi Andreas,

    Thanks for the reply. I'm getting Subscript Out of range error message in the following line while executing the code.

    Set Destination = Workbooks("test1.xlsx").Sheets("Sheet1")

    If i use the exact path of test1.xlsx file, then also I'm getting the same error.

    I have test1.xlsx folder ready in my desktop but still I,m getting this error. Any idea?

    Thanks!!

    Wednesday, May 13, 2015 11:00 AM
  • Thanks for the reply. I'm getting Subscript Out of range error message in the following line while executing the code.

    Set Destination = Workbooks("test1.xlsx").Sheets("Sheet1")


    Hi Deb,

    I bet the file is not open. You can open the file with e.g.

    Workbooks.Open "C:\Users\Deb\Desktop\Test1.xlsx"

    but if the file is already opened and you run that line, you have another issue.

    Andreas.

    • Marked as answer by Deb_chatt Thursday, May 14, 2015 9:18 AM
    • Unmarked as answer by Deb_chatt Thursday, May 14, 2015 10:29 AM
    • Marked as answer by Deb_chatt Friday, May 15, 2015 1:31 PM
    Wednesday, May 13, 2015 12:57 PM
  • Hi Andreas,

    Yes you are correct. If I use Workbooks.Open "C:\Users\Deb\Desktop\Test1.xlsx" followed by Set Destination = Workbooks("test1.xlsx").Sheets("Sheet1"), it's working correctly. However I don't want to make visible test1.xlsx file.

    I'm using the following lines but it's not working.

    Private Sub CommandButton1_Click() Dim OO As OLEObject Dim R As Range Dim Path As String Dim ImportList As Range Dim Destination As Worksheet Application.ScreenUpdating = False 'Refer to the destination sheet (doesn't matter where it is) Workbooks.Open "C:\Users\Deb\Desktop\Test1.xlsx" ActiveWindow.Visible = False Set Destination = Workbooks("Test1.xlsx").Sheets("Sheet1") 'refer to the cells that contain the file names (doesn't matter where it is) With Workbooks("test1.xlsx").Sheets("Sheet1") Set ImportList = .Range("B1", .Range("B" & .Rows.Count).End(xlUp)) End With 'Path can be also anywhere Path = "C:\Users\Deb\Desktop\Test(Folder)\" 'Visit each cell For Each R In ImportList If IsEmpty(R) Then GoTo Skip 'File exists? If Dir(Path & R) <> "" Then 'Embed Set OO = Destination.OLEObjects.Add( _ Filename:=Path & R, Link:=False, DisplayAsIcon:=True, _ IconFileName:=Application.Path & "\WINWORD.EXE", _ IconIndex:=0, IconLabel:=R.Value, _ End If Skip: Next

    Workbooks("Test1.xlsx").Close SaveChanges:=True
            Application.ScreenUpdating = True

    End Sub


    Could you please let me know if I'm missing any steps.

    Thanks!!


    • Edited by Deb_chatt Thursday, May 14, 2015 7:12 AM
    Wednesday, May 13, 2015 6:01 PM
  • However I don't want to make visible test1.xlsx file.

    Create a second instance of Excel.

    Andreas.

    Private Sub CommandButton1_Click()
      Dim OO As OLEObject
      Dim R As Range
      Dim Path As String
    
      Dim ImportList As Range
      Dim Destination As Worksheet
      Dim App As Application
      Dim Wb As Workbook
      
      Set App = CreateObject("Excel.Application")
      Set Wb = App.Workbooks.Open("C:\Users\Deb\Desktop\Test1.xlsx")
      Set Destination = Wb.Sheets("Sheet1")
      With Destination
        Set ImportList = .Range("B1", .Range("B" & .Rows.Count).End(xlUp))
      End With
    
      Path = "C:\Users\Deb\Desktop\Test(Folder)\"
    
      'Visit each cell
      For Each R In ImportList
        If IsEmpty(R) Then GoTo Skip
        'File exists?
        If Dir(Path & R) <> "" Then
          'Embed
          Set OO = Destination.OLEObjects.Add( _
            Filename:=Path & R, Link:=False, DisplayAsIcon:=True, _
            IconFileName:=Application.Path & "\WINWORD.EXE", _
            IconIndex:=0, IconLabel:=R.Value, _
            Left:=R.Offset(, 1).Left, Top:=R.Top)
        End If
    Skip:
      Next
      Wb.Close True
      App.Quit
    End Sub
    

    • Marked as answer by Deb_chatt Thursday, May 14, 2015 9:18 AM
    • Unmarked as answer by Deb_chatt Thursday, May 14, 2015 10:28 AM
    • Marked as answer by Deb_chatt Friday, May 15, 2015 1:31 PM
    Thursday, May 14, 2015 8:49 AM
  • Hi Andreas,

    I have checked your code but I would not say it’s producing the exact result. Please find my comments below:

    The code you have provided is executing without producing any error. But after executing your code if I open Test1.xlsx file I’m getting the following error message.

    Excel was unable to open the file by repairing the unreadable content.

    The docx files embedded in Test1.xlsx file are no longer accessible. I am not able to open any of the embedded objects from test1.xlsx file by double clicking on them. Is it an issue with my excel version. Currently I’m using Excel 2010.

    I have uploaded Test.xlsm and Test1.xlsx in the below mentioned path for your reference.

     https://onedrive.live.com/redir?resid=6ebb3882e85bedfd!117&authkey=!AGXiivCaORokaWg&ithint=folder%2cxlsm

    Thursday, May 14, 2015 10:29 AM
  • Excel was unable to open the file by repairing the unreadable content.

    It's related to your Word files, the file size is 0 bytes (inside the Excel file).

    Andreas.

    • Marked as answer by Deb_chatt Friday, May 15, 2015 1:31 PM
    Friday, May 15, 2015 7:33 AM
  • Thanks for the reply Andreas!! Now it is working exactly as it should.  :)

    Just a quick question for my information.

    As per your code If 

     File1.docx is present in cell B1 of Test1.xlsx file then corresponding file is embedding in E1. But if I want it to embed it in the next row what exactly I need to change in my code?

    For example..

    If File1.docx is present in cell B1 of Test1.xlsx file then corresponding file will be embedded in E2

    If File2.docx is present in cell B5 of Test1.xlsx file then corresponding file will be embedded in E6

    If File3.docx is present in cell B10 of Test1.xlsx file then corresponding file will be embedded in E11

    I have tried with the below line. But no luck.

    Set OO = Destination.OLEObjects.Add( _
            Filename:=Path & R, Link:=False, DisplayAsIcon:=True, _
            IconFileName:=Application.Path & "\WINWORD.EXE", _
            IconIndex:=0, IconLabel:=R.Value, _
            "prettyprint">

     Thanks!!



    
    
    • Edited by Deb_chatt Friday, May 15, 2015 9:00 AM
    • Marked as answer by Deb_chatt Friday, May 15, 2015 1:31 PM
    • Unmarked as answer by Deb_chatt Friday, May 15, 2015 1:31 PM
    Friday, May 15, 2015 8:38 AM
  • File1.docx is present in cell B1 of Test1.xlsx file then corresponding file is embedding in E1. But if I want it to embed it in the next row what exactly I need to change in my code?

    You can set the Left and Top property to any position you like, just refer to the related cell:

    Set OO = Destination.OLEObjects.Add( _
            Filename:=Path & R, Link:=False, DisplayAsIcon:=True, _
            IconFileName:=Application.Path & "\WINWORD.EXE", _
            IconIndex:=0, IconLabel:=R.Value, _
            Left:=R.Left, Top:=R.Offset(1, 0).Top)

    Andreas.

    • Marked as answer by Deb_chatt Friday, May 15, 2015 1:31 PM
    Friday, May 15, 2015 9:49 AM
  • You ara a genius Andreas!! 

    I have some other questions but I'm creating a separate thread for that. Thanks a lot for your help!!! :)

    Friday, May 15, 2015 1:31 PM