View Full Version : [SOLVED:] get filename from url
patel
11-03-2013, 09:24 AM
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
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
11-03-2013, 11:18 AM
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
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
11-03-2013, 11:10 PM
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
11-03-2013, 11:34 PM
thanks snb, problem solved, you are a master.
Jan Karel Pieterse the filename in not included in the url
Jan Karel Pieterse
11-04-2013, 01:11 AM
Ah, so I see.
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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.