0

I am trying to insert a picture into excel based off a cell value. The Cell value is in the image path. I am new, what I have is partially based on recording the macro and part from looking stuff up. This is what I tried...

I keep getting an error on the ActiveSheet.Pictures.Insert line

Sub Part_Picture()
'
' Part_Picture Macro
'
Dim imageName As String
Dim imageFolder As String
Dim imagePath As String

For Each Cell In Range("B7")
    imageName = Cell.Value
    imageFolder = "Q:\New Project Part Folders\Elizabeth Delgado\Database pictures\Part\" & imageName
    imagePath = imageFolder & ".jpg"

    Range("B11").Select
    '
    ActiveSheet.Pictures.Insert(imagePath).Select
Next Cell
End Sub
chris neilsen
  • 52,446
  • 10
  • 84
  • 123
ca2ky
  • 1
  • 2
  • 3
    what is the question? what is code not doing that you want it to? If you expect code to place pictures in different areas of the workbook let us know how you want to insert them ... i.e. - every 8 rows or something. – Scott Holtzman May 08 '17 at 19:42
  • 4
    Get rid of all the `.Select` stuff, that's macro-recorder poison teaching you the wrong things by mimicking every single user action that was recorded. e.g. do `For Each cell In Range("B7:G9")`, and declare that `cell` variable (specify `Option Explicit` at the top of the module) - and then yeah, ask a clear question that can be answered. – Mathieu Guindon May 08 '17 at 20:02
  • 1
    @Mat'sMug "macro-recorder poison" -- I like that phrase, although I might prefer "crud" to "poison" (reserving "poison" for blanket uses of `On Error Resume Next`). – John Coleman May 08 '17 at 20:05
  • 1
    What error message are you getting exactly? Does it blow up at the first iteration or it processes a couple of cells correctly? What's the data like in `B7:G9`? What's the value of `imageFolder` when things blow up? Please a breakpoint (F9) on the `ActiveSheet.Pictures.Insert` instruction, run the code, and then use the *immediate pane* and the *locals* debugger toolwindows to help you find out what's wrong. – Mathieu Guindon May 08 '17 at 20:13
  • 1
    Does your ImagePath just have the name (without ".jpg")? That variable name is kinda misleading as your code makes it look like it should be the ImageName. – Rdster May 08 '17 at 20:14
  • @Mat'sMug I am not sure how to use those tools... I edited my code so it is a little more consise... I think. But I am still getting an error on the same line. Is there something big I am missing or just not doing? – ca2ky May 08 '17 at 20:54
  • Possible duplicate of [How to get images to appear in Excel given image url](http://stackoverflow.com/q/6312710/11683) – GSerg May 08 '17 at 20:57
  • You need to learn to use the debugger, otherwise nobody here can help you. F9 to place a breakpoint; then run the code. The yellow line is the line currently being executed, press F8 to execute one line at a time ("step through"), and type `?imagePath` in the *immediate pane* (Ctrl+G) to know the value of `imagePath` before you execute the `ActiveSheet.Pictures.Insert` instruction. – Mathieu Guindon May 08 '17 at 20:57
  • @Mat'sMug The imagePath looks correct. It looks like it did before I tried to make it based on a cell reference. It says it is a run-time error '1004' "Unable to get the insert property of the Pictures class" – ca2ky May 08 '17 at 21:12
  • Make sure you take a look at the question @GSerg linked. I'm sure it has your answer. – Mathieu Guindon May 08 '17 at 21:22
  • What does "Unable to get the insert property of the Pictures class" mean? – ca2ky May 08 '17 at 21:58

3 Answers3

0

"Unable to get the insert property of the Pictures class" is a generic error message which you may as well just translate as "Something went wrong with what you're trying to do and I can't give you more information". It's likely though that the path to the image file has not been build correctly.

1) Remove the .Select from your insert statement. Syntactically it makes no sense. Just use ActiveSheet.Pictures.Insert(imagePath)

2) Check the value in cell B7 is the file name only, not including the extension. Since your code adds ".jpg" you dont need that in B7.

3) Check the file is actually a jpg, not for instance a png

4) Check the file / folder actually exists

FYI For Each Cell In Range("B7") is only going to iterate one cell - B7 - and is unnecessary. If you only intended for one cell to be read you should use imageName = Range("B7").Value, or better yet since you need a string use imageName = Range("B7").Text

Absinthe
  • 3,258
  • 6
  • 31
  • 70
  • Thanks so much @absinthe I made the #1 change and then the changes you suggested in the FYI and it worked perfectly!! – ca2ky May 09 '17 at 14:43
0

Consider this option.

Sub InsertPics()
Dim fPath As String, fName As String
Dim r As Range, rng As Range

Application.ScreenUpdating = False
fPath = "C:\your_path_here\"
Set rng = Range("A1:A" & Cells(Rows.Count, 1).End(xlUp).Row)
i = 1

For Each r In rng
    fName = Dir(fPath)
    Do While fName <> ""
        If fName = r.Value Then
            With ActiveSheet.Pictures.Insert(fPath & fName)
                .ShapeRange.LockAspectRatio = msoTrue
                Set px = .ShapeRange
                If .ShapeRange.Width > Rows(i).Columns(2).Width Then .ShapeRange.Width = Columns(2).Width
                    With Cells(i, 2)
                        px.Top = .Top
                        px.Left = .Left
                        .RowHeight = px.Height
                    End With
            End With
        End If
        fName = Dir
    Loop
    i = i + 1
Next r
Application.ScreenUpdating = True
End Sub

' Note: you need the file extension, such as ',jpg', or whatever you are using, so you can match on that.

Whatever picture name you put in Column A, will be imported into the adjacent cell in, Column B

ASH
  • 20,759
  • 19
  • 87
  • 200
0

The .Pictures.Insert("c:\fixedfile.png") asked a fix file name as its parameter. However you may use FileCopy "desiredfile.png", "fixedfile.png" to replace the content of fixedfile.png which then meet your needs.

Tim
  • 1