-5

Been reading a lot if different threads about inserting images and re-sizing them but cannot find one that does exactly what I want it to do.

So say I have a spreadsheet with 2 rows. Column A which is the image column and Column B which is the Image Name.

I want to have a script that will run through each value in column B, insert the image that matches that name into the same Row on Column A, Resize it to fit the size of the cell which is 150 high by 18 wide, then move on to the next row and repeat through the spreadsheet.

Community
  • 1
  • 1
Reg
  • 555
  • 5
  • 10
  • 26
  • If you've read a lot, then presumably you've tried some of those things. Please tell us [what have you tried so far](http://whathaveyoutried.com) and what you'd like to change/fix about the method(s) you have tried already. – David Zemens Mar 23 '13 at 16:34

1 Answers1

4

Here is a sample that will iterate over a range of cells (B1:B100) which you can modify, and uses the filename from the cell one column to the left (so, from Column A), and sizes the image to fit within cell in column B.

Sub InsertPic()
Dim pic As String 'file path of pic
Dim myPicture As Picture 'embedded pic
Dim rng As Range 'range over which we will iterate
Dim cl As Range 'iterator

Set rng = Range("B7:B7")
For Each cl In rng
    pic = cl.Offset(0, -1)

        Set myPicture = ActiveSheet.Pictures.Insert(pic)
        '
        With myPicture
            .ShapeRange.LockAspectRatio = msoFalse
            .Width = cl.Width
            .Height = cl.Height
            .Top = Rows(cl.Row).Top
            .Left = Columns(cl.Column).Left
        End With
        '

Next

End Sub

There is no error-handling in this code to account for invalid filenames, you will probably want to add that.

David Zemens
  • 53,033
  • 11
  • 81
  • 130
  • I tried using different URLS, but the ones I have to use aren't working. I get 1004: Insert property od picture object cannot be assigned (translated). Any ideas? Here is how my URL looks: https://imissit.blob.core.windows.net/iris/596480cf967e0c990c37fba3725ada0c/0a78efee-493c-40df-9202-6de8d5b769f2/codes/qsw.png – 4ndro1d Nov 29 '16 at 22:22
  • @4ndro1d you should [ask your own question](http://stackoverflow.com/questions/ask). Remember when doing so to show the code that you're using and describe your problem fully and in detail. Cheers. – David Zemens Nov 30 '16 at 11:31
  • @4ndro1d here's a hint, though: you can't provide a URL path to an image using the above method, you first need to download the files. This can also be done with VBA. Try to figure that out (there are other Q's here on that topic) and ask your own question if you get stuck. – David Zemens Nov 30 '16 at 11:32