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 Mul­ti­ple objects from a folder in Microsoft Excel, you need to fol­low the steps below:

  • Cre­ate the object of FileSys­te­mOb­ject(Link)
  • Cre­ate Folder object using FileSys­te­mOb­ject and Get­Folder (link) method and count the num­ber 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


Cre­ate the object of FileSystemObject

Set fso = CreateObject(“Scripting.FileSystemObject”)

Cre­ate Folder object using FileSys­te­mOb­ject and Get­Folder  method and count the num­ber of files.

NoOf­Files = fso.GetFolder(Folderpath).Files.Count

Get the files Object

Set list­files = fso.GetFolder(Folderpath).Files

Run the Loop for each files in folder

For Each fls In listfiles

str­Comp­FilePath = Fold­er­path & “\” & 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

 

Com­plete 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 par­tic­u­lar loca­tions. thn how to do this?

    • sumitjain says:

      Abhi­tosh, Could you please elab­o­rate your query?

      If you mean to add the doc file in par­tic­u­lar loca­tion in an excel file,
      yes we can do it, if you see the above exam­ple
      “Sheets(“Object”).Range(“B” & ((Counter — 1) * 3) + 1).Select“
      in the par­tic­u­lar sheet, select the range where you want to add your doc using “.Select” before you add the doc­u­ment to the sheet
      Say you want to add to “C5” cell in Sheet1
      “Sheets(“Sheet1”).Range(“C5”).Select“
      and then add the doc­u­ment.
      ( it works sim­i­lar to Copy paste oper­a­tion 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 look­ing for

      Thanks
      Sumit Jain

  2. Abhitosh Verma says:

    If we need to add some doc files at par­tic­u­lar loca­tions then how to do this?
    is there any syn­tax to add files at some location?

  3. Abhitosh Verma says:

    Hi Sumit, sorry for reply­ing you late.:) …I wanted to add the doc­u­ments at some loca­tion within the cell. In your case it adds on the top of that par­tic­u­lar cell. More­over 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 fur­ther documents .

  4. jackcluseau says:

    Hi Ashutosh,

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

    The sce­nario is explained below:

    My work­book has two tabs, one is named “birth­date­names” which has birth­day names with date of birth” and sec­ond tab is named “Images”

    The macro cod­ing checks the cur­rent day for any birth­date match­ing with data in the “birth­date­names” tab, if matches drafts mails for those num­ber of col­leagues from the “birth­date­names” tabs.

    Birth­day mails are drafted using the above macro every­day and I have already pre­pared the draft for send­ing mail on the basis of vba macro but what I am try­ing to design that the images for the email is picked from the next work­sheet tab named “images”:

    Here’s what I am look­ing for

    1. excel vba code picks up the images from the work­sheet tab name “Images” for each of the mail. Note: one image is used for one per­son only once and should not be repeated twice for the sec­ond 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 birth­day mails

    Please help me as I have to present it for my pre­sen­ta­tion by monday.

    I hope I can count on you.

  5. sudheer says:

    Hi Sumit,

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

    Regards,
    Sudheer.

  6. Siddharth pai says:

    can any­one explain me the vba code in lay­man lan­guage? new to vba

    • SJ says:

      i am work­ing on a plan in which vba will be taught sequen­tially 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 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.

  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: