How to get smaller size of an icon for an embedded file by default or by using a macro in Microsoft Excel

Question

I'm using Microsoft Excel 2010 on Windows 7. I have an assignment that requires me to keep on embedding objects of a compressed file into a cell in a Microsoft Excel worksheet every few minutes.

The icon of the compressed file is large and takes up the height of three adjacent cells. So every time I embed the compressed file, I right click on the icon and then select the format object menu item, followed by changing the size of the icon to 50% of its original size.

Is there a way to set the default size of an icon in Microsoft Excel? If not is there a way to automate the procedure of setting the format of the embedded object using a macro? I searched a lot but could not find anything. I tried VBA script for one particular object but cannot do it for any object

Thank you for any inputs

Edit: thank you for the comment requiring me to post to the macro. Here is a macro that I had recorded but it is only for a specific object which is object 21. There are multiple problems with this code which I am outlining below the code

Sub Reduce_size_icon() 
' 
' Reduce_size_icon Macro
' Reduce the size of the icon 
'
'
    ActiveSheet.Shapes("Object 21").LockAspectRatio = msoTrue
    ActiveSheet.Shapes("Object 21").Height = 20.25
    ActiveSheet.Shapes("Object 21").Width = 69.75 End Sub

1. This is only for a specific object. One way maybe to loop through all the possible objects on a worksheet and then reduce their height. But if I am adding the objects one by one, the macro needs to run only on the last object and not all of the ones prior to do it because they have already been formatted, especially if we want the height and width to be reduced by 50% of the current dimensions. (See point 2)
2. it would be better if we can reduce the height by half instead of assigning absolute values to the height. This is because sometimes I may have objects of different files and therefore of different icon sizes.
3. After going through your comment, I realized that maybe it is much easier to create a macro which will automatically embed and format an object into the highlighted cell on which the macro is run. However, what we want is that the object should be created from a different file every time. So is it possible that the macro can run through exactly the same steps of building an object but at the point where it prompts you for the file path, it can stop and allow the user to choose a particular file?

And of course, the easiest way is to be able to specify the size of the icon to default values so that it always embeds with a smaller size.

Answer 1

I am not too sure if there's any setting anywhere in Excel that specifies the default size of Icon for the embedded object. If there is, I would really like to know about it. It will make the job very simple.

At the moment I just suggest a small macro that goes like this.

This is tested on Excel 2013 and I hope it should work on Excel 2010 as well.

In your current worksheet Press ATL + F11 to Access VBA Editor. Insert a Module and then paste the following code into it.

Sub SelectOLE()
Dim objFileDialog As Office.FileDialog
    Set objFileDialog = Application.FileDialog(MsoFileDialogType.msoFileDialogFilePicker)

        objFileDialog.AllowMultiSelect = False
        objFileDialog.ButtonName = "Select File"
        objFileDialog.Title = "Select File"
        objFileDialog.Show

        If (objFileDialog.SelectedItems.Count > 0) Then

        Set f = ActiveSheet.OLEObjects.Add _
            (Filename:=objFileDialog.SelectedItems(1), _
              Link:=False, _
              DisplayAsIcon:=True, _
              IconLabel:=objFileDialog.SelectedItems(1), _
              Top:=ActiveCell.Top, _
              Left:=ActiveCell.Left _
             )
        f.Select
        f.Width = 18   'Adjust as per your needs
        f.Height = 18  'Adjust as per your needs

        End If

End Sub

You may assign a keyboard shortcut to this macro as well. Save your file as .xlsm Macro Enabled Excel Workbook.

Now every time you need to embed an object, first click on the cell where you wish to embed and run this macro. Select the file from the File Open Dialog Box and Click Open Button.

You can best decide the Width & Height parameters as per your needs in the code. Just note that this code may not be robust and have not been tested for any outliers or exception handling as such.

See the screenshot below, how this works.