1

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.

  • You may like to post what code you have tried that seem to have worked for a single object, so that it may be suitably modified to loop thru all objects. – patkim Aug 26 '18 at 0:18
  • Thank you for your response. I have added the code as you have requested and also suggested the problems I was having with this code. If there are any other ideas which can achieve the same objective, for example changing the default settings, that will be useful. Also is there a way to find the total number of objects on a worksheet and preferably, to find that the total number of objects of a certain type (objects with zip extension) on a worksheet – Ramana Aug 26 '18 at 11:26
0

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.

enter image description here

| improve this answer | |
  • Thank you. Works like a charm! An additional question if you have time to answer-how do you make these kind of images which have recording for a short period. Instead of answering perhaps you can just give me a keyword and I will go ahead and search for it and learn about this. Once again, a big thank you. I'm marking this issue is resolved – Ramana Aug 27 '18 at 16:08
  • I think you mean to ask how did I create a screen recording as GIF image file that's inserted in my above answer. I used a freeware called ScreenToGIF. – patkim Aug 28 '18 at 7:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.