using VBA to insert a column of pictures

A

andy hackett

I have a column of file names and want to write a macro to insert th
pictures the files refer to in the cell to the right my code so far i

Filename = ActiveCell.Offset(0, -1)
ActiveSheet.Pictures.insert( _
"C:\Documents and Settings\12ahackett\Desktop\aspen smal
pics\re sized\(filename)"
).Select
the active cell being the insertion point, the variable "filename
being the Jpeg filename.
Any help on the correct syntax for the insert statement would b
appreciate
 
T

Tom Ogilvy

Dim Filename as String
Filename = ActiveCell.Offset(0, -1).Value
ActiveSheet.Pictures.insert _
"C:\Documents and Settings\12ahackett\Desktop" & _
"\aspen small pics\re sized\" & Filename & ".jpg"

if the cell includes the jpg extension, then you can delete that from the
string above.
 
C

Charles

Andy,

Here is a code that you can work with. It's for a BMP. But it may giv
you some ideal.

Charles


Sub Get_picture()
Dim fname
fname = ActiveCell.Offset(0, -1)
ActiveSheet.Pictures.Insert ( _
"C:\Documents and Settings\Charles Harmon\My Documents\M
Pictures\") & fname<<fname wksh =101.bmp
End Su
 
A

andy hackett

Thanks Charles. That works great. Now I have to figure a way of workin
down the column. (as you will no doubt of guessed I'm new to VBA bu
enjoy learning !
 
C

Charles

andy,






This assumes all bmp is in column "A"Row 1 through ?? It loops throug
the colum for the bmp.


Sub Get_picture()
Dim fname
Dim rng As Range
Dim i As Integer
Set rng = Worksheets("sheet1").Cells(1, 1).CurrentRegion
For i = 1 To rng.Rows.Count
fname = rng(i, 1).Text
ActiveSheet.Pictures.Insert ( _
"C:\Documents and Settings\Charles Harmon\My Documents\M
Pictures\") & fname
Next
End Sub


Have fun.

Charle
 
A

andy hackett

Thanks again Charles,

Unfortunately I can't get this one to work. I have put all the fil
names in column A and changed the directory ref. to the correct one bu
the macro stops on one the activesheet.picture.insert lin
 
T

Tom Ogilvy

As written, this inserts all pictures in the activeCell, not next to the
cell with the name.
 
A

andy hackett

Charles said:
*Andy,

Post your code and I'll look at it


Charles *
Click to expand...

Code copied in below.
I have all the picture ref files in column A and want the macro to ste
through the column and insert the picture in each adjacent cell

Sub Get_picture()
Dim fname
Dim rng As Range
Dim i As Integer
Set rng = Worksheets("sheet1").Cells(1, 1).CurrentRegion
For i = 1 To rng.Rows.Count
fname = rng(i, 1).Text
ActiveSheet.Pictures.insert ( _
"C:\Documents and Settings\12ahackett\Desktop\aspen small pics\r
sized\") & fname
Next
End Su
 
T

Tom Ogilvy

I would assume he would the following addition:

Sub Get_picture()
Dim fname
Dim rng As Range
Dim i As Integer
Set rng = Worksheets("sheet1").Cells(1, 1).CurrentRegion
For i = 1 To rng.Rows.Count
fname = rng(i, 1).Text
rng(i,2).Select '<==
ActiveSheet.Pictures.Insert _
"C:\Documents and Settings\" _
& "Charles Harmon\My Documents\My Pictures\" _
& fname
Next
End Sub

But if he can't get past the Pictures.Insert line, it is unclear wha
his problem might be, especially if he was successful with the initia
suggestion for a single cell.

Just a note:
() are not required around the argument to Insert since it isn'
returning a value.
 
T

Tom Ogilvy

Check the path below to make sure it has spaces where they should be an
does not have spaces where they should not be.

Sub Get_picture()
Dim fname
Dim rng As Range
Dim i As Integer
Set rng = Worksheets("sheet1").Cells(1, 1).CurrentRegion
For i = 1 To rng.Rows.Count
fname = rng(i, 1).Text
rng(i,2).Select
ActiveSheet.Pictures.insert _
"C:\Documents and Settings\12ahackett\" & _
"Desktop\aspen small pics\re sized\" & fname
Next
End Sub
 
A

andy hackett

Charles, Tom,
Thank you very much for your help and the time you have put in.
The fault was mine in that I had column headers which meant the firs
cell was not a picture filename.
As a "newbie" I'm now working through your code to figure out how i
works!
It's great to have peole like yourselves who are prepared to take th
time to help others out

Thanks,

And
 
Top