How to insert Object into Excel Sheet using VBA?
- Enable Microsoft Scripting Runtime for creating Object of FileSystemObject. Enable Microsoft Scripting Runtime -> Tools -> References -> Check Microsoft Scripting Runtime.
- Create Folder Object From FileSystemObject
- Get File Object
- Run the For Each Loop for every single Folder Files.
- Insert File Object into an Excel Sheet.
Insert Word File into Excel Sheet as Word Icon from Early Binding
Sub Insert_Object_1()
‘Insert Word Object into Excel Sheet and Display Word Object Icon
‘Use these statement helps Program Run Fast
On Error Resume Next
With Application
.DisplayAlerts = False
.ScreenUpdating = False
.Calculation = xlCalculationManual
End With
Dim FSO As New FileSystemObject ‘Create new instance of FileSystemObject
Dim Main_Folder As Folder
Dim Folder_File As File
Dim Folder_Path As String
Dim i As Integer
Dim Extension As Variant
‘Paste Folder Path
With Application.FileDialog(msoFileDialogFolderPicker)
.Title = “Select Word Folder”
.Show
Folder_Path = .SelectedItems(1) & “\” ‘Assing Folder Path into Variable
End With
Set Main_Folder = FSO.GetFolder(Folder_Path) ‘Create Folder Object
i = 1
For Each Folder_File In Main_Folder.Files
Extension = Split(Folder_File, “.”)
If Extension(Ubound(Extension)) Like “do*” Then
ActiveSheet.Range(“a” & i).Select
ActiveSheet.OLEObjects.Add(Filename:= _
Folder_File, Link:=True, _
DisplayAsIcon:=True, IconFileName:= _
“C:\Windows\Installer\{90160000-0011-0000-0000-0000000FF1CE}\wordicon.exe”, _
IconIndex:=0, IconLabel:=Folder_File _
).Select
ActiveSheet.Rows(i).RowHeight = 51
i = 1 + i
End If
Next Folder_File
‘After Complete Program we do Enable these
With Application
.DisplayAlerts = True
.ScreenUpdating = True
.Calculation = xlCalculationAutomatic
End With
MsgBox “Done !!!!”
End Sub
Insert Excel File into Excel Sheet as Excel Icon from Early Binding
Sub Insert_Object_2()
‘Insert Excel Object into Excel Sheet and Display Excel Object Icon
‘Use these Syntax helps Program Run Fast
On Error Resume Next
With Application
.DisplayAlerts = False
.ScreenUpdating = False
.Calculation = xlCalculationManual
End With
Dim FSO As New FileSystemObject ‘Create new instance of FileSystemObject
Dim Main_Folder As Folder
Dim Folder_File As File
Dim Folder_Path As String
Dim i As Integer
Dim Extension As Variant
‘Paste Folder Path
With Application.FileDialog(msoFileDialogFolderPicker)
.Title = “Select Word Folder”
.Show
Folder_Path = .SelectedItems(1) & “\” ‘Assing Folder Path into Variable
End With
Set Main_Folder = FSO.GetFolder(Folder_Path) ‘Create Folder Object
i = 1
For Each Folder_File In Main_Folder.Files
Extension = Split(Folder_File, “.”)
If Extension(Ubound(Extension)) Like “xl*” Then
ActiveSheet.Range(“a” & i).Select
ActiveSheet.OLEObjects.Add(Filename:= _
Folder_File, Link:=True, _
DisplayAsIcon:=True, IconFileName:= _
“C:\Windows\Installer\{90160000-0011-0000-0000-0000000FF1CE}\xlicons.exe”, _
IconIndex:=0, IconLabel:=Folder_File _
).Select
ActiveSheet.Rows(i).RowHeight = 51
i = 1 + i
End If
Next Folder_File
‘After Complete Program we do Enable these
With Application
.DisplayAlerts = True
.ScreenUpdating = True
.Calculation = xlCalculationAutomatic
End With
MsgBox “Done !!!!”
End Sub
Insert Excel File into Excel Sheet as Excel Icon from Late Binding
Sub Insert_Object_3()
‘Insert Word Object into Excel Sheet and Display Word Object Icon
‘Use these Syntax helps Program Run Fast
On Error Resume Next
With Application
.DisplayAlerts = False
.ScreenUpdating = False
.Calculation = xlCalculationManual
End With
Dim FSO As Object
Dim Main_Folder As Object
Dim Folder_File As Object
Dim Folder_Path As String
Dim i As Integer
Dim Extension As Variant
Set FSO = CreateObject(“Scripting.FileSystemObject”) ‘Create FileSystemObject Object
With Application.FileDialog(msoFileDialogFolderPicker)
.Title = “Select Word Folder”
.Show
Folder_Path = .SelectedItems(1) & “\” ‘Assing Folder Path into Variable
End With
Set Main_Folder = FSO.GetFolder(Folder_Path) ‘Create Folder Object
i = 1
For Each Folder_File In Main_Folder.Files
Extension = Split(Folder_File, “.”)
If Extension(1) Like “do*” Then
ActiveSheet.Range(“a” & i).Select
ActiveSheet.OLEObjects.Add(Filename:= _
Folder_File, Link:=True, _
DisplayAsIcon:=True, IconFileName:= _
“C:\Windows\Installer\{90160000-0011-0000-0000-0000000FF1CE}\wordicon.exe”, _
IconIndex:=0, IconLabel:=Folder_File _
).Select
ActiveSheet.Rows(i).RowHeight = 51
i = 1 + i
End If
Next Folder_File
‘After Complete Program we do Enable these
With Application
.DisplayAlerts = True
.ScreenUpdating = True
.Calculation = xlCalculationAutomatic
End With
MsgBox “Done !!!!”
End Sub
Insert Excel File into Excel Sheet as Excel Icon from Late Binding
Sub Insert_Object_4()
‘Insert Excel Object into Excel Sheet and Display Excel Object Icon
‘Use these Syntax helps Program Run Fast
On Error Resume Next
With Application
.DisplayAlerts = False
.ScreenUpdating = False
.Calculation = xlCalculationManual
End With
Dim FSO As Object ‘Create new instance of FileSystemObject
Dim Main_Folder As Object
Dim Folder_File As Object
Dim Folder_Path As String
Dim i As Integer
Dim Extension As Variant
Set FSO = CreateObject(“Scripting.FileSystemObject”)
With Application.FileDialog(msoFileDialogFolderPicker)
.Title = “Select Word Folder”
.Show
Folder_Path = .SelectedItems(1) & “\” ‘Assing Folder Path into Variable
End With
Set Main_Folder = FSO.GetFolder(Folder_Path) ‘Create Folder Object
i = 1
For Each Folder_File In Main_Folder.Files
Extension = Split(Folder_File, “.”)
If Extension(Ubound(Extension)) Like “xl*” Then
ActiveSheet.Range(“a” & i).Select
ActiveSheet.OLEObjects.Add(Filename:= _
Folder_File, Link:=True, _
DisplayAsIcon:=True, IconFileName:= _
“C:\Windows\Installer\{90160000-0011-0000-0000-0000000FF1CE}\xlicons.exe”, _
IconIndex:=0, IconLabel:=Folder_File _
).Select
ActiveSheet.Rows(i).RowHeight = 51
i = 1 + i
End If
Next Folder_File
‘After Complete Program we do Enable these
With Application
.DisplayAlerts = True
.ScreenUpdating = True
.Calculation = xlCalculationAutomatic
End With
MsgBox “Done !!!!”
End Sub
