Inserting Photos using a Loop: Deviation from Target Cell Incrementally Increases
up vote
2
down vote
favorite
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
excel vba excel-vba
add a comment |
up vote
2
down vote
favorite
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
excel vba excel-vba
So, using your example, first pic is inA6
, the second is inA29
, thenA52
?
– 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 |
up vote
2
down vote
favorite
up vote
2
down vote
favorite
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
excel vba excel-vba
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
excel vba excel-vba
excel vba excel-vba
edited Nov 13 at 23:23
asked Nov 12 at 8:08
Cake
112
112
So, using your example, first pic is inA6
, the second is inA29
, thenA52
?
– 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 inA6
, the second is inA29
, thenA52
?
– 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
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
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
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
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
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
up vote
-1
down vote
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?
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 ofws
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',
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
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
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
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
-1
down vote
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?
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 ofws
would prevent that.
– CLR
Nov 13 at 11:14
add a comment |
up vote
-1
down vote
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?
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 ofws
would prevent that.
– CLR
Nov 13 at 11:14
add a comment |
up vote
-1
down vote
up vote
-1
down vote
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?
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,7331320
5,7331320
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 ofws
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 ofws
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
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
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 |
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.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
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
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
So, using your example, first pic is in
A6
, the second is inA29
, thenA52
?– 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