VBA to Browse insert an Object in excel in specific cell

kkyuvaraj · Apr 15th 2020

Hi,

I am looking for an VBA code to browse and insert an object in a specific cell as follows in the attached excel.

1.If i click browse G3, it shoud open a file explorer to choose a file.

2. Then if i select a file and attach, it shoud attach in cell F3 in centre.

3.The attached file should be with the same name of the file and with the same icon (For Eg.If the attached filr is PDF, then the icon shold be PDF, if fie is excel then the file icon should be excel and so on)

4.In the same way if i browse G4, file should be attached in F4 and so on.

Experts, pls help to create a VBA code in the attached Excel.

trunten · Apr 16th 2020

Code

Option Explicit

Sub attachFile(ByRef Target As Range)
    Dim fn, o
    
    If Target Is Nothing Then Exit Sub
    If Target.Count > 1 Then Exit Sub
    
    fn = Application.GetOpenFilename
    If TypeName(fn) <> "Boolean" Then
        Set o = Target.Parent.OLEObjects.Add(Filename:=fn, Link:=False, DisplayAsIcon:=False)
        If Target.Height < o.Height Then Target.RowHeight = o.Height
        Application.ScreenUpdating = False
        While Target.Width < o.Width
            Target.ColumnWidth = Target.ColumnWidth + 0.1
        Wend
        Application.ScreenUpdating = True
        o.Top = Target.Top
        o.Left = Target.Left
    End If
End Sub

Sub eg()
    attachFile Range("a2")
End Sub

Display More

AussieJohn · Mar 9th 2022

Hello,

I tried using your code and it works well. One thing that I would like it to do for me is just show as an Icon of about 1.5cm x 1.5cm in size instead of showing the whole document. When the icon is then clicked for it open up an embeded file (e.g. pdf or jpg).

Would you be so kind as to show me the alternative code for this.

Thank you very much

VBA to Browse insert an Object in excel in specific cell

Participate now!

Share

Tags