PDA

View Full Version : How to turn any URL input into file name



Cinegue
02-06-2011, 03:59 PM
How to turn any URL input into filename using VBA or VBscript?

example would be:

input before:
http any url with domainname1 dot com /myfile.rar
http any url with domainname2 dot com /myfile7.rar
http any url with domainname3 dot com /myfilenameislongernow.rar
http any url with domainname4 dot com /myf.zip

output after:
myfile.rar
myfile7.rar
myfilenameislongernow.rar
myf.zip

Blade Hunter
02-06-2011, 05:01 PM
replace(right(replace(activecell.Text,"/",application.WorksheetFunction.Rept("/",255)),255),"/","")

Change Activecell.Text to a cell reference if you prefer.

Cinegue
02-06-2011, 05:10 PM
replace(right(replace(activecell.Text,"/",application.WorksheetFunction.Rept("/",255)),255),"/","")
Change Activecell.Text to a cell reference if you prefer.

Thank you so much, it works.

Can you explain how did you find the second forward slash?

Also how would you do this in also vbscript? Can you convert this vba?

Kenneth Hobs
02-06-2011, 05:21 PM
Here are some other methods.

Sub test()
Dim s As String
s = "http any url with domainname1 dot com /myfile.rar"
MsgBox Mid(s, InStrRev(s, "/") + 1, Len(s) - InStrRev(s, "/") + 1)
End Sub

Sub test2()
Dim s As String
s = "http any url with domainname1 dot com /myfile.rar"
MsgBox Split(s, "/")(UBound(Split(s, "/")))
End Sub

Cinegue
02-06-2011, 05:26 PM
Here are some other methods.

Sub test()
Dim s As String
s = "http any url with domainname1 dot com /myfile.rar"
MsgBox Mid(s, InStrRev(s, "/") + 1, Len(s) - InStrRev(s, "/") + 1)
End Sub
Sub test2()
Dim s As String
s = "http any url with domainname1 dot com /myfile.rar"
MsgBox Split(s, "/")(UBound(Split(s, "/")))
End Sub

Both ways work perfectly. This is exactly what I needed.

Thank you.

Blade Hunter
02-06-2011, 05:30 PM
Thank you so much, it works.

Can you explain how did you find the second forward slash?

Also how would you do this in also vbscript? Can you convert this vba?

I don't find the second as such, I find the last.

It replaces all "/" with 255 of the same char

Then it takes the right 255 chars of that

We are pretty much guaranteed that it will be the filename with a load of "/" at the start

Then we replace all "/" with nothing leaving just the filename.

And no, it can't be used as is in VBScript because of this part:

application.WorksheetFunction.Rept("/",255)

You would need to put it in a variable and loop it 8 times (2 to the power of 8 is 256) replacing "/" with "//".

Cheers

Dan

Blade Hunter
02-06-2011, 05:35 PM
Here are some other methods.

Sub test()
Dim s As String
s = "http any url with domainname1 dot com /myfile.rar"
MsgBox Mid(s, InStrRev(s, "/") + 1, Len(s) - InStrRev(s, "/") + 1)
End Sub

Sub test2()
Dim s As String
s = "http any url with domainname1 dot com /myfile.rar"
MsgBox Split(s, "/")(UBound(Split(s, "/")))
End Sub

I don't understand why I ALWAYS forget about Split when doing these things. Nice solution :).

Cinegue
02-06-2011, 05:35 PM
I don't find the second as such, I find the last.

It replaces all "/" with 255 of the same char

Then it takes the right 255 chars of that

We are pretty much guaranteed that it will be the filename with a load of "/" at the start

Then we replace all "/" with nothing leaving just the filename.

And no, it can't be used as is in VBScript because of this part:

application.WorksheetFunction.Rept("/",255) You would need to put it in a variable and loop it 8 times replacing "/" with "//".

Cheers

Dan

Oh I get it now, thank you for explaining it to me. Very helpful.
No need for VBscript anymre because Kenneth Hobs explained it in two different ways. I got both ways now in VBA and VBscript. Thank you all for your help.