Add photo in excel from local folder

Asked

Viewed 281 times

0

Hello,

I followed every step here - Add photo in excel spreadsheet from local folder And it all worked out well.

But I wanted to resize the images and align them to the center of the cell - horizontal and vertical.

Since I didn’t know how to do it, I edited it like this: oCell.Width / 1.2, oCell.Height / 1.2

The image was the size you wanted in relation to the cell, but it’s always aligned at the top left. How do I now align the image to the center of the cell?

image excel vba

  • And by the way how it works if there are 2 or more sheets in the file?

1 answer

2


When you say you "edited" I’m guessing you changed that code snippet:

With oImage
    .Left = oCell.Left
    .Top = oCell.Top
    .Width = oCell.Width
    .Height = oCell.Height
End With

For that reason:

With oImage
    .Left = oCell.Left
    .Top = oCell.Top
    .Width = oCell.Width / 1.2
    .Height = oCell.Height / 1.2
End With

That is, in practice you have reduced the size of the image. So, to center the image in the cell (without worrying about how much factor has been applied) the easiest way is for you to add to the top coordinates (Left and Top) half the size of the cell and half the size of the image (which then needs to be calculated before, depending on the factor you are applying). Try so:

factor = 1.2 ' Separei em uma variável só pra deixar claro que você pode
             ' mudar sem precisar mexer nas duas linhas finais.
With oImage
    .Width = oCell.Width / factor
    .Height = oCell.Height / factor
    .Left = oCell.Left + oCell.Width / 2 - .Width / 2
    .Top = oCell.Top + oCell.Height / 2 - .Height / 2
End With

  • Hi @Guilhermenascimento. Thank you! To tell you the truth, I haven’t tried it (Ricardo, any feedback? ). But in principle it is correct yes, because the idea is to discount half of the image height itself (so the .Height alone). Otherwise, as you suggest, you add and subtract the same value, don’t you? rs :)

  • 1

    It’s been a long time since I worked with VB, I wanted to say that oImage.Height, but only now do I realize With, thanks, the +1 is guaranteed ;)

  • @Guilhermenascimento Relax. It was good to have asked, because there confirms the understanding. :)

  • 1

    Perfect. It worked perfectly. Thank you very much.

  • I’m glad you solved it, Ricardo. In that case, please consider marking the answer as accepted. Learn more here: http://answall.com/help/someone-answers

Browser other questions tagged image excel vba

You are not signed in. Login or sign up in order to post.