Upload file to a certain coordinate/cell by using a button

[Beino]

Hi,

I have been trying to find a solution to how I can upload a file to a specific coordinate in my Excel sheet. I have so far created an ActiveX Command Button by using the following code:

Private Sub CommandButton1_Click()
Application.Dialogs(xlDialogInsertObject).Show
End Sub

When I hit the button, the file uploads either randomly in the sheet or on the button itself, which becomes a problem when I want to upload many files. Therefore, is there a way I can have the file attached in a certain cell or coordinate in the Excel sheet? And then have the second file attached in another cell/coordinate.. and the third etc.?

Would be great if someone could help me with this!

Thanks

//Henrik

 

[John_w]

Welcome to MrExcel forums.

The dialogue inserts the object at the active cell, so simply select the cell where you want the object to be inserted before displaying the dialogue, for example:

Private Sub CommandButton1_Click()
     Range("B2").Select
     Application.Dialogs(xlDialogInsertObject).Show
End Sub

Expanding this method, the following code inserts the first object at the active cell and subsequent objects 1 row below the previously inserted object.

Public Sub Insert_Object()

    Static insertAtCell As Range
    Dim ws As Worksheet
    Dim newOLEobject As OLEObject
    Dim numOLEobjects As Integer
    
    If insertAtCell Is Nothing Then Set insertAtCell = ActiveCell
    insertAtCell.Select
    
    Set ws = ActiveSheet
    numOLEobjects = ws.OLEObjects.Count
    
    Application.Dialogs(xlDialogInsertObject).Show
    
    If ws.OLEObjects.Count = numOLEobjects + 1 Then
            
        'Insert object dialogue was not cancelled
            
        Set newOLEobject = ws.OLEObjects(ws.OLEObjects.Count)
                
        'Set cell for next object 1 row below the object that was just inserted
        
        Set insertAtCell = ws.Cells(newOLEobject.BottomRightCell.Row + 1, newOLEobject.TopLeftCell.Column)
            
    End If
    
End Sub

PS please post VBA code within CODE tags - the # icon in the message editor.

 

[Beino]

Thank you and thank you! The first code was exactly what I was after - highly appreciated.

Is there a way to change the size of the added icon, so that the icon gets smaller?

 

[John_w]

Set the Width and Height properties:

Private Sub CommandButton1_Click()

    Dim ws As Worksheet
    Dim numOLEobjects As Integer
    Dim newOLEobject As OLEObject
    
    Set ws = ActiveSheet
    numOLEobjects = ws.OLEObjects.Count
    
    Range("B2").Select
    Application.Dialogs(xlDialogInsertObject).Show
    
    If ws.OLEObjects.Count = numOLEobjects + 1 Then
    
        'Insert object dialogue was not cancelled
    
        Set newOLEobject = ws.OLEObjects(ws.OLEObjects.Count)
        newOLEobject.Width = 100
        newOLEobject.Height = 200
    End If

End Sub