Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Inserting Pictures Based On File name
I have this code from another thread posted by Joel, and I want
to find out if it will work for me On my sheet I have a file name in column A and this is the name of the file I want to insert automatically into that cell or on top of that cell. The number of rows changes from sheet to sheet, and to allow room for the picture, the rows may be, the first three, then the next three then the next three So my sheet may look like this; ball.bmp | 1 | "need image inserted into cell A1" |.750 | | 3.0 | bull.bmp | 1 | "need image inserted into cell A4" |.500 | | 3.5 | squ .bmp | 1 | "need image inserted into cell A7" |.375 | | 3.2 | etc, etc as long as the sheet might be... I think, I would be checking every fourth row for a file name Would this code work or would I be better of using something else? Thanks Jeff W. .................................................. .................................................. ................... The code deletes all old pictures and then adds all the pictures. It is not easy to only delete the pictures that have changed. Sub add_pictures() Const PictureHeight = 25 'delete pictures For Each shp In ActiveSheet.Shapes If shp.Type = msoPicture Then shp.Delete End If Next shp LastRow = Cells(Rows.Count, "A").End(xlUp).Row Rows(2).RowHeight = PictureHeight For Each cell In Range("B1:K1") If cell < "" Then Set pict = ActiveSheet.Pictures. _ Insert(cell.Value) pict.ShapeRange.LockAspectRatio = msoTrue pict.ShapeRange.Height = PictureHeight pict.Top = cell.Offset(1, 0).Top pict.Left = cell.Offset(1, 0).Left End If Next cell End Sub .................................................. .................................................. ................... |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Inserting Pictures Based On File name
Not sure if will assist or hinder, but i use the following code to input a picture into a sheet
based on Cell(A1): ' Photo 1 Application.ScreenUpdating = False Dim WB As Workbook Dim SH As Worksheet Dim rng As Range Dim mypic As Picture Dim res As Variant Set WB = ActiveWorkbook res = sheet1.Range("A1").value If res = False Then Exit Sub Set SH = ActiveSheet Set rng = ActiveCell Set mypic = SH.Pictures.Insert(res) With mypic .Top = rng.Top .Left = rng.Left .Locked = False mypic.ShapeRange.LockAspectRatio = msoFalse mypic.ShapeRange.Height = 213.1 mypic.ShapeRange.Width = 249.2 mypic.ShapeRange.Rotation = 0# End With End If Corey.... "Jeff W." wrote in message ... I have this code from another thread posted by Joel, and I want to find out if it will work for me On my sheet I have a file name in column A and this is the name of the file I want to insert automatically into that cell or on top of that cell. The number of rows changes from sheet to sheet, and to allow room for the picture, the rows may be, the first three, then the next three then the next three So my sheet may look like this; ball.bmp | 1 | "need image inserted into cell A1" |.750 | | 3.0 | bull.bmp | 1 | "need image inserted into cell A4" |.500 | | 3.5 | squ .bmp | 1 | "need image inserted into cell A7" |.375 | | 3.2 | etc, etc as long as the sheet might be... I think, I would be checking every fourth row for a file name Would this code work or would I be better of using something else? Thanks Jeff W. .................................................. .................................................. ................... The code deletes all old pictures and then adds all the pictures. It is not easy to only delete the pictures that have changed. Sub add_pictures() Const PictureHeight = 25 'delete pictures For Each shp In ActiveSheet.Shapes If shp.Type = msoPicture Then shp.Delete End If Next shp LastRow = Cells(Rows.Count, "A").End(xlUp).Row Rows(2).RowHeight = PictureHeight For Each cell In Range("B1:K1") If cell < "" Then Set pict = ActiveSheet.Pictures. _ Insert(cell.Value) pict.ShapeRange.LockAspectRatio = msoTrue pict.ShapeRange.Height = PictureHeight pict.Top = cell.Offset(1, 0).Top pict.Left = cell.Offset(1, 0).Left End If Next cell End Sub .................................................. .................................................. ................... |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Inserting Pictures Based On File name
Thank you for the responce Cory, although I do have a concern or two.
I have my image files in a sub folder of the workbook folder, for example c:\setup This is where the wookbook is stored c:\setup\images This is where the images files are Would I need something to point to this folder in order for it to find the images files? I'm assuming that the code you supplied would need to be in a sub routine then ran when I wanted to get the pictures inserted, is this correct? Thanks, <Jeff "Corey" wrote in message ... Not sure if will assist or hinder, but i use the following code to input a picture into a sheet based on Cell(A1): ' Photo 1 Application.ScreenUpdating = False Dim WB As Workbook Dim SH As Worksheet Dim rng As Range Dim mypic As Picture Dim res As Variant Set WB = ActiveWorkbook res = sheet1.Range("A1").value If res = False Then Exit Sub Set SH = ActiveSheet Set rng = ActiveCell Set mypic = SH.Pictures.Insert(res) With mypic .Top = rng.Top .Left = rng.Left .Locked = False mypic.ShapeRange.LockAspectRatio = msoFalse mypic.ShapeRange.Height = 213.1 mypic.ShapeRange.Width = 249.2 mypic.ShapeRange.Rotation = 0# End With End If Corey.... "Jeff W." wrote in message ... I have this code from another thread posted by Joel, and I want to find out if it will work for me On my sheet I have a file name in column A and this is the name of the file I want to insert automatically into that cell or on top of that cell. The number of rows changes from sheet to sheet, and to allow room for the picture, the rows may be, the first three, then the next three then the next three So my sheet may look like this; ball.bmp | 1 | "need image inserted into cell A1" |.750 | | 3.0 | bull.bmp | 1 | "need image inserted into cell A4" |.500 | | 3.5 | squ .bmp | 1 | "need image inserted into cell A7" |.375 | | 3.2 | etc, etc as long as the sheet might be... I think, I would be checking every fourth row for a file name Would this code work or would I be better of using something else? Thanks Jeff W. .................................................. .................................................. .................. The code deletes all old pictures and then adds all the pictures. It is not easy to only delete the pictures that have changed. Sub add_pictures() Const PictureHeight = 25 'delete pictures For Each shp In ActiveSheet.Shapes If shp.Type = msoPicture Then shp.Delete End If Next shp LastRow = Cells(Rows.Count, "A").End(xlUp).Row Rows(2).RowHeight = PictureHeight For Each cell In Range("B1:K1") If cell < "" Then Set pict = ActiveSheet.Pictures. _ Insert(cell.Value) pict.ShapeRange.LockAspectRatio = msoTrue pict.ShapeRange.Height = PictureHeight pict.Top = cell.Offset(1, 0).Top pict.Left = cell.Offset(1, 0).Left End If Next cell End Sub .................................................. .................................................. .................. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Inserting Pictures Based On File name
Jeff,
The code i pasted uses (res) I have a cell below the picture location with the address of the image/pic like : \\Server\server\My Pictures\ABC123.jpg This way it does not matter where the pictures are stored provided they are not renamed or moved. Corey.... "Jeff W." wrote in message ... Thank you for the responce Cory, although I do have a concern or two. I have my image files in a sub folder of the workbook folder, for example c:\setup This is where the wookbook is stored c:\setup\images This is where the images files are Would I need something to point to this folder in order for it to find the images files? I'm assuming that the code you supplied would need to be in a sub routine then ran when I wanted to get the pictures inserted, is this correct? Thanks, <Jeff "Corey" wrote in message ... Not sure if will assist or hinder, but i use the following code to input a picture into a sheet based on Cell(A1): ' Photo 1 Application.ScreenUpdating = False Dim WB As Workbook Dim SH As Worksheet Dim rng As Range Dim mypic As Picture Dim res As Variant Set WB = ActiveWorkbook res = sheet1.Range("A1").value If res = False Then Exit Sub Set SH = ActiveSheet Set rng = ActiveCell Set mypic = SH.Pictures.Insert(res) With mypic .Top = rng.Top .Left = rng.Left .Locked = False mypic.ShapeRange.LockAspectRatio = msoFalse mypic.ShapeRange.Height = 213.1 mypic.ShapeRange.Width = 249.2 mypic.ShapeRange.Rotation = 0# End With End If Corey.... "Jeff W." wrote in message ... I have this code from another thread posted by Joel, and I want to find out if it will work for me On my sheet I have a file name in column A and this is the name of the file I want to insert automatically into that cell or on top of that cell. The number of rows changes from sheet to sheet, and to allow room for the picture, the rows may be, the first three, then the next three then the next three So my sheet may look like this; ball.bmp | 1 | "need image inserted into cell A1" |.750 | | 3.0 | bull.bmp | 1 | "need image inserted into cell A4" |.500 | | 3.5 | squ .bmp | 1 | "need image inserted into cell A7" |.375 | | 3.2 | etc, etc as long as the sheet might be... I think, I would be checking every fourth row for a file name Would this code work or would I be better of using something else? Thanks Jeff W. .................................................. .................................................. .................. The code deletes all old pictures and then adds all the pictures. It is not easy to only delete the pictures that have changed. Sub add_pictures() Const PictureHeight = 25 'delete pictures For Each shp In ActiveSheet.Shapes If shp.Type = msoPicture Then shp.Delete End If Next shp LastRow = Cells(Rows.Count, "A").End(xlUp).Row Rows(2).RowHeight = PictureHeight For Each cell In Range("B1:K1") If cell < "" Then Set pict = ActiveSheet.Pictures. _ Insert(cell.Value) pict.ShapeRange.LockAspectRatio = msoTrue pict.ShapeRange.Height = PictureHeight pict.Top = cell.Offset(1, 0).Top pict.Left = cell.Offset(1, 0).Left End If Next cell End Sub .................................................. .................................................. .................. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Inserting Pictures Based On File name
Cory, I'm sorry but I don't understand
I'm not really a VBA programmer so there are many things that I don't to change or add in order to get this to work. On my sheet, in cell A1 I have ball.gif in cell A4 I have bore.gif in cell A7 I have bull.gif and in cell A10 I have center.gif I pasted that code into a module inside a sub and it didn't run when I called that sub, I have to rem out the "end if" line but then when I run it , it error's out on the following line; Set mypic = SH.Pictures.Insert(res) I mean this is the line the debugger high lights and I don't really know why or where to go from here... <Jeff "Corey" wrote in message ... Jeff, The code i pasted uses (res) I have a cell below the picture location with the address of the image/pic like : \\Server\server\My Pictures\ABC123.jpg This way it does not matter where the pictures are stored provided they are not renamed or moved. Corey.... "Jeff W." wrote in message ... Thank you for the responce Cory, although I do have a concern or two. I have my image files in a sub folder of the workbook folder, for example c:\setup This is where the wookbook is stored c:\setup\images This is where the images files are Would I need something to point to this folder in order for it to find the images files? I'm assuming that the code you supplied would need to be in a sub routine then ran when I wanted to get the pictures inserted, is this correct? Thanks, <Jeff "Corey" wrote in message ... Not sure if will assist or hinder, but i use the following code to input a picture into a sheet based on Cell(A1): ' Photo 1 Application.ScreenUpdating = False Dim WB As Workbook Dim SH As Worksheet Dim rng As Range Dim mypic As Picture Dim res As Variant Set WB = ActiveWorkbook res = sheet1.Range("A1").value If res = False Then Exit Sub Set SH = ActiveSheet Set rng = ActiveCell Set mypic = SH.Pictures.Insert(res) With mypic .Top = rng.Top .Left = rng.Left .Locked = False mypic.ShapeRange.LockAspectRatio = msoFalse mypic.ShapeRange.Height = 213.1 mypic.ShapeRange.Width = 249.2 mypic.ShapeRange.Rotation = 0# End With End If Corey.... "Jeff W." wrote in message ... I have this code from another thread posted by Joel, and I want to find out if it will work for me On my sheet I have a file name in column A and this is the name of the file I want to insert automatically into that cell or on top of that cell. The number of rows changes from sheet to sheet, and to allow room for the picture, the rows may be, the first three, then the next three then the next three So my sheet may look like this; ball.bmp | 1 | "need image inserted into cell A1" |.750 | | 3.0 | bull.bmp | 1 | "need image inserted into cell A4" |.500 | | 3.5 | squ .bmp | 1 | "need image inserted into cell A7" |.375 | | 3.2 | etc, etc as long as the sheet might be... I think, I would be checking every fourth row for a file name Would this code work or would I be better of using something else? Thanks Jeff W. .................................................. .................................................. .................. The code deletes all old pictures and then adds all the pictures. It is not easy to only delete the pictures that have changed. Sub add_pictures() Const PictureHeight = 25 'delete pictures For Each shp In ActiveSheet.Shapes If shp.Type = msoPicture Then shp.Delete End If Next shp LastRow = Cells(Rows.Count, "A").End(xlUp).Row Rows(2).RowHeight = PictureHeight For Each cell In Range("B1:K1") If cell < "" Then Set pict = ActiveSheet.Pictures. _ Insert(cell.Value) pict.ShapeRange.LockAspectRatio = msoTrue pict.ShapeRange.Height = PictureHeight pict.Top = cell.Offset(1, 0).Top pict.Left = cell.Offset(1, 0).Left End If Next cell End Sub .................................................. .................................................. .................. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do you export pictures from my pictures file into a word docu | New Users to Excel | |||
Inserting Pictures | Excel Discussion (Misc queries) | |||
Inserting Pictures | Excel Discussion (Misc queries) | |||
Inserting Pictures Help Please | Excel Programming | |||
inserting more than 65536 pictures into file using VBA | Excel Programming |