You are here:

Excel/inserting images in excel

Advertisement


Question
For some reason there was no place to click to follow up on our las communicatio!

I have pastesd it below:

I asked the question below last night:

I can set up the cells so that the 140x80 images fit into them. All that would be necessary is to reduce the 640x480 images to the same size. Does that make the project below doable?

Ben

I am using excel 2010.

1. I have 430 .jpg images in folder C:1. LP software05-13-1130-1630-SA-RAS-mpeg4-
2. The first two images are differnt in size. The first image is 640X480. The second image is a zoom of a portion of the first image - its size is 140X80.
3. The subsequent images are similar pairs as described in 2.
4. I would like to be able to insert the first image in A1 and the second image in B1. I would like the first image (640X408)to be reduced in size so that it will fit in A1 which will be the size to fit second image (140X80).
5. I would like to have the subsequent pairs be inserted in order into A2-B2,A3-B3, etc.

Thanks for your help!

Ben

ANSWER: Ben

you can certainly set the height and width properties of a picture and you can position it over a cell.  (pictures exist on the object layer - they don't actually go into cells - but sit above the cells).

I certainly read a directory and get a list of file names.  

If they are images, I can insert them into the file and arrange them in successive rows.

but I have no way of knowing how to pair up the images - nor would there necessarily be any order to the images so if I could pair them, if which pair is in row 1, there is no information on that.

"1. LP software05-13-1130-1630-SA-RAS-mpeg4-"
is a folder name?

so if you can answer some of those questions, I can try to provide you some basic code.

--
Regards,
Tom Ogilvy


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

QUESTION: 1. "but I have no way of knowing how to pair up the images - nor would there necessarily be any order to the images so if I could pair them, if which pair is in row 1, there is no information on that."

The first and second images in the file are a pair and should go into row 1 (A1,B1).
The third and fourth images in the file are a pair and should go into row 2 (A2,B2).
This sequence continues through out the entire file.

2. "1. LP software05-13-1130-1630-SA-RAS-mpeg4-"
is a folder name?

Yes

Those are the only two questions that I see.

Thanks!

Ben

ANSWER: Ben,

If all the images are in one file, then I can't help you.  I know nothing about multiple images in a single file.  My knowledge starts when the file is inserted into excel. I can manipulate the single picture object, but what is contained internally in that picture object is invisible to me.     

Sorry.

--
Regards,
Tom Ogilvy


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

QUESTION: I probably used the wrong word when I wrote:

"The first and second images in the file are a pair and should go into row 1 (A1,B1).
The third and fourth images in the file are a pair and should go into row 2 (A2,B2).
This sequence continues through out the entire file."

I should have used the word folder intead of file and said:

The first and second images in the folder are a pair and should go into row 1 (A1,B1).
The third and fourth images in the folder are a pair and should go into row 2 (A2,B2).
This sequence continues through out the entire folder.

Can you work with images in folders?

Ben
Answer:  Ben,

Try this

Open a new sheet in the active workbook
then run this code (adjust the path to point to  your folder if necessary.  The path should end with a back slash as shown)

Sub checkfiles()
Dim sPath As String, sName As String
Dim sFirst As Boolean, rw As Long
sFirst = True
rw = 1
sPath = "C:\1. LP software05-13-1130-1630-SA-RAS-mpeg4-\"

sName = Dir(sPath & "*.jpg")
Do While sName <> ""
If sFirst Then
   Cells(rw, 1) = sName
   sFirst = False
Else
   Cells(rw, 2) = sName
   sFirst = True
   rw = rw + 1
End If
sName = Dir()
Loop
End Sub


So that is as much as I know about the files in your folder.  Saying things like first file and second file are pretty much meaningless.  How they are returned could vary.  One would expect something that can be used to order the files

picture1A.jpg
picture1B.jpg
picture2A.jpg
picture2B.jpg

if the code orders you files as you expect and the first file is the one that needs to be scaled smaller, then the code just needs to add those commands.  But if they don't, then back to square 1.

--
Regards,
Tom Ogilvy


The code put the proper name of the images in the proper row and column. If you could now put the images in the same cells that would solve the problem. The image in column B is the 640x480 image and needs to be reduced to fit 140X80. If possible, it would be nice if you could place the image name at the bottom of the cell and the image at the top. I can adjust the cell height to accomodate that addition.

Thanks again!

Ben

Answer
Ben,

See if this works.  

Sub checkfiles()
Dim sPath As String, sName As String
Dim sFirst As Boolean, rw As Long
Dim pic As Picture
Dim r As Range
sFirst = True
rw = 1
'sPath = "C:\1. LP software05-13-1130-1630-SA-RAS-mpeg4-\"
sPath = "C:\Data\"
sName = Dir(sPath & "*.jpg")
Do While sName <> ""
 If sFirst Then
    Set r = Cells(rw, 1)
    r.Value = sName
    r.VerticalAlignment = xlBottom  ' text at the bottom
    r.HorizontalAlignment = xlCenter
    Set pic = ActiveSheet.Pictures.Insert(sPath & sName)
    pic.ShapeRange.LockAspectRatio = False
    pic.Top = r.Top   ' Picture at the top
    pic.Left = r.Left
    'Column A, image does not need to be reduced
'    pic.Height = 140
'    pic.Width = 80
    sFirst = False
 Else
    
    Set r = Cells(rw, 2)
    r.Value = sName
    r.VerticalAlignment = xlBottom  ' text at the bottom
    r.HorizontalAlignment = xlCenter
    Set pic = ActiveSheet.Pictures.Insert(sPath & sName)
    pic.ShapeRange.LockAspectRatio = False
    pic.Top = r.Top    ' Picture at the top
    pic.Left = r.Left
    ' column B, image needs to be reduced
    pic.Height = 140
    pic.Width = 80

    Cells(rw, 2) = sName
    sFirst = True
    rw = rw + 1
 End If
 sName = Dir()
Loop
End Sub

--
Regards,
Tom Ogilvy

About Excel
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

All Answers


Answers by Expert:


Ask Experts

Volunteer


Tom Ogilvy

Expertise

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.]

Experience

Extensive experience.

Education/Credentials
Master of Science (MS) degree Operations Research (ORSA)

Awards and Honors
Microsoft MVP in Excel.

©2016 About.com. All rights reserved.