How to use the content of a cell as a file name

[cyrus]

I Like to have a macro that looks at the content of a specific cell in the
sheet to get the file name neede to insert the picture in a specific cell.
This would be same function for all the sheets except sheet(cover).
Here is the recorded macro:
ActiveSheet.Unprotect
Range("E2:H18").Select
ActiveSheet.Pictures.Insert( _ """" this is where I want to unsert the
cell that has file name"""'
"C:\Documents and Settings\Vince Aragona\My Documents\My
Pictures\101MSDCF\101MSDCF\DSC00002.JPG" _
).Select
Selection.ShapeRange.LockAspectRatio = msoTrue
Selection.ShapeRange.Height = 216#
Selection.ShapeRange.Width = 288#
Selection.ShapeRange.Rotation = 0#

Any help would be greatly appreciated.

 

[Tom Ogilvy]

http://www.mcgimpsey.com/excel/lookuppics.html

If that isn't exactly what you want, it should give you all the information
you need.

if you want your simplified code:

ActiveSheet.Unprotect
Range("E2:H18").Select
sName = Worksheets("Sheet1").Range("A1").Text
ActiveSheet.Pictures.Insert(s).Select

Selection.ShapeRange.LockAspectRatio = msoTrue
Selection.ShapeRange.Height = 216#
Selection.ShapeRange.Width = 288#
Selection.ShapeRange.Rotation = 0#

 

[cyrus]

Tom,
I'm so close to getting this right but not there yet. I tried your code and
I get this message" Unable to get the property of the pictures class"
My thinking is that I have the wrong file name in the cell. but checked and
double checked. I even ran the recorded macro to see the exact file and it
maches with my cell. Any ideas?

ActiveSheet.Unprotect
Range("E2:H18").Select
sName = Worksheets("3").Range("G43").Text
ActiveSheet.Pictures.Insert(s).Select """ this is highlighted""""
Selection.ShapeRange.LockAspectRatio = msoTrue
Selection.ShapeRange.Height = 216#
Selection.ShapeRange.Width = 288#
Selection.ShapeRange.Rotation = 0#

 

[Dave Peterson]

A typo.

Try:
ActiveSheet.Pictures.Insert(sName).Select

 

[cyrus]

It's funny how how a four letter word can change everything! You just saved
me a bunch time. Thank you so much


Regards,

 

[cyrus]

Dave, Tom

Sorry to bug you. I left out one minor detail. In need to do this for every
sheet except sheet named(Cover). All the formula cells and cells for Pic
insertions are the same range for every sheet.

Thanks a million

 

[Dave Peterson]

Dim wks as worksheet

for each wks in activeworkbook.worksheets
with wks
select case lcase(.name)
case is = "cover"
'do nothing
case else
.select
.Range("E2:H18").Select
sName = .Range("A1").Text
.Pictures.Insert(sName).Select
Selection.ShapeRange.LockAspectRatio = msoTrue
Selection.ShapeRange.Height = 216#
Selection.ShapeRange.Width = 288#
Selection.ShapeRange.Rotation = 0#
end select
end with
next wks

Maybe???????

 

[cyrus]

Tom,

That worked Great.
Thanks again,