Myujth

I have to regularly make a report whereby a photo of each item used needs to be inserted into a specific position for printing. For example, if I use 3 items, I need to place a photo of each item in column A, row 6+(i-1)*23. I have tried using a loop and picture.insert but what always happens is that the first photo is perfectly placed while the second photo onwards gets incrementally deviated vertically. I would like to know if anyone has experienced this and if there are any solutions available. I apologize if there is already an answer but I was unable to find it. Also, apologies for the simplified code as I am typing from a mobile device.

For i=1 to lastRow
 Set targetCell = range(some range)
 Set pic = ws.pictures.insert(file name)
 With pic
 .Top = targetCell.Top
 .Left = targetCell.Left
 End With
Next i

Observations Part 1:

• this phenomenon occurs when I control the placement of the photos using a separate workbook, whether I use a loop or not




• without using a loop (pressing a button to execute the macro manually), the placement of the photos are perfect in Excel 2010 computers and are off horizontally (consistently) in Excel 2016 computers




• the abovementioned method involves writing the code in Excel 2016

Observations Part 2:

• I will start with solving the non-loop horizontal displacement issue before tackling the loop issue to try to better understand the problem




• it appears that the pixel size of each workbook are different, which could be the cause of the problem (different computers might have different pixel size definitions when the workbooks were created)




• for example, one workbook shows a 3.55 column width as 46 pixels while the other shows a 3.50 width as 49 pixels




• by copying a whole worksheet from one workbook and pasting it in another, I was able to make the pixel size of both workbooks consistent and that seems to have solved horizontal displacement (non-loop) issue




• the next step would be to try using the same workbook to implement the loops

I think the following should work:

For i = 1 To lastRow
 Set targetCell = ws.Cells(6 + (i - 1) * 23, 1)
 Set pic = ws.pictures.insert(file name)
 With pic
 .Top = targetCell.Top
 .Left = targetCell.Left
 End With
Next i

If there are still issues, it might be worth checking that the pictures don't include any blank/white pixel rows that make the image 'look' lower?