Expression error comes up when I tried to insert image and set linktofile:=msofalse

[renoz]

So i previous was using below coding and it works perfectly fine until i got to share it with someone.

i know i have to use the below coding but try to insert in several places it says "Expression error", so where should i put this in my original coding?

LinkToFile:=msoTrue, _ SaveWithDocument:=msoFalse

_______________________________________________________________________________________________________________________________


Sub InsertPhoto()

Dim i As Integer

Dim all_std As Integer

all_std = ActiveSheet.UsedRange.Rows.Count

On Error GoTo Close_Error

Dim x As Integer

x = 2

For i = x To all_std + 1


Cells(i, 21).Select

ActiveSheet.Pictures.Insert( _

"I:\2022\02_Campaign\04_Spring22\02_Images\PDP_SS22\00. all image\" & Cells(i, 19) & ".PNG").select_



Selection.ShapeRange.IncrementLeft 10.5

Selection.ShapeRange.IncrementTop 6#

Selection.ShapeRange.LockAspectRatio = msoTrue

Selection.ShapeRange.Height = 60.25

Selection.ShapeRange.Width = 60.25

Selection.ShapeRange.Rotation = 0#



x = x + 1


Next i



Close_Error:


i = x

Resume Next



x



End Sub

 

[Anthony47]

Those are options for Shapes.AddPicture method, so you have to replace your For i /Next i inner code, for example:

    Cells(I, 21).Select
    PosizLeft = Cells(I, 21).Left
    PosizTop = Cells(I, 21).Top
    Set mySh = ActiveSheet.Shapes.AddPicture(Filename:="I:\2022\02_Campaign\04_Spring22\02_Images\PDP_SS22\00. all image\" & Cells(I, 19) & ".PNG", _
       LinkToFile:=False, SaveWithDocument:=True, Left:=PosizLeft, Top:=PositTop, Width:=True, Height:=True)
    mySh.IncrementLeft 10.5
    mySh.IncrementTop 6#
    mySh.LockAspectRatio = msoTrue
    mySh.Height = 60.25
    mySh.Width = 60.25
    mySh.Rotation = 0
    x = x + 1

Bye

 

[renoz]

Those are options for Shapes.AddPicture method, so you have to replace your For i /Next i inner code, for example:

    Cells(I, 21).Select
    PosizLeft = Cells(I, 21).Left
    PosizTop = Cells(I, 21).Top
    Set mySh = ActiveSheet.Shapes.AddPicture(Filename:="I:\2022\02_Campaign\04_Spring22\02_Images\PDP_SS22\00. all image\" & Cells(I, 19) & ".PNG", _
       LinkToFile:=False, SaveWithDocument:=True, Left:=PosizLeft, Top:=PositTop, Width:=True, Height:=True)
    mySh.IncrementLeft 10.5
    mySh.IncrementTop 6#
    mySh.LockAspectRatio = msoTrue
    mySh.Height = 60.25
    mySh.Width = 60.25
    mySh.Rotation = 0
    x = x + 1

Bye

does it mean something like this? but looks like i got another error

Sub InsertPhoto()

Dim i As Integer
Dim all_std As Integer
all_std = ActiveSheet.UsedRange.Rows.Count
On Error GoTo Close_Error
Dim x As Integer
x = 2

For i = x To all_std + 1

  Cells(i, 21).Select
    PosizLeft = Cells(i, 21).Left
    PosizTop = Cells(i, 21).Top
    Set mySh = ActiveSheet.Shapes.AddPicture(Filename:="I:\2022\02_Campaign\04_Spring22\02_Images\PDP_SS22\00. all image\" & Cells(i, 19) & ".PNG", _
       LinkToFile:=False, SaveWithDocument:=True, Left:=PosizLeft, Top:=PositTop, Width:=True, Height:=True)
    mySh.IncrementLeft 10.5
    mySh.IncrementTop 6#
    mySh.LockAspectRatio = msoTrue
    mySh.Height = 60.25
    mySh.Width = 60.25
    mySh.Rotation = 0
    x = x + 1

Next i


Close_Error:

i = x
Resume Next

x

End Sub

 

[Anthony47]

Is that final X a typing error? What's for?

 

[renoz]

thanks i think it worked! but then the pictures are not showing where they're suppose to be.

I'm trying to reference the file name inserted in column H then my picture suppose to be showing my same row column D

however it's now not on the same row on column D but all pictures from all the rows are in the same position.

also for the last file i would like to retrieve on column H, it's in a random position now.

here below is my latest code

Sub InsertPhoto()

Dim i As Integer
Dim all_std As Integer
all_std = ActiveSheet.UsedRange.Rows.Count
On Error GoTo Close_Error
Dim x As Integer
x = 2

For i = x To all_std + 1

   Cells(i, 4).Select
    
    PosizLeft = Cells(i, 4).Left
    PosizTop = Cells(i, 4).Top
    Set mySh = ActiveSheet.Shapes.AddPicture(Filename:="D:\Tumi_Data\Desktop\RFID\01_Raw files\" & Cells(i, 8) & ".PNG", _
       LinkToFile:=False, SaveWithDocument:=True, Left:=PosizLeft, Top:=PositTop, Width:=True, Height:=True)
    mySh.IncrementLeft 10.5
    mySh.IncrementTop 6#
    mySh.LockAspectRatio = msoTrue
    mySh.Height = 60.25
    mySh.Width = 60.25
    mySh.Rotation = 0
    x = x + 1

Next i


Close_Error:

i = x
Resume Next

End Sub

 

[Anthony47]

I mistyped a row:
Not PosizTop = Cells(I, 21).Top
but

PositTop = Cells(I, 21).Top

Bye

 

[renoz]

I mistyped a row:
Not PosizTop = Cells(I, 21).Top
but

PositTop = Cells(I, 21).Top

Bye

IT WORKS PERFECTLY WELL except on the last row, the image is still at a random position (where the star is) which suppose to be at the blue cell

 

[Anthony47]

Are you sure that it was inserted by the macro, or was it an already there picture?
Also, how do you remove old pictures before inserting the new ones?

So my suggestion is:
Before the current For I /Next I add this loop to remove the pictures:

For Each mySh In ActiveSheet.Shapes
    If Left(mySh.Name, 6) = "ZcPIC_" Then
        mySh.Delete
    End If
Next mySh

Then add the following line just after mySh = Set etc etc:

    mySh.Name = "ZcPIC_" & Cells(I, 4).Address(0, 0)

Unfortunately the first step is remove manually all the pictures, but from that moment the new added loop will remove the pictures previously added by the macro.
Bye