Show Buttons
Share On Facebook
Share On Twitter
Share On Google Plus
Share On Linkdin
Share On Reddit
Contact us
Hide Buttons

VBA-Excel: Add/Insert multiple objects from a folder in an Excel Document.

To Add or Insert Multiple objects from a folder in Microsoft Excel, you need to follow the steps below:

  • Create the object of FileSystemObject(Link)
  • Create Folder object using FileSystemObject and GetFolder (link) method and count the number of files.
  • Get the files Object
  • Run the Loop for each files in folder
  • Insert the File objects , using OLE objects.
  • Insert the added files name list in another sheet(just for reference)
  • Save the workbook


Create the object of FileSystemObject

Set fso = CreateObject(“Scripting.FileSystemObject”)

Create Folder object using FileSystemObject and GetFolder  method and count the number of files.

NoOfFiles = fso.GetFolder(Folderpath).Files.Count

Get the files Object

Set listfiles = fso.GetFolder(Folderpath).Files

Run the Loop for each files in folder

For Each fls In listfiles

strCompFilePath = Folderpath & “\” & Trim(fls.Name)

Insert the File objects , using OLE objects.

ActiveSheet.OLEObjects.Add(Filename:=strCompFilePath, Link:= _

False, DisplayAsIcon:=True, IconIndex:=1, IconLabel:=strCompFilePath).Select

Sheets(“Object”).Activate

Sheets(“Object”).Range(“B” & ((Counter – 1) * 3) + 1).Select

Insert the added files name list in another sheet(just for reference)

Range(“A” & Counter).Value = fls.Name

Save the workbook

mainWorkBook.Save

 

Complete Code:

Sub AddOlEObject()

Dim mainWorkBook As Workbook

Set mainWorkBook = ActiveWorkbook

Folderpath = "D:\Insert"

Set fso = CreateObject("Scripting.FileSystemObject")

    NoOfFiles = fso.GetFolder(Folderpath).Files.Count

    Set listfiles = fso.GetFolder(Folderpath).Files

    For Each fls In listfiles

        Counter = Counter + 1

        Range("A" & Counter).Value = fls.Name

        strCompFilePath = Folderpath & "\" & Trim(fls.Name)

        If strCompFilePath <> "" Then

            ActiveSheet.OLEObjects.Add(Filename:=strCompFilePath, Link:= _

                False, DisplayAsIcon:=True, IconIndex:=1, IconLabel:=strCompFilePath).Select

                Sheets("Object").Activate

            Sheets("Object").Range("B" & ((Counter - 1) * 3) + 1).Select

        End If

    Next

mainWorkBook.Save

End Sub
Insert OLE Object-1

Insert OLE Object-1

Insert OLE Object-2

Insert OLE Object-2

You may also like...

15 Responses

  1. Abhitosh Verma says:

    if we need to add some doc files at particular locations. thn how to do this?

    • sumitjain says:

      Abhitosh, Could you please elaborate your query?

      If you mean to add the doc file in particular location in an excel file,
      yes we can do it, if you see the above example
      “Sheets(“Object”).Range(“B” & ((Counter – 1) * 3) + 1).Select”
      in the particular sheet, select the range where you want to add your doc using “.Select” before you add the document to the sheet
      Say you want to add to “C5” cell in Sheet1
      “Sheets(“Sheet1”).Range(“C5″).Select”
      and then add the document.
      ( it works similar to Copy paste operation in excel , you can read http://excel-macro.tutorialhorizon.com/vba-excel-copypaste-data-copy-the-range-of-data-and-paste-it-to-another-range/ )
      please let me know if this is not you were looking for

      Thanks
      Sumit Jain

  2. Abhitosh Verma says:

    If we need to add some doc files at particular locations then how to do this?
    is there any syntax to add files at some location?

  3. Abhitosh Verma says:

    Hi Sumit, sorry for replying you late.:) …I wanted to add the documents at some location within the cell. In your case it adds on the top of that particular cell. Moreover can you please tell if there is some blank row in between the excel sheet then how to add the logic in code to skip that row and to add further documents .

  4. jackcluseau says:

    Hi Ashutosh,

    I need your help with regard to a vba code that will work on the below scenario:

    The scenario is explained below:

    My workbook has two tabs, one is named “birthdatenames” which has birthday names with date of birth” and second tab is named “Images”

    The macro coding checks the current day for any birthdate matching with data in the “birthdatenames” tab, if matches drafts mails for those number of colleagues from the “birthdatenames” tabs.

    Birthday mails are drafted using the above macro everyday and I have already prepared the draft for sending mail on the basis of vba macro but what I am trying to design that the images for the email is picked from the next worksheet tab named “images”:

    Here’s what I am looking for

    1. excel vba code picks up the images from the worksheet tab name “Images” for each of the mail. Note: one image is used for one person only once and should not be repeated twice for the second and so on..example bday1 image is used for A and bday2 image is used for B etc.
    Once the image is used, the macro should not use it again when the next day macro is re-run to send birthday mails

    Please help me as I have to present it for my presentation by monday.

    I hope I can count on you.

  5. sudheer says:

    Hi Sumit,

    why only for excel sheets, I am not getting the name in the object? could you help in this?

    Regards,
    Sudheer.

  6. Siddharth pai says:

    can anyone explain me the vba code in layman language? new to vba

    • SJ says:

      i am working on a plan in which vba will be taught sequentially from the very basic. I will send to a mail once that is done. Thanks for the patience .

  7. Mervin Knight says:

    SJ – how you gettin’ on with this? I’d be very interested when you’ve completed it. Can you email me the details also – that would be great.

  8. Mervin Knight says:

    SJ — how you get­tin’ on with this? I’d be very inter­ested when you’ve com­pleted it. Can you email me the details also — that would be great.

Leave a Reply

Your email address will not be published. Required fields are marked *

%d bloggers like this: