Solved

Aligning graphics top left in the target cell after rotation using VBA

Posted on 2013-11-12
13
744 Views
Last Modified: 2013-11-15
Dear Experts:

Below macro, courtesy of Rgonzo1971, batch inserts pictures in Column D. The network paths of these pictures are located in Column C.

The macro works just fine and I was able to tweak it a little bit ...
... i.e. I also integrated an image rotation action for certain images.

The trouble is that the images get rotated around its centers and afterwards the graphics are not aligned top left anymore as are the other pictures.

Has anybody a solution to this problem?

Help is much appreciated. Thank you very much in advance.

Regards, Andreas


Sub InsertPictures()
'
' by Rgonzo, EE
'
Dim c As Range
Dim Image As Picture
'
For Each c In Range(Range("C2"), Range("C" & Rows.Count).End(xlUp))
    c.Offset(0, 1).Activate
    Set Image = ActiveSheet.Pictures.Insert(c.Value2)
    
        If Image.Height > Image.Width Then
           Image.ShapeRange.Rotation = 90
    
        If Image.Height > Application.CentimetersToPoints(4) Then
        Image.Height = Application.CentimetersToPoints(4)
        
        End If
        
        End If
     
Next
End Sub

Open in new window

0
Comment
Question by:AndreasHermle
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 2
  • 2
  • +1
13 Comments
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 39643888
Try this modification

Sub InsertPictures()
'
' by Rgonzo, EE
'
Dim c As Range
Dim Image As Picture
'
For Each c In Range(Range("C2"), Range("C" & Rows.Count).End(xlUp))
    c.Offset(0, 1).Activate
    Set Image = ActiveSheet.Pictures.Insert(c.Value2)
    Imtop=image.top
    Imleft=image.left
   
        If Image.Height > Image.Width Then
           Image.ShapeRange.Rotation = 90
   
        If Image.Height > Application.CentimetersToPoints(4) Then
        Image.Height = Application.CentimetersToPoints(4)
       
        End If
       
        End If
     image.top=Imtop
     image.left=Imleft
Next
End Sub
0
 

Author Comment

by:AndreasHermle
ID: 39644000
Hi Rgonzo,

thank you very much for your quick and professional help.

Although this looks very logical to me, it actually does not do anything.

Any idea why ?

A quick search on the internet on a similar subject produced a code snippet that -at least- repositioned the graphics to various degrees to the right (still with the top alignment)

Image.ShapeRange.IncrementLeft Image.ShapeRange.Height / 2 - Image.ShapeRange.Width / 2
           Image.ShapeRange.IncrementTop Image.ShapeRange.Width / 2 - Image.ShapeRange.Height / 2

Regards, Andreas
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 39644148
When you select one of the rotated images, does the top left of the selection appear over the top left of the cell?
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:AndreasHermle
ID: 39644217
Hi rorya:

Thank you very much for your quick help:

Assuming running the following macro ID: 39643888

the ones that get rotated are not top aligned anymore but almost centered vertically.

The Horizontal Position is as follows: part of the image (to varying degrees) reach into Column C with most of picture being located in Column D.

See graphic.
Position_Graphic_Macro

Regards, Andreas
0
 
LVL 85

Assisted Solution

by:Rory Archibald
Rory Archibald earned 250 total points
ID: 39644264
Try this:
Sub InsertPictures()
'
' by Rgonzo, EE
'
   Dim c                           As Range
   Dim Image                       As Picture
   '
   For Each c In Range(Range("C2"), Range("C" & Rows.Count).End(xlUp))
      c.Offset(0, 1).Activate
      Set Image = ActiveSheet.Pictures.Insert(c.Value2)
      With Image
         If .Height > Application.CentimetersToPoints(4) Then _
                  .ShapeRange.ScaleHeight Application.CentimetersToPoints(4) / .Height, msoCTrue

         If .Height > .Width Then
            With .ShapeRange
               .Rotation = 90
               .IncrementLeft .Height / 2 - .Width / 2
               .IncrementTop .Width / 2 - .Height / 2
            End With

         End If
      End With
   Next
End Sub

Open in new window

0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 39644266
Can you upload a small sample for testing?
0
 
LVL 52

Expert Comment

by:Rgonzo1971
ID: 39644286
Hi,

pls try

Sub InsertPictures()
'
' by Rgonzo, EE
'
Dim c As Range
Dim Image As Picture
'
For Each c In Range(Range("C2"), Range("C" & Rows.Count).End(xlUp))
    c.Offset(0, 1).Activate
    Set Image = ActiveSheet.Pictures.Insert(c.Value2)
    
        If Image.Height > Image.Width Then
            If Image.Width > Application.CentimetersToPoints(4) Then
                Image.Width = Application.CentimetersToPoints(4)
            End If
            
            Image.ShapeRange.Rotation = 90
            Image.ShapeRange.IncrementTop -(Image.Height - Image.Width) / 2
            Image.ShapeRange.IncrementLeft (Image.Height - Image.Width) / 2
        Else
            If Image.Height > Application.CentimetersToPoints(4) Then
                Image.Height = Application.CentimetersToPoints(4)
            End If
        End If     
Next
End Sub 

Open in new window

Regards
0
 

Author Comment

by:AndreasHermle
ID: 39645139
I will not have time to do thorough testing right away.

I will do some testing tomorrow.

Thank you very much for your overwhelming and professional support.

Regards, Andreas
0
 

Author Comment

by:AndreasHermle
ID: 39645772
Dear both,

thank you very much  for your great and professional support. Both codes work just fine.

I am truly impressed with your coding abilities.

I hope you two find it fair if I split the points.  

300 go to Rory since he was a bit quicker to answer and
200 go to Rgonzo since he provided the base parts of this code.

Again, thank you very much for your great help.

Regards, Andreas
0
 

Author Comment

by:AndreasHermle
ID: 39650500
Dear Rgonzo and rorya:

I was gonna award points when I realized there is another requirement that needs to be considered:

Is it possible to adjust the rows so that they match the size of the graphics in Column D. As you know the size of the graphics can vary wildly from 1 to 4 cm.

I could also post this question as a new post.

Regards, Andreas
0
 
LVL 52

Accepted Solution

by:
Rgonzo1971 earned 250 total points
ID: 39650525
Hi,

pls try
Sub InsertPictures()
'
' by Rgonzo, EE
'
Dim c As Range
Dim Image As Picture
'
For Each c In Range(Range("C2"), Range("C" & Rows.Count).End(xlUp))
    c.Offset(0, 1).Activate
    Set Image = ActiveSheet.Pictures.Insert(c.Value2)
        If Image.Height > Image.Width Then
            If Image.Width > Application.CentimetersToPoints(4) Then
                Image.Width = Application.CentimetersToPoints(4)
            End If
            
            Image.ShapeRange.Rotation = 90
            Image.ShapeRange.IncrementTop -(Image.Height - Image.Width) / 2
            Image.ShapeRange.IncrementLeft (Image.Height - Image.Width) / 2
            Image.TopLeftCell.RowHeight = Image.Width
        Else
            If Image.Height > Application.CentimetersToPoints(4) Then
                Image.Height = Application.CentimetersToPoints(4)
                Image.TopLeftCell.RowHeight = Image.Height
            End If
        End If
    Next

End Sub

Open in new window

0
 

Author Comment

by:AndreasHermle
ID: 39650772
Hi Rgonzo,

I have tested it on around 50 Excel Records, works great so far. I will do some more testing and then let you know. Thank you very much for your superb help.

Regards, Andreas
0
 

Author Closing Comment

by:AndreasHermle
ID: 39650977
Thank you very much for your great and professional support.

I really appreciate it. I am so glad I can turn to this forum for real professional help.

Regards, Andreas
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

623 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question