Code to insert a picture, not a link

[ner1277]

Slightly different problem than I saw in other threads: When I step through this code it put the image in the worksheet but gives me an error on the line in bold:

The error is object variable or with block variable not set. If It didn't get hung up on the error it would be fine. The goal is an image not linked so if i sent it to someone it works.

Sub xxx()

Dim fName As String
Dim pic1 As Object
Dim r As Range

filelocation = Range("path").Value & "\"
sheetstotal = Sheets.Count 'ADD
path = filelocation
file = Dir(path)
i = sheetstotal - 1 'EDIT
If Dir(filelocation) = "" Then Exit Sub

filelocation = path + file
Set r = ActiveCell

Set pic1 = Nothing
pic1 = ActiveSheet.Shapes.AddPicture(Filename:=filelocation, LinkToFile:=False, SaveWithDocument:=True, Left:=r.Left, Top:=r.Top, Width:=r.Width * 8, Height:=r.Height * 22)
pic1.Select

End Sub

 

[Domenic]

Since you're assigning an object to a variable, you'll need to use the keyword Set...

Set pic1 = ActiveSheet.Shapes.AddPicture(Filename:=filelocation, LinkToFile:=False, SaveWithDocument:=True, Left:=r.Left, Top:=r.Top, Width:=r.Width * 8, Height:=r.Height * 22)

Hope this helps!

 

[ner1277]

I tried that and it didn’t work. I had it originally that way without the set pic1 = nothing and then I just said set pic1 = …. And it was the same result.

I also tried assigning pic1 as a picture and not an object. No difference. Any chance I’m missing a library or something weird like that.

 

[Domenic]

Can you confirm the exact error message when trying...

Set pic1 = ActiveSheet.Shapes.AddPicture( . . . .)

???

 

[ner1277]

So I did what you said and I guess I was changing so many things that I messed something else up, so you were right, but I swear I had something really close and it didn't work. I guess close isn't good enough, thanks for the help. Final code is below and this did work.

Sub xxx()

Dim fName As String
Dim pic1 As Object
Dim r As Range

filelocation = Range("path").Value & "\"
sheetstotal = Sheets.Count 'ADD
path = filelocation
file = Dir(path)
i = sheetstotal - 1 'EDIT
If Dir(filelocation) = "" Then Exit Sub

filelocation = path + file
Set r = ActiveCell
Set pic1 = ActiveSheet.Shapes.AddPicture(Filename:=filelocation, LinkToFile:=False, SaveWithDocument:=True, Left:=0, Top:=0, Width:=r.Width * 3, Height:=r.Height * 33)
End Sub

 

[Domenic]

That's great, I'm glad you've got it sorted it.

Cheers!