Help - Insert photos from directory

S

Stevep4

I have downloaded and installed the "morefunc" add ins which allows me to
access information from closed workbooks.

for example
=INDIRECT.EXT("'G:\shared\MyName\npms\data\["&B4&".xls]Sheet1'!$B$1")

Now what I need to do is download a photo into a cell.

Firstly
I would like to ensure that I can pull a photo (just to prove it at the
simplest level - before we make it complicated). So, in the local drive I
have a picture.
Filename "c:\lg.jpg"
What is the code I would need to get that to drop into a worksheet??

ultimately, I want to pull a photo from a specified drive on the network.
The file name would be provided from another cell in the worksheet (see code
above).

I need the macro code that would insert the photo into a specified cell and
scale it to fill the cell.

Can someone provide some code??
 
G

Gary''s Student

The first part is easy. Using the Macro Recorder:

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 1/20/2009 by James Ravenswood
'

'
Range("B9").Select
ActiveSheet.Pictures.Insert("C:\test\pic demo_files\image001.jpg").Select
Range("A1").Select
End Sub

However, the picture is not really "in" a cell. It just rests on top of the
cells. You can move it and re-size it with a macro, but another alternative
might be to put a button the the worksheet and put the picture on the button.
 
S

Stevep4

OK,
I modified the macro to match my sheet , which does work when I click "run"
macro.
I understand that in this macro that AH33 is the target location for the
photo, but what does B10 do? It just appear to be the cell that is
highlighted following the insertion of the photo.

Sub Macro1()
Range("ah33").Select

ActiveSheet.Pictures.Insert("G:\shared\penney\npms\data\stack\hp97.jpg").Select
Range("B10").Select

End Sub



So, next job is to revise the code so that rather than "hp97.jpg" I want
"(content of cell L22).jpg" This is because the other work being carried out
by the sheet details the filename I require.


Gary''s Student said:
The first part is easy. Using the Macro Recorder:

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 1/20/2009 by James Ravenswood
'

'
Range("B9").Select
ActiveSheet.Pictures.Insert("C:\test\pic demo_files\image001.jpg").Select
Range("A1").Select
End Sub

However, the picture is not really "in" a cell. It just rests on top of the
cells. You can move it and re-size it with a macro, but another alternative
might be to put a button the the worksheet and put the picture on the button.
--
Gary''s Student - gsnu200828


Stevep4 said:
I have downloaded and installed the "morefunc" add ins which allows me to
access information from closed workbooks.

for example
=INDIRECT.EXT("'G:\shared\MyName\npms\data\["&B4&".xls]Sheet1'!$B$1")

Now what I need to do is download a photo into a cell.

Firstly
I would like to ensure that I can pull a photo (just to prove it at the
simplest level - before we make it complicated). So, in the local drive I
have a picture.
Filename "c:\lg.jpg"
What is the code I would need to get that to drop into a worksheet??

ultimately, I want to pull a photo from a specified drive on the network.
The file name would be provided from another cell in the worksheet (see code
above).

I need the macro code that would insert the photo into a specified cell and
scale it to fill the cell.

Can someone provide some code??
 
G

Gary''s Student

We are definitely making progress!

Sub Macro1()
Dim s As String
s = "G:\shared\penney\npms\data\stack\" & Range("L22").Value & ".jpg"
Range("ah33").Select
ActiveSheet.Pictures.Insert(s).Select
Range("B10").Select
End Sub

Basically splicing three string together.
--
Gary''s Student - gsnu200828


Stevep4 said:
OK,
I modified the macro to match my sheet , which does work when I click "run"
macro.
I understand that in this macro that AH33 is the target location for the
photo, but what does B10 do? It just appear to be the cell that is
highlighted following the insertion of the photo.

Sub Macro1()
Range("ah33").Select

ActiveSheet.Pictures.Insert("G:\shared\penney\npms\data\stack\hp97.jpg").Select
Range("B10").Select

End Sub



So, next job is to revise the code so that rather than "hp97.jpg" I want
"(content of cell L22).jpg" This is because the other work being carried out
by the sheet details the filename I require.


Gary''s Student said:
The first part is easy. Using the Macro Recorder:

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 1/20/2009 by James Ravenswood
'

'
Range("B9").Select
ActiveSheet.Pictures.Insert("C:\test\pic demo_files\image001.jpg").Select
Range("A1").Select
End Sub

However, the picture is not really "in" a cell. It just rests on top of the
cells. You can move it and re-size it with a macro, but another alternative
might be to put a button the the worksheet and put the picture on the button.
--
Gary''s Student - gsnu200828


Stevep4 said:
I have downloaded and installed the "morefunc" add ins which allows me to
access information from closed workbooks.

for example
=INDIRECT.EXT("'G:\shared\MyName\npms\data\["&B4&".xls]Sheet1'!$B$1")

Now what I need to do is download a photo into a cell.

Firstly
I would like to ensure that I can pull a photo (just to prove it at the
simplest level - before we make it complicated). So, in the local drive I
have a picture.
Filename "c:\lg.jpg"
What is the code I would need to get that to drop into a worksheet??

ultimately, I want to pull a photo from a specified drive on the network.
The file name would be provided from another cell in the worksheet (see code
above).

I need the macro code that would insert the photo into a specified cell and
scale it to fill the cell.

Can someone provide some code??
 
S

Stevep4

Looking good. That step appears to work OK.

Next steps

1:/ Ensure macro runs any time the contents of L22 change. I have added a
Run Macro command of CTRL+M but would prefer if this was automatic when I
change the content of the single unlocked cell and press ENTER

2:/ Delete any existing picture in cell AH33 (otherwise subsequent pictures
lay on top of previous)

3:/ Reduce scale of picture to the limits of the cell

Gary''s Student said:
We are definitely making progress!

Sub Macro1()
Dim s As String
s = "G:\shared\penney\npms\data\stack\" & Range("L22").Value & ".jpg"
Range("ah33").Select
ActiveSheet.Pictures.Insert(s).Select
Range("B10").Select
End Sub

Basically splicing three string together.
--
Gary''s Student - gsnu200828


Stevep4 said:
OK,
I modified the macro to match my sheet , which does work when I click "run"
macro.
I understand that in this macro that AH33 is the target location for the
photo, but what does B10 do? It just appear to be the cell that is
highlighted following the insertion of the photo.

Sub Macro1()
Range("ah33").Select

ActiveSheet.Pictures.Insert("G:\shared\penney\npms\data\stack\hp97.jpg").Select
Range("B10").Select

End Sub



So, next job is to revise the code so that rather than "hp97.jpg" I want
"(content of cell L22).jpg" This is because the other work being carried out
by the sheet details the filename I require.


Gary''s Student said:
The first part is easy. Using the Macro Recorder:

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 1/20/2009 by James Ravenswood
'

'
Range("B9").Select
ActiveSheet.Pictures.Insert("C:\test\pic demo_files\image001.jpg").Select
Range("A1").Select
End Sub

However, the picture is not really "in" a cell. It just rests on top of the
cells. You can move it and re-size it with a macro, but another alternative
might be to put a button the the worksheet and put the picture on the button.
--
Gary''s Student - gsnu200828


:


I have downloaded and installed the "morefunc" add ins which allows me to
access information from closed workbooks.

for example
=INDIRECT.EXT("'G:\shared\MyName\npms\data\["&B4&".xls]Sheet1'!$B$1")

Now what I need to do is download a photo into a cell.

Firstly
I would like to ensure that I can pull a photo (just to prove it at the
simplest level - before we make it complicated). So, in the local drive I
have a picture.
Filename "c:\lg.jpg"
What is the code I would need to get that to drop into a worksheet??

ultimately, I want to pull a photo from a specified drive on the network.
The file name would be provided from another cell in the worksheet (see code
above).

I need the macro code that would insert the photo into a specified cell and
scale it to fill the cell.

Can someone provide some code??
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top