I have numerous occasions when I have needed to list photographs in a
document and use that document for data entry and very limited
analysis (sorting, aggregate functions, etc...)
An example might be a list of 100 products with small 100x100 pixel
images that need to be listed along witht heir product code, price,
etc... to send to a client.

Ideally I would like to do this in Microsoft Excel or Microsoft
Access.
In Excel I can choose 'Move and size with cells' in the properties of
each image I insert but this option doesnt allow me to auto size the
image to the size of the cell or the cell to the size of the image and
is REALLY time consuming!
In Access, I can get the desired result if I store the image in a
database as an ole object, but if I want to use existing jpeg files on
my hard disk I cant make a linked image display *different* pictures
in each row of a datasheet
or continuous form view.

It seems a pretty trivial and common requirement, but I cant seem to
find a way to do it in the ease that powerful programs like access or
excel normally provide. Perhaps I am missing something simple?

---

Request for Question Clarification by walts-ga on 02 Jul 2002 06:41 PDT

Have you had any luck with the method mentioned in my 21 Jun 2002
12:25 PDT comment?
 
Regards, walts-ga

Dear lexz-ga,

This sounds like a really useful technique to master.  I think your
best bet is to use the Acess database since it will give you the
maximum control over extracting data, sorting it, etc.

Here's how to create a database that I believe will meet your needs.
There are probably any number of ways to create this but this will
give you the idea and I suspect you'll be able to run from there.

First start Access and create a new blank database.
Create a table in design view.
The first data element I created was called ID and was an autonumber
item. I then right-clicked on the line to make it the primary key.
(You may want to use your product ID, part number or some other data
item for your primary key but I'm just creating a small test case for
you to see as an example.)

The second data element I called "pic" and made an OLE object

The third data element I called "text" and made a text object.
Obviously you could create any number of other data fields of whatever
type is desired.

I then saved this table. I just called it the default "Table1" but
you'll want to give it some more meaningful name.

The next step was to insert some data into the table. For this I
opened the table.

The ID field is, of course, filled in automatically.

I right-clicked in the pic field and selected "insert Object". There
is a box full of tempting objects but instead I clicked on the radio
button to "Create from File".  I was then able to fill in the file
name of the picture I wanted in that data element.  A browse button is
available for convenience.

I then typed some text in to the third data field.

I repeated this process to give myself 2 or 3 rows of test data.  

I made sure my table was saved then went on to the next step.

The third step was to create a report to get the data back out.  For
this simple example, I just used the wizard and mostly took default
choices.

I went to the "Reports" tab and double-clicked on "Design report by
using wizard"

I added the pic and text data elements to my report then clicked next.

I didn't specify any grouping so I just clicked next and continued
doing so thru the following screens until it came time to name the
report and save it.  I just accepted the default name "Table1".  I
changed the radio button to "Modify the report's design" then clicked
"Finish".

On the report form, I right clicked on the large picture box (I could
have changed it's shape or position as needed). I selected
"Properties". On the Format tab I changed "Size Mode" to "zoom" then
saved the report.  I then clicked on the "view" button to see my
report.

Voila - the pictures are automatically sized and the text displays
beside them as formatted on the report form.

Hope this answers your question satisfactorily.  Thanks for asking it!

funzone-ga

This macro does the resizing.

At the moment all this does is open a file dialog, you then select a
file (GIF or JPG -- you can add more if you like) and it inserts the
image into the selected (active) cell. It will resize the image down
to the width of the existing cell, and resize the cell height to the
height of the image. I did the resizing in this way to preserve the
proportions of the image. If you want something which take a list of
images (in a text file, line-by-line for example), it would be
relatively easy. If you can't figure it out, just let me know.

Cheers,

Tom.

Sub InsertPic()
    Dim fNameAndPath As Variant
    Application.ScreenUpdating = False
    fNameAndPath = Application.GetOpenFilename _
        (FileFilter:="JPEG File (*.jpg),*.jpg,GIF File (*.gif),*.gif",
_
        Title:="Select file to be imported")
    If fNameAndPath = False Then Exit Sub
    ActiveSheet.Pictures.Insert( _
        fNameAndPath). _
        Select
    Selection.ShapeRange.Left = ActiveCell.Left
    Selection.ShapeRange.Top = ActiveCell.Top
    Selection.ShapeRange.Width = ActiveCell.Width
    Rows(ActiveCell.Row & ":" & ActiveCell.Row).RowHeight =
Selection.ShapeRange.Height
End Sub

lexz-ga - 

You asked how to list photographs in a document and use that document
for data entry and very limited analysis (sorting, aggregate
functions, etc...), with an example of 100 products with small 100x100
pixel
images that need to be listed along with heir product code, price,
etc. You also mentioned that while it seems a trivial and common
requirement, you cannot seem to find a way to do it in the ease that
powerful programs like access or excel normally provide, and perhaps
you were missing something simple?

Well, me too! Having had the need to do this, myself, I came up with
this Excel solution. It works - but requires some advanced preparation
and one rule must be followed.

Unfortunately, I don't know if this is the answer for which you are
looking. As such, I won't post this as an answer - just a comment. If
it is an answer, leave word here and I'll then mark it as answered.

The advanced preparation:
 - The rows and columns must be set to a height and width that allows
a cell to be "just a little bigger" than the largest inserted graphic.
 - If you are going to sort the data, you most ensure you have done
the advanced preparation or else the graphics won't sort properly.

Say you are using 100 pixel square graphics. I would format all the
rows to a height of 102, which can be done in one operation by first
selecting all the rows. I would format the column to 19 characters.
Note that both settings might "change" due to your selected font and
font size.

Once set, you can insert a graphic in one column, insert data in
others, and sort, sum, etc., and print as you like.

Good luck!

 --- Regards, walts-ag