How to insert Object into Excel Sheet using VBA?

  1. Enable Microsoft Scripting Runtime for creating Object of FileSystemObject. Enable Microsoft Scripting Runtime -> Tools -> References -> Check Microsoft Scripting Runtime.

how to insert object in excel using vba

how to insert object in excel 2016

  1. Create Folder Object From FileSystemObject
  2. Get File Object
  3. Run the For Each Loop for every single Folder Files.
  4. 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

how to insert object in excel

insert object into excel

Download Workbook

What is Procedure in VBA?

Leave a Reply

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