displaying pictures

[Guest]

I am sorry to post the query once again. I main school data base in excel.
The database has around 1000 student entries. It contians student id,
student particulars, fee structure and etc. What i want is i would like to
incorporate the photos of the students and when i select the student id, i
would like to get the std-data with his/her photo. I tried McGimpsey
"lookuppictures". It is allowing me only about 75 pictures. How can i store
the photos of all the 1000 students? Is there any way in Excel?
with heart felt thanks to all from Sridhar
Sridhar

 

[JE McGimpsey]

FOr 1000 pictures, I'd think you'd be better off using a database
appication, like MySQL, or even Access. Even thumbnails are going to
make your workbook huge.

Alternatively, storing the picture filenames in the records, and using a
macro to load the referenced picture, then delete it when the record is
dismissed.

 

[Guest]

Thank you Mr. McGimpsey. The second option you mentioned storing the
filenames, how could this be achieved?
My sincere thanks to you Mr. McGimpsey
Sridhar

 

[Guest]

Download the demo at this link:
http://www.contextures.com/excelfiles.html#CH0003

The ShowFilePicsDemo shows how to click on a drop-down list and pull the
associated picture from network or web folders.

Is that something you can work with?
***********
Regards,
Ron

XL2003, WinXP

 

[Guest]

Let me first extend my sincere thanks to Mr. McGimpsey, for floating the idea
and to Mr. Ron for the required macro. Mr. Ron it is the same what i want.
It worked well. I changed the code, though i don't know nothing about VB
code, to fit to my requirements. The following is the change, i request your
permission for it.

Set oNewPic = shtWS.Shapes.AddPicture( _
Filename:=strFileLoc, _
LinkToFile:=msoFalse, _
SaveWithDocument:=msoTrue, _
Left:=.Left + 1, Top:=.Top, Width:=.Width, Height:=.Height)

'Maintain original aspect ratio and set to full size
oNewPic.LockAspectRatio = msoFalse
oNewPic.ScaleHeight Factor:=1, RelativeToOriginalSize:=msoFalse
oNewPic.ScaleWidth Factor:=1, RelativeToOriginalSize:=msoFalse

If blnFitInDestHeight = True Then
'Resize the picture to fit in the destination cells
oNewPic.Height = 78
oNewPic.Width = 103
End If
any suggestions for the change.
The code is more advantegious than the "lookuppictures" (i hope i am not
hurting Mr. McGimpsey) as we can store the picture-path with the database
without bulging the size of the file.

Mr. Ron one more question : i am inserting the path using insert|hyperlink
and removing it. Is it the correct way or can you suggest any simple way.

Heart felt Thanks to Mr. McGimpsey and Mr. Ron
with regards
Sridhar

 

[Guest]

Thank you for the kind words, yshridhar

Your changes to the VBA code:
I offered the code only as a demonstration of the technique. Please feel
free to amend to to suit your situation as you see fit.

If all of the pictures have an aspect ration of 78 x 103, then I believe
you could have the same effect with less code:

With rDestCells
'Create the new picture
'*** SET THE SIZE TO 103 X 78 UPON INSERTION***
Set oNewPic = shtWS.Shapes.AddPicture( _
Filename:=strFileLoc, _
LinkToFile:=msoFalse, _
SaveWithDocument:=msoTrue, _
Left:=.Left + 1, Top:=.Top + 1, Width:=103, Height:=78)

'Assign the desired name to the picture
oNewPic.Name = strPicName
End With 'rCellDest

Establishing the file paths:
If pasting links then converting them to text works for you, then I may have
an idea to save you some time:

You can turn of the automatic creation of hyperlinks. That way, when you
past the link into the sheet, it will be text instead of converting to a
hyperlink.

Here's how...
From the Excel Main Menu:
<tools><options><spelling tab>
Click [autocorrect options]
Select the "Autoformat as you type" tab
UNcheck: Internet and network paths with hyperlinks
Click [OK]

When you're done pasting the paths to the picture files, reset that option.

Is that something you can work with?
***********
Regards,
Ron

XL2003, WinXP

 

[Guest]

Lot many thanks to you Mr. Ron. I am really very happy for your code and
kind suggestions.
With regards
Sridhar

Thank you for the kind words, yshridhar

Your changes to the VBA code:
I offered the code only as a demonstration of the technique. Please feel
free to amend to to suit your situation as you see fit.

If all of the pictures have an aspect ration of 78 x 103, then I believe
you could have the same effect with less code:

With rDestCells
'Create the new picture
'*** SET THE SIZE TO 103 X 78 UPON INSERTION***
Set oNewPic = shtWS.Shapes.AddPicture( _
Filename:=strFileLoc, _
LinkToFile:=msoFalse, _
SaveWithDocument:=msoTrue, _
Left:=.Left + 1, Top:=.Top + 1, Width:=103, Height:=78)

'Assign the desired name to the picture
oNewPic.Name = strPicName
End With 'rCellDest

Establishing the file paths:
If pasting links then converting them to text works for you, then I may have
an idea to save you some time:

You can turn of the automatic creation of hyperlinks. That way, when you
past the link into the sheet, it will be text instead of converting to a
hyperlink.

Here's how...
From the Excel Main Menu:
<tools><options><spelling tab>
Click [autocorrect options]
Select the "Autoformat as you type" tab
UNcheck: Internet and network paths with hyperlinks
Click [OK]

When you're done pasting the paths to the picture files, reset that option.

Is that something you can work with?
***********
Regards,
Ron

XL2003, WinXP

Let me first extend my sincere thanks to Mr. McGimpsey, for floating the idea
and to Mr. Ron for the required macro. Mr. Ron it is the same what i want.
It worked well. I changed the code, though i don't know nothing about VB
code, to fit to my requirements. The following is the change, i request your
permission for it.

Set oNewPic = shtWS.Shapes.AddPicture( _
Filename:=strFileLoc, _
LinkToFile:=msoFalse, _
SaveWithDocument:=msoTrue, _
Left:=.Left + 1, Top:=.Top, Width:=.Width, Height:=.Height)

'Maintain original aspect ratio and set to full size
oNewPic.LockAspectRatio = msoFalse
oNewPic.ScaleHeight Factor:=1, RelativeToOriginalSize:=msoFalse
oNewPic.ScaleWidth Factor:=1, RelativeToOriginalSize:=msoFalse

If blnFitInDestHeight = True Then
'Resize the picture to fit in the destination cells
oNewPic.Height = 78
oNewPic.Width = 103
End If
any suggestions for the change.
The code is more advantegious than the "lookuppictures" (i hope i am not
hurting Mr. McGimpsey) as we can store the picture-path with the database
without bulging the size of the file.

Mr. Ron one more question : i am inserting the path using insert|hyperlink
and removing it. Is it the correct way or can you suggest any simple way.

Heart felt Thanks to Mr. McGimpsey and Mr. Ron
with regards
Sridhar