July 31st, 2003, 10:18 AM
Need help with inserting pictures into Excel automatically
I need some help on writing some VB code to insert picture files automatically within Excel 2000 !!!!
Basically, I have an Excel spreadsheet with a column that has a list of picture filenames (without their paths and file extensions.) What I want to do is to write a little VB macro to pick up the filenames in each cell in the column, add it to a hard-coded path and file extension, and then load the picture into another specific cell on the spreadsheet. Also, I would like to hard-code the size of each picture so that they are all the same size. Ideally, the order of the pictures should be the same as the order of the filenames in the column. Obviously, I will need to specify the range of cells from which to access the picture files so it can process one after the other but how I do not know!
This is so that I can press a button and it loads the pictures up automatically.
Unfortunately, I know nothing about VB and very little about macros, so I would be VERY grateful if anyone could help me as soon as possible!!!!
August 1st, 2003, 06:11 AM
The easiest why to create a macro is to record it, and see how they did it.
What I would have done 4 your prob is to create a constant 4 the path (guess you knew that): dim sPath = "c:\whatever\"
I'll then loop through the cells until it finds a blank:
Sorry, this is from the top of my head (didn't test it), so there might be errors.
dim i = 1
dim bContinue = true
sFileName = worksheets(1).cells(i,1).value
'worksheets is the number of the sheeg, e.g. sheet1 = 1 sheet 2 = 2 etc.
'cells is the cell, the first parameter the row, the second the column
if sFileName = "" then
bContinue = false
cells(i,2).select 'select the cell where it should be inserted
ActiveSheet.Pictures.Insert(sPath & sFilName).Select 'inserts picture and select it
Selection.ShapeRange.ScaleWidth 0.47, msoFalse, msoScaleFromTopLeft
Selection.ShapeRange.ScaleHeight 0.47, msoFalse, msoScaleFromTopLeft
i = i + 1
Play around with recording macro's, and the help file is quite usefull (when you learn how to use it). Macro's are the easiest thing to write once you know how.
August 4th, 2003, 04:49 AM
VB Script in Excel
Thanks very much for that Silian. I have been looking at the macros and following your advice, I have written some code (see below.) However, when I place it behind a button, it doesn't seem to do anything. Can you see anything wrong with it?
In order to test the idea of loading up several images from several filenames in a column, I entered the names of picture files in a handful of cells within the first column. There was one thing I was wondering about in relation to how a cell is referenced. In the code you gave me, you used integers e.g. (1, 1) but when I record a basic macro to select a cell in which to load a picture, it recorded:
Should letters be used for column references or does it not matter?
Anyway, this is what I've got so far:
Dim i As Integer
Dim sFilename As Integer
Dim bcontinue As Boolean
Dim spath As String
spath = "O:\tristan\design_development"
sFilename = Worksheets(1).Cells(i, 1).Value
If sFilename = "" Then
bcontinue = False
ActiveSheet.Pictures.Insert(spath + sFilename).Select
Selection.ShapeRange.LockAspectRatio = msoFalse
Selection.ShapeRange.Height = 141.75
Selection.ShapeRange.Width = 141.75
i = i + 1
Once again, I would be very grateful if you could help me out - I'm actually a Progress Developer by trade but I'm trying to learn!!
August 4th, 2003, 05:24 AM
Cool. It's always good to learn new things, so I might bug you abt what you do (I'm trying to get out of development - though not very hard, bcs I don't have a clue what else 2 do )
First thing: Your while loop. You are basically telling the computer that it must continue doing this, while bContinue is true. OK, so you declare your bContinue (which automatically sets it to false) and have this while loop. The code isn't going to be executed, bcs while is false. Before the while loop, add the statement: bContinue = true
Second: I'm not sure if this is going 2 cause problems, bcs VB is pretty lenient. You declare your file Name as an integer. This is all good and well if the contents of the cell are integer values, but if they are strings, you might have a prob (rather declare sFIleName as string). Remember that the filename need an extension as well (.gif, etc). You can either add this in your cell, or change the line to:
ActiveSheet.Pictures.Insert(spath + sFilename + ".gif").Select
'or whatever the file extention is.
When you record a macro in excel, it always uses the range object: Range("A1").select. If you use the range object, you must always use alphabetical characters. I, personally prefer to use the cells object: Cells(iRow, iColumn).select.
(Although I try to select cells, etc. as little as I can get away with. It makes the code run faster.
e.g. instead of using
ActiveCell.value = "abc"
Unfortunatly, you sometimes have to select a sell e.g. when you need to insert a picture at a specified spot.)
cells(1,1).value = "abc"
Hope this made sense.
December 9th, 2007, 07:48 AM
Im sorry for acting/sounding dumb here, but im a real novice when it comes to this sort of stuff..
I want to do basically what the person here has done.
I want 1 image icon on my spreadsheet, then with the help of a combobox, select an item and the image appears in the image box.
I have no idea how to code this, or if it is the code above, where to put it.
Any help would be largely appreciative.
Please post here, or if you can make a 'copy' and sent it to my email address (firstname.lastname@example.org) then that would be very helpful. I learn more by looking at some work, than looking at posts.
April 13th, 2010, 03:24 AM
Insert .Jpg Image in Any Cell of Excel Using Visual Basic 6.0
Private Sub Command1_Click()
'With the macro below you can insert pictures and fit them to any range in a worksheet.
Public Sub PutYourImage()
SetPicture "C:\test.jpg", "F" & 2
Public Sub SetPicture(PictureFileName As String, D As String)
Dim R As Range
Set WB = EX.Workbooks.Open("C:\TEST.XLS")
Set WS = WB.Worksheets("1")
Set R = WS.Range(D)
Dim p As Object, t As Double, l As Double, w As Double, h As Double, s As Double
If TypeName(ActiveSheet) <> "Worksheet" Then Exit Sub
If Dir(PictureFileName) = "" Then Exit Sub
Set p = WB.ActiveSheet.Pictures.Insert(PictureFileName)
' Set Picture Location
t = .Top
l = .Left
s = .Width
w = .Offset(0, .Columns.Count).Left - .Left
h = .Offset(.Rows.Count, 0).Top - .Top
' position picture
.Top = t
.Left = l
.Width = w
.Height = h
Set p = Nothing
August 12th, 2010, 01:03 PM
Multiple sheet input
The information posted here has been very helpful. However I have a couple of problems that I have not been able to resolve myself.
1. If I have 10 images (uniquely named) 1.jpg, 2.jpg, 3.jpg and so on... in a directory called "C:\Workbook" and in that same directory I have a workbook called Autofill_workbook.xls. How do I get these (uniquely named) images to show up (1 on each worksheet) in the workbook?
2. Can I get the images to load automatically when I open the workbook for the first time?
Any help would be greatly appreciated!
June 27th, 2013, 04:14 AM
Exactly the same problem
Hello. This is an old post but I have exactly the same problem and I desperately need an answer for it. I do not code so you need what to do. THANK YOU!!
Originally Posted by isnemo