Results 1 to 5 of 5
  1. #1
    Lounger
    Join Date
    Dec 2004
    Posts
    25
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Insert pictures based on cell text (excel 97)

    Hi fellows,
    I am trying to insert pictures by matching the cell text in the range A3:A150. The Idea is to insert these pictures starting in cell B200, next one in D200, 3rd one in F200 and 4th, 5th 6th go in the next row (i.e. B201, D201, F201) and so on depending on however many cells are filled in range A3:A150.

    The code I came up with is posting all pictures in cell F200 (I tried with 3 cells filled in A3:A150 range). Can I size the pictures through code?? Please help me with this issue. Thanks

    Sub Get_picture()
    Dim pic As Picture
    Dim fname
    Dim rngSource As Range
    Dim rngDestination As Range
    Dim i As Integer
    Dim j As Integer
    Dim lngDestinationRow As Long

    Set rngSource = Worksheets("sheet2").Range("A1:A150")
    For i = 1 To rngSource.Rows.Count
    fname = rngSource(i, 1).Text
    lngDestinationRow = 200
    For j = 2 To 6 Step 2
    Set rngDestination = Worksheets("sheet2").Cells(lngDestinationRow, j)
    rngDestination.Select
    Next j
    Set pic = ActiveSheet.Pictures.Insert ("Cictures" & fname & ".jpg")
    Next i
    End Sub

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Insert pictures based on cell text (excel 97)

    You don't do anything useful inside the For j = ... loop. Try this:

    Sub Get_picture()
    Dim fname
    Dim rngSource As Range
    Dim rngDestination As Range
    Dim i As Integer
    Dim lngDestinationRow As Long
    Dim lngDestinationCol As Long

    Set rngSource = Worksheets("Sheet2").Range("A3:A150")
    For i = 1 To rngSource.Rows.Count
    fname = rngSource.Cells(i, 1).Text
    lngDestinationRow = i 3 + 199
    lngDestinationCol = (i Mod 3) * 2 + 2
    Set rngDestination = Worksheets("Sheet2").Cells(lngDestinationRow, lngDestinationCol)
    If Not fname = "" Then
    ActiveSheet.Shapes.AddPicture "Cictures" & fname & ".jpg", _
    False, True, rngDestination.Left, rngDestination.Top, _
    rngDestination.Width, rngDestination.Height
    End If
    Next i

    Set rngDestination = Nothing
    Set rngSource = Nothing
    End Sub

  3. #3
    Lounger
    Join Date
    Dec 2004
    Posts
    25
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Insert pictures based on cell text (excel 97)

    Bingo!! Thank you very much
    One additional request is:
    I am getting an error message "Run-time error '1004': Specified file wasn't found" in case the picture file does not exist. Is there a way to fix that because not all the entries in the source range would have the pictures to go with.
    Thanks
    -Siz

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Insert pictures based on cell text (excel 97)

    ...
    If Not fname = "" Then
    If Not Dir("Cictures" & fname & ".jpg") = "" Then
    ActiveSheet.Shapes.AddPicture "Cictures" & fname & ".jpg", _
    False, True, rngDestination.Left, rngDestination.Top, _
    rngDestination.Width, rngDestination.Height
    End If
    End If
    ...

  5. #5
    Lounger
    Join Date
    Dec 2004
    Posts
    25
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Insert pictures based on cell text (excel 97)

    Hans, This is great, doing exactly what I wanted.

    Many thanks for yet another rescue.
    Siz

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •