#1
  1. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2003
    Location
    Scotland
    Posts
    7
    Rep Power
    0

    Question 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!!!!

    Thank you!!!!!
  2. #2
  3. Gogga
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2003
    Posts
    198
    Rep Power
    21
    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:
    Code:
    dim i = 1
    dim sFileName
    dim bContinue = true
    while bContinue
      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
      else
        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
      end if
    wend
    Sorry, this is from the top of my head (didn't test it), so there might be errors.

    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.
  4. #3
  5. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2003
    Location
    Scotland
    Posts
    7
    Rep Power
    0

    Thumbs up 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:
    Range("A1").Select.

    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

    Sub Attempt1()

    spath = "O:\tristan\design_development"

    While bcontinue
    sFilename = Worksheets(1).Cells(i, 1).Value
    If sFilename = "" Then
    bcontinue = False
    Else
    Cells(i, 2).Select
    ActiveSheet.Pictures.Insert(spath + sFilename).Select
    Selection.ShapeRange.LockAspectRatio = msoFalse
    Selection.ShapeRange.Height = 141.75
    Selection.ShapeRange.Width = 141.75
    i = i + 1
    End If
    Wend


    End Sub


    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!!

    Cheers

    tristyboy
  6. #4
  7. Gogga
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2003
    Posts
    198
    Rep Power
    21
    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
    Code:
    cells(1,1).select
    ActiveCell.value = "abc"
    I'll use
    Code:
    cells(1,1).value = "abc"
    Unfortunatly, you sometimes have to select a sell e.g. when you need to insert a picture at a specified spot.)

    Hope this made sense.
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2007
    Posts
    1
    Rep Power
    0
    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 (agoodall1807@hotmail.com) then that would be very helpful. I learn more by looking at some work, than looking at posts.
  10. #6
  11. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2010
    Posts
    1
    Rep Power
    0

    Thumbs up Insert .Jpg Image in Any Cell of Excel Using Visual Basic 6.0


    Private Sub Command1_Click()
    PutYourImage
    End Sub
    '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
    End Sub

    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

    'importing picture
    Set p = WB.ActiveSheet.Pictures.Insert(PictureFileName)
    ' Set Picture Location
    With R
    t = .Top
    l = .Left
    s = .Width
    w = .Offset(0, .Columns.Count).Left - .Left
    h = .Offset(.Rows.Count, 0).Top - .Top
    End With
    ' position picture
    With p
    .Top = t
    .Left = l
    .Width = w
    .Height = h
    End With

    Set p = Nothing
    WB.Save
    WB.Close
    EX.Quit
    End Sub
  12. #7
  13. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2010
    Posts
    1
    Rep Power
    0

    Multiple sheet input


    Hi,
    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!

    Nemo
  14. #8
  15. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2013
    Posts
    1
    Rep Power
    0

    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
    Hi,
    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!

    Nemo

IMN logo majestic logo threadwatch logo seochat tools logo