Inserting Photos using a Loop: Deviation from Target Cell Incrementally Increases

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

edited Nov 13 at 23:23

asked Nov 12 at 8:08

Cake

112

So, using your example, first pic is in A6, the second is in A29, then A52 ?
– CLR
Nov 12 at 10:54

Yes, that is correct. I do not know if it helps, but the increasing deviation occurs downwards. For example, the second photo might be 5 pixels lower than its intended position, the third 10, and so on.
– Cake
Nov 12 at 12:19

Could there be something resizing or otherwise altering the rows after the pictures are placed in the sheet?
– eirikdaude
Nov 13 at 14:15

1

There are no procedures altering the rows but I have made some new observations based on what you mentioned. I will edit my post to reflect that. Thank you for the ideas.
– Cake
Nov 13 at 23:09

add a comment |

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

edited Nov 13 at 23:23

asked Nov 12 at 8:08

Cake

112

So, using your example, first pic is in A6, the second is in A29, then A52 ?
– CLR
Nov 12 at 10:54

Yes, that is correct. I do not know if it helps, but the increasing deviation occurs downwards. For example, the second photo might be 5 pixels lower than its intended position, the third 10, and so on.
– Cake
Nov 12 at 12:19

Could there be something resizing or otherwise altering the rows after the pictures are placed in the sheet?
– eirikdaude
Nov 13 at 14:15

1

There are no procedures altering the rows but I have made some new observations based on what you mentioned. I will edit my post to reflect that. Thank you for the ideas.
– Cake
Nov 13 at 23:09

add a comment |

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

edited Nov 13 at 23:23

asked Nov 12 at 8:08

Cake

112

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

excel vba excel-vba

edited Nov 13 at 23:23

asked Nov 12 at 8:08

Cake

112

edited Nov 13 at 23:23

asked Nov 12 at 8:08

Cake

112

edited Nov 13 at 23:23

asked Nov 12 at 8:08

Cake

112

asked Nov 12 at 8:08

Cake

112

asked Nov 12 at 8:08

Cake

112

So, using your example, first pic is in A6, the second is in A29, then A52 ?
– CLR
Nov 12 at 10:54

Yes, that is correct. I do not know if it helps, but the increasing deviation occurs downwards. For example, the second photo might be 5 pixels lower than its intended position, the third 10, and so on.
– Cake
Nov 12 at 12:19

Could there be something resizing or otherwise altering the rows after the pictures are placed in the sheet?
– eirikdaude
Nov 13 at 14:15

1

There are no procedures altering the rows but I have made some new observations based on what you mentioned. I will edit my post to reflect that. Thank you for the ideas.
– Cake
Nov 13 at 23:09

add a comment |

So, using your example, first pic is in A6, the second is in A29, then A52 ?
– CLR
Nov 12 at 10:54

Yes, that is correct. I do not know if it helps, but the increasing deviation occurs downwards. For example, the second photo might be 5 pixels lower than its intended position, the third 10, and so on.
– Cake
Nov 12 at 12:19

Could there be something resizing or otherwise altering the rows after the pictures are placed in the sheet?
– eirikdaude
Nov 13 at 14:15

1

There are no procedures altering the rows but I have made some new observations based on what you mentioned. I will edit my post to reflect that. Thank you for the ideas.
– Cake
Nov 13 at 23:09

So, using your example, first pic is in A6, the second is in A29, then A52 ?
– CLR
Nov 12 at 10:54

Yes, that is correct. I do not know if it helps, but the increasing deviation occurs downwards. For example, the second photo might be 5 pixels lower than its intended position, the third 10, and so on.
– Cake
Nov 12 at 12:19

Could there be something resizing or otherwise altering the rows after the pictures are placed in the sheet?
– eirikdaude
Nov 13 at 14:15

There are no procedures altering the rows but I have made some new observations based on what you mentioned. I will edit my post to reflect that. Thank you for the ideas.
– Cake
Nov 13 at 23:09

add a comment |

1 Answer
1

active

oldest

votes

-1

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?

answered Nov 12 at 14:49

CLR

5,7311320

Thank you for your response. Unfortunately, it does not address my issue. Just to be sure, I have confirmed that there are no such “blank” pixels in all my photos.
– Cake
Nov 12 at 21:54

Can you confirm you're using the code above and it's not working? I was wondering if the measurements for .Top and .Left were based on a different sheet (where perhaps the rows aren't identically sized) but in my code the use of ws would prevent that.
– CLR
Nov 13 at 11:14

add a comment |

Your Answer

StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");

StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);

else
createEditor();

);

function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);

);

draft saved

draft discarded

StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53258043%2finserting-photos-using-a-loop-deviation-from-target-cell-incrementally-increase%23new-answer', 'question_page');

);

Post as a guest

Name

Required, but never shown

1 Answer
1

active

oldest

votes

1 Answer
1

active

oldest

votes

-1

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?

answered Nov 12 at 14:49

CLR

5,7311320

Thank you for your response. Unfortunately, it does not address my issue. Just to be sure, I have confirmed that there are no such “blank” pixels in all my photos.
– Cake
Nov 12 at 21:54

Can you confirm you're using the code above and it's not working? I was wondering if the measurements for .Top and .Left were based on a different sheet (where perhaps the rows aren't identically sized) but in my code the use of ws would prevent that.
– CLR
Nov 13 at 11:14

add a comment |

-1

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?

answered Nov 12 at 14:49

CLR

5,7311320

Thank you for your response. Unfortunately, it does not address my issue. Just to be sure, I have confirmed that there are no such “blank” pixels in all my photos.
– Cake
Nov 12 at 21:54

Can you confirm you're using the code above and it's not working? I was wondering if the measurements for .Top and .Left were based on a different sheet (where perhaps the rows aren't identically sized) but in my code the use of ws would prevent that.
– CLR
Nov 13 at 11:14

add a comment |

-1

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?

answered Nov 12 at 14:49

CLR

5,7311320

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?

answered Nov 12 at 14:49

CLR

5,7311320

answered Nov 12 at 14:49

CLR

5,7311320

answered Nov 12 at 14:49

CLR

5,7311320

answered Nov 12 at 14:49

CLR

5,7311320

Thank you for your response. Unfortunately, it does not address my issue. Just to be sure, I have confirmed that there are no such “blank” pixels in all my photos.
– Cake
Nov 12 at 21:54

Can you confirm you're using the code above and it's not working? I was wondering if the measurements for .Top and .Left were based on a different sheet (where perhaps the rows aren't identically sized) but in my code the use of ws would prevent that.
– CLR
Nov 13 at 11:14

add a comment |

Thank you for your response. Unfortunately, it does not address my issue. Just to be sure, I have confirmed that there are no such “blank” pixels in all my photos.
– Cake
Nov 12 at 21:54

Can you confirm you're using the code above and it's not working? I was wondering if the measurements for .Top and .Left were based on a different sheet (where perhaps the rows aren't identically sized) but in my code the use of ws would prevent that.
– CLR
Nov 13 at 11:14

Thank you for your response. Unfortunately, it does not address my issue. Just to be sure, I have confirmed that there are no such “blank” pixels in all my photos.
– Cake
Nov 12 at 21:54

Can you confirm you're using the code above and it's not working? I was wondering if the measurements for .Top and .Left were based on a different sheet (where perhaps the rows aren't identically sized) but in my code the use of ws would prevent that.
– CLR
Nov 13 at 11:14

add a comment |

draft saved

draft discarded

Thanks for contributing an answer to Stack Overflow!

Please be sure to answer the question. Provide details and share your research!

But avoid …

Asking for help, clarification, or responding to other answers.

Making statements based on opinion; back them up with references or personal experience.

To learn more, see our tips on writing great answers.

Some of your past answers have not been well-received, and you're in danger of being blocked from answering.

Please pay close attention to the following guidance:

Please be sure to answer the question. Provide details and share your research!

But avoid …

Asking for help, clarification, or responding to other answers.

Making statements based on opinion; back them up with references or personal experience.

To learn more, see our tips on writing great answers.

draft saved

draft discarded

Post as a guest

Name

Required, but never shown

Name

Required, but never shown

Name

Required, but never shown

This page is only for reference, If you need detailed information, please check here

搜尋此網誌

Odtnhj