Solved

VBA - Sizing Various Images

Posted on 2012-04-05
4
279 Views
Last Modified: 2012-04-05
Using VB.Net 2010 (Pro) to create an Excel 2010 spreadsheet.

I'm in the process of creating a report.  At first, there were two images on the report.  Our company logo and the customer's logo.  These were "hard coded" as to the size.  Now, I'm charged with re-writing the code to allow us to offer the report app to other customers, which means I need to allow the customer to choose their own image file to be put in the report.

The problem is, I only have a certain area for that logo image to go and the customer's image can (literally) be any size.  What I need is to adjust the image size so that it does not overwrite (or be overwritten by) the text that will be going in the cells near it.  Further complicating the situation is the fact that Excel deals with inches and points, and image files are in pixels (with no easy conversion).

Here is the code I used originally to place the image in the spreadsheet...
m_oSheet.Shapes.AddPicture(sLogoFilePathName, _
                           TriState.False, _
                           TriState.True, _
                           m_oXL.InchesToPoints(1.1), _
                           m_oXL.InchesToPoints(0.11), _
                           m_oXL.InchesToPoints(1.3), _
                           m_oXL.InchesToPoints(0.49))

Open in new window

The original image size is 1.3" wide by .49" high (measured by me on a printout).  Right or wrong, this has been working correctly so, ultimately, I need to keep the 1.3"w by .49" h ratio.

I also know that the original image is 123px wide and 47px high.

So here's my question...

Once I have an image file (whatever the user selected), how do I enlarge or shrink the image so that it will fit the hole where the image goes?  I'm guessing that I can alter the height to match the original (maintaining aspect ratio), then compare the width.  If the width is wider than the original, then alter the width to match the original.  But I'm not sure exactly how to code it, nor do I know how to figure out the image height/width in inches to put in the AddPicture() procedure.

TIA
0
Comment
Question by:Clif
  • 3
4 Comments
 
LVL 10

Author Comment

by:Clif
ID: 37811814
Excuse me for thinking out loud (as it were), but I've been researching and see that (in my case) the actual ratio between sizes (points vs pixels) is 72:96 (the original image depth noted above is 96dpi).

So, if I know the depth, then I sould be able to figure out everything else.  

However, it does not seem that depth is a property of the image object.
0
 
LVL 83

Accepted Solution

by:
CodeCruiser earned 500 total points
ID: 37811834
Have you tried checking HorizontalResolution and VerticalResolution?

http://msdn.microsoft.com/en-us/library/system.drawing.image.horizontalresolution.aspx
0
 
LVL 10

Author Comment

by:Clif
ID: 37812381
I got that, but something's not working right.  My new image is ending up the correct height, but far wider than it should be.

Here is my code:
Dim fLogoHeight As Single = 0
Dim fLogoWidth As Single = 0
Dim fLogoHDPI As Single = 0
Dim fLogoWDPI As Single = 0
Dim fLogoSizeRatio As Single = 0
Dim imgLogo As Image = Image.FromFile(sLogoFilePathName)
fLogoHeight = imgLogo.Height
fLogoWidth = imgLogo.Width
fLogoHDPI = imgLogo.HorizontalResolution
fLogoWDPI = imgLogo.VerticalResolution

fLogoSizeRatio = 47 / fLogoHeight

m_oSheet.Shapes.AddPicture(sLogoFilePathName, _
                           TriState.False, _
                           TriState.True, _
                           m_oXL.InchesToPoints(1.1), _
                           0, _
                           (fLogoWidth * fLogoSizeRatio) * (72 / fLogoWDPI), _
                           m_oXL.InchesToPoints(0.71))

Open in new window

I did make a change to the top and height parameters to position the new logo more along the lines of our company logo.  If I put the top and height parameters back, the logo looks fine.
0
 
LVL 10

Author Closing Comment

by:Clif
ID: 37813333
It seems to be working, albeit in it's original (smaller) form.  But that'll do.
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
New Criteria Prototype 3 16
.NET 2008 VB and C# 6 27
VBA: copy range dynamically based on config sheet v2 3 30
Excel format formula for currency 15 22
Microsoft Office Picture Manager was included in Office 2003, 2007, and 2010, but not in Office 2013. Users had hopes that it would be in Office 2016/Office 365, but it is not. Fortunately, the same zero-cost technique that works to install it with …
This article will show you how to use shortcut menus in the Access run-time environment.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
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…

816 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

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now