Create image url

[JohnnyDrama]

Hi guys,

hope everyone is doing fine these days!

I've got a questions regarding creating image URLS in bulk.
I work at a small company who sell mainly to only key accounts like Amazon.
We have over 15.000 different SKU's so you can imagine how many images we have (roughly 150k).

All images are named by barcode, following up with _1 _2 for lifestyle images;

4037571322237.jpg

4037571322237_1.jpg

4037571322237_2.jpg

4037571322237_3.jpg

4037571322237_4.jpg

Now we host all the images on a FTP which creates a link.
I am fine with creating links, merging cells and so on but the problem is this (hopefully I explain this right).

When we have to fill out the datasheet for our customer they request all the images per SKU in 1 row in Excel.
See below;

Barcode	SKU Code	Product	Productimage	Lifestyle Image	Lifestyle Image 2
4037571320936​	ABCDE123	SAMPLE PRODUCT	http://link.com/4037571320936.jpg	http://link.com/4037571320936_1.jpg	http://link.com/4037571320936_2.jpg
4037571386215​	ABCDE124	SAMPLE PRODUCT 2	http://link.com/4037571386125.jpg	http://link.com/4037571386125_1.jpg	http://link.com/4037571386125_2.jpg

Now I have to do this in bulk for all 15.000 images.
I can get all the file names in Excel, no problem, but then all the filenames are underneath eachother.
Each unique barcode has to be on 1 row in Excel.

So basically, how do I turn this;

4037571320936.jpg

4037571320936_1.jpg

4037571320936_2.jpg

4037571386215.jpg

4037571386215_1.jpg

4037571386215_2.jpg

4037571386215_3.jpg

4037571322237.jpg

4037571322237_1.jpg

4037571322237_2.jpg

4037571322237_3.jpg

4037571322237_4.jpg

Into this; (ignore the SKU code and Product, but that's how the data sheets usually look here..)

Barcode	SKU Code	Product	Productimage	Lifestyle Image	Lifestyle Image 2	Lifestyle Image 3
4037571320936​	ABCDE123	SAMPLE PRODUCT	http://link.com/4037571320936.jpg	http://link.com/4037571320936_1.jpg	http://link.com/4037571320936_2.jpg
4037571386215​	ABCDE124	SAMPLE PRODUCT 2	http://link.com/4037571386125.jpg	http://link.com/4037571386125_1.jpg	http://link.com/4037571386125_2.jpg
4037571322237​	ABCDE125	SAMPLE PRODUCT	http://link.com/4037571322237.jpg	http://link.com/4037571322237_1.jpg	http://link.com/4037571322237_2.jpg	http://link.com/4037571322237_3.jpg

Many thanks.

Cheers.

JohnnyD.

 

[offthelip]

try this code:

Sub test()
lastcol = 1
Path = "http://link.com/"
With Worksheets("Data")
lastrow = .Cells(Rows.Count, "A").End(xlUp).Row
inarr = .Range(.Cells(1, 1), .Cells(lastrow, lastcol))
End With

Worksheets("Sheet1").Select
Barcode = 0
oldpcode = ""
colno = 4
indi = 1
For i = 2 To lastrow
  ' assuming the product code is always 13 digits
  pcode = Left(inarr(i, 1), 13)
If pcode = oldpcode Then
   ' write another column out

' create link
  With ActiveSheet
    .Hyperlinks.Add Anchor:=.Range(Cells(indi, colno), Cells(indi, colno)), _
                                Address:=Path & inarr(i, 1), _
                                TextToDisplay:=inarr(i, 1)
  End With
  colno = colno + 1
Else
  colno = 4
  indi = indi + 1
' create link
  With ActiveSheet
    .Hyperlinks.Add Anchor:=.Range(Cells(indi, colno), Cells(indi, colno)), _
                                Address:=Path & inarr(i, 1), _
                                TextToDisplay:=inarr(i, 1)
  End With
  colno = colno + 1
End If

oldpcode = pcode

Next i


End Sub

 

[JohnnyDrama]

Hi OfftheLip,

I tried this code but I gives me an error 9: Subscript out of Range.
I also got the error message it needs an End Sub. I type End Sub below Next i to hopefully fix this error.

I am a totale newbie when it comes to VBA!

I appreciate all your help.

Cheers.

 

[JohnnyDrama]

Ok I've got the script working! It's awesome!

Just one more question;

In my line of business the product image (the one thats first on a webshop) is always <BARCODE.JPG> every other image follows with _1 _2 _3 and so on.
I copy paste the barcode names in Excel and the product image (<BARCODE.JPG) is the last image.

5407005140000_1.jpg

5407005140000_2.jpg

5407005140000_3.jpg

5407005140000_4.jpg

5407005140000_5.jpg

5407005140000.jpg

This means, whenever I use the script the product image ends up last.

5407005140000_1.jpg

5407005140000_2.jpg

5407005140000_3.jpg

5407005140000_4.jpg

5407005140000_5.jpg

5407005140000.jpg

All of our customer request the productimage first.
So maybe I am asking a stupid question now... but how do I get the product image first without changing the name of the files? So it looks like this;

5407005140000.jpg

5407005140000_1.jpg

5407005140000_2.jpg

5407005140000_3.jpg

5407005140000_4.jpg

5407005140000_5.jpg

The whole reason behind this, each image has a specific place when renaming.
Product image = always <BARCODE.JPG>
Product in Packaging = always the last image. So in this case _5 is the product with packaging.

Again, many many thanks! Appreciate it much

 

[JohnnyDrama]

Okay, this is strange.
I work on a Mac and when I sort A to Z the result is different then when I sort A to Z on a windows pc.

When I sort from A to Z on windows the result is fine.
So for now I can sort them on my windows PC. But if you do have a solution (I love mac's.. sorry) that would be great!

Cheers

 

[offthelip]

It looks as though you have solved all the problems with the code I wrote. I can't help you with the problem of sorting on Macs since I use a PC. I suggest you raise another query about that because it is totally new problem and it might be of interest to others