How to turn any URL input into file name

[Cinegue]

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]

[vba]replace(right(replace(activecell.Text,"/",application.WorksheetFunction.Rept("/",255)),255),"/","")[/vba]

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

[Cinegue]

[vba]replace(right(replace(activecell.Text,"/",application.WorksheetFunction.Rept("/",255)),255),"/","")[/vba]
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]

Here are some other methods.

[VBA]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[/VBA]

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

[Cinegue]

Here are some other methods.

[vba]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[/vba]
[vba]Sub test2()
Dim s As String
s = "http any url with domainname1 dot com /myfile.rar"
MsgBox Split(s, "/")(UBound(Split(s, "/")))
End Sub[/vba]

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

Thank you.

[Blade Hunter]

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]

Here are some other methods.

[vba]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[/vba]

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

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

[Cinegue]

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.