get filename from url

[patel]

I have an excelsheet with many urls in column A like this
http://beeldbank.ahold.com.kpnis.nl/getimage/AHI_434d50303030363139?dRevLabel=1&Rendition=200x200_JPG
I don't need download the image but I would get the filename.

------------------------
Excel 2010 - Windows 8

[snb]

Sub M_snb()
With CreateObject("MSXML2.XMLHTTP")
.Open "Get", "http://beeldbank.ahold.com.kpnis.nl/getimage/AHI_434d50303030363139?dRevLabel=1&Rendition=200x200_JPG", False
.send

Do While .ReadyState <> 4
DoEvents
Loop

Open "G:\OF\patelwebtest.jpg" For Binary As #1
Put #1, , .responsebody
Close #1
End With
End Sub

[patel]

thanks snb, but I said "I don't need download the image but I would get the filename", i.e gtin8711600639027_2d1 (200x200_JPG).jpg

[snb]

Sub M_snb()
With CreateObject("MSXML2.XMLHTTP")
.Open "Get", "http://beeldbank.ahold.com.kpnis.nl/getimage/AHI_434d50303030363139?dRevLabel=1&Rendition=200x200_JPG", False
.send
Do While .ReadyState <> 4
DoEvents
Loop

MsgBox Split(Filter(Split(.getAllResponseHeaders, ".jpg"), "filename=")(0), "filename=""")(1) & ".jpg"
End With
End Sub

[Jan Karel Pieterse]

Using a normal formula in a cell:

=MID(A1;FIND("Rendition";A1)+LEN("Rendition")+1;LEN(A1))

Or in Dutch Excel:

=MIDDEN(A1;VINDEN.ALLES("Rendition";A1)+LENGTE("Rendition")+1;LENGTE(A1))

[patel]

thanks snb, problem solved, you are a master.

Jan Karel Pieterse the filename in not included in the url

[Jan Karel Pieterse]

Ah, so I see.

[snb]

more elegant:


Sub M_snb()
With CreateObject("MSXML2.XMLHTTP")
.Open "Get", "http://beeldbank.ahold.com.kpnis.nl/getimage/AHI_434d50303030363139?dRevLabel=1&Rendition=200x200_JPG", False
.send

Do While .ReadyState <> 4
DoEvents
Loop

MsgBox Split(replace(.getResponseHeader("Content-Disposition"),chr(34),""), "filename=")(1)
End With
End Sub