You are here:

- Home
- Computing/Technology
- Business Software
- Excel
- Inserting images into excel file

Advertisement

QUESTION: Hello Tom,

A while back you provided a solution

http://en.allexperts.com/q/Excel-1059/2009/12/Excel-2007-VBA-insert.htm

we have a similar issue and can use the above solution, however we would like the image size to scale to 50% of the original size, and the row to automatically resize to accommodate the image, what modification would we need to the code.

thanks so much for your help

Amit

ANSWER: Amit,

row height is limited to 409 - so as long as 50% of the height is less than 409, the row will hold the picture. If it is creater than that, then it will make the height 409.

I tested it with 4 of the sample picture in Windows Vista (Height of 576) and it worked fine for me. It put the picture in column A using the picture name (without .jpg on the end) in column B starting in cell B2 (so that was how the original macro worked - I copied that and made the modifications you asked for).

I set the height to 50% of the original picture height and the picture should scale in proportion unless you have changed that setting.

Sub ProcessFiles()

Dim sPath As String, s As String, r As Range

Dim shp As ShapeRange

Dim c As Range, cell As Range, sname As String

Dim p As Picture, diffwidth As Double, diffHeight As Double

sPath = "C:\Users\Public\Pictures\Sample Pictures\"

If Right(sPath, 1) <> "\" Then sPath = sPath & "\"

Set r = Range("B2", Cells(Rows.Count, 2).End(xlUp))

For Each cell In r

cell.Offset(0, 1).Select

Set c = cell.Offset(0, -1)

s = sPath & cell.Value & ".jpg" 'remove the .jpg if the cell contains the extension

sname = Dir(s)

If sname <> "" Then

Set p = ActiveSheet.Pictures.Insert(s)

Set shp = p.ShapeRange

shp.ScaleHeight Factor:=0.5, RelativeToOriginalSize:=msoTrue

If shp.Height > 409 Then

cell.EntireRow.RowHeight = 409

Else

cell.EntireRow.RowHeight = shp.Height

End If

p.Left = c.Left

p.Top = c.Top

End If

Next

End Sub

--

Regards,

Tom Ogilvy

---------- FOLLOW-UP ----------

QUESTION: hi Tom

Thanks so much. The modified Macro worked nicely, all my images are 500x500 pixels.

I am novice at VBA and have few additional question:

1. The above macro assumes picture name in Column B, and pictures are to be inserted in Column A, where in the code would i modify if the picture name and pictures were to be in different columns

2. if i modify shp.ScaleHeight Factor:=0.5 to lets say shp.ScaleHeight Factor:=0.3 , then will the picture be 30% of the original picture height?

Greatly appreciate your help

Amit

ANSWER:

Amit,

this is where it uses column B to get the list of pictures

Set r = Range("B2", Cells(Rows.Count, 2).End(xlUp))

So you could do

Set r = Range("B2", Cells(Rows.Count, "B").End(xlUp))

I changed the 2 to a B so you can plainly see the reference to column B.

So you would change "B2" and "B" to reference another location such as

Set r = Range("F2", Cells(Rows.Count, "F").End(xlUp))

for column F. If you want to reference a specific range, then you could just do

set r = Range("M10:M15")

as an example.

No where the picture goes is set up to be to one column to the left of where the picture name is.

that is done with

Set c = cell.Offset(0, -1)

so the 0 means same row. the -1 means one column to the left. Cell refers to the cell with the picture name in it.

if you want to put the picture over the cell with the name then you would do

Set c = cell

if F was the column with the picture name and you wanted the pictures in column M then

set c = cell.offset(0, 7) because

G is one column to the right

H is two

I is three

J is four

K is five

L is six

M is seven (7)

So hopefully you can modify the macro to do what you want.

--

regards,

Tom Ogilvy

---------- FOLLOW-UP ----------

QUESTION: hi Tom

Thanks for explaining things so clearly, i was able to modify the columns in the macro.

I am assuming that the following code

p.Left = c.Left

p.Top = c.Top

inserts the picture left and Top aligned, so if i wanted the picture to align in the center, the code would be

p.Left = c.Center

p.Top = c.Center

thanks

Amit

Amit,

Well, since the row height is adjusted to the height of the picture, I wouldn't see that being in the center. Not sure what you thoughts are on that.

If you want to center it in the center of the column, you could do

p.Left = c.Left + c.Width/2

so you calculate the center of the cell by taking half the width of the cell and adding it to the left side of the cell.

there is no center property.

Now that puts the left edge starting in the center of the column.

If you wanted the center of the picture in the center of the column, that would be more complex

p.left = (c.Left + c.Width/2) - (p.Width/2)

so we take our center location calculated the same as above and subtract half the width of the picture to get where the left edge of the picture should be placed.

you would need to make sure the picture would still be totally on the sheet.

--

Regards,

Tom Ogilvy

- Add to this Answer
- Ask a Question

Rating(1-10) | Knowledgeability = 10 | Clarity of Response = 10 | Politeness = 10 |

Comment | Tom was wonderful to work with, he explained things clearly and solved the issue, we are grateful for your service. amit |

This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Excel

Answers by Expert:

Selected as an Excel MVP by Microsoft since 1999. Answering Excel questions in Allexperts since its inception in 2001. Able to answer questions on almost all aspects of Excel's internal capabilities. If seeking a VBA solution, please specify that in your question itself so I give you the answer you want. [Excel has weak protection - if you are distributing an application, I don't answer questions on how to protect your project from your users.]

Extensive experience. **Education/Credentials**

Master of Science (MS) degree Operations Research (ORSA)**Awards and Honors**

Microsoft MVP in Excel.