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
if we need to add some doc files at particular locations. thn how to do this?
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
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?
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 .
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.
Hello,
Have you stored the images in specific cells under images tab, if yes then what you can do is once the image is used , set the flag in the next cell as “used” and when ever u r picking an image, check the next cell of image, if it is used then pick the next image.
Other solution is keep storing the used images name in a separate column and when ever u r using a new image, check whether its name is not present in that column.
Hope thats what you where looking for
Thanks
Sumit
Hi Sumit,
is there a way you can help me with a vba code that will help me your example, which is exactly what i am looing for.
Its a kind of urgent matter.
Regards,
Jack
Hello Jack,
I wrote the code for your requirement.
Please read:
http://excel-macro.tutorialhorizon.com/excel-vba-send-unique-images-embedded-to-mail-body-with-every-mail-from-ms-outlook-using-excel/
Note : i have stored all the images in a folder
Do let me know if you need any further assistance. 🙂
Thanks
Sumit Jain
Hi Sumit,
why only for excel sheets, I am not getting the name in the object? could you help in this?
Regards,
Sudheer.
I couldn’t understand your question. please elaborate. Which object name is not getting???
can anyone explain me the vba code in layman language? new to vba
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 .
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.
can you explain, what details you are looking for
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.