​

So I was tasked to do something that probably should be handled by a 1 of the 1000 people we have in our systems team that have the skills and experience to handle this. but still, here we are. Not sour about it.

We have a specific in house built system to the company, this system for this purpose houses images of items. The system has a export function so I can get them to spit out into excel. Every time you export something to excel it opens a new version of excel, I need to copy these images into another workbook. I don't know from a technical point of view why this happens but if you copy these images into another file as cntrl C and cntrl V the image disappears. If you copy and paste special as an image it treats all the images selected as 1 big image. This would be fine, but the images are all different sizes so I would need to adjust the rows for each image so they didn't go out of sync with the information. There's too many images for this.

So I wrote a macro to do it, open my file in the 'current' version of excel the images are in and copy the images over 1 by 1 rather than doing it manually. Brilliant! I was chuffed with myself for managing this. The code probably isn't optimal because I've done it and frankly, I'm not that great with VBA.

Code below

Sub GetpPics()

'----------DIMS--------------

Dim xwb As Workbook
Dim pics As Shapes
Dim filestring As String
Dim TWB As Workbook
Dim counter As Integer

'---------Set workbooks-----------

Application.ScreenUpdating = False

filestring = "T:\Common\Kieran\Selection template.xlsx" 'update with location of file to transfer to

Set TWB = ThisWorkbook
Set xwb = Workbooks.Open(filestring)

'-----------Copy Images-------------

counter = TWB.Worksheets(1).Shapes.Count

For S = 1 To counter
For R = 1 To counter

TWB.Sheets(1).Shapes("picture " & R).CopyPicture
xwb.Sheets(1).Cells(S   1, 4).PasteSpecial

S = S   1
Next R
Next S

MsgBox (counter & " images have been copied")

End Sub

As you can see the idea is simple, it counts the number of images as a total and then cycles through each one copying. My problem is that I didnt think about how to handle where an image is missing. The code doesnt appreciate that one might be missing and skipping a row as it would it the extract. So the image would then be placed next to an incorrect item that it doesn't relate to.

So basically I want the code to, if possible, mimic the export in the way it copies the images over. I'm completely at a loss on how to handle this really, so any ideas would be great.

This is going to have to be a regular job for me, despite my protests, approximately 40 times a season. And I need to get as much of it as automated as possible to eliminate as much time as I can.

I'm open to completely binning this off and trying to tackle it a different way if anyone can think of one.

Any help would be appreciated

Edit : I may have an idea, could I somehow possibly rename the shapes to correspond to the row they're placed on? then with an error I could just skip that number? so if image 2 would be placed on Row 2 on the other file, then if image 3 was missing it would skip and then resume on image 4?