I have a variable that contains a Path and filename.. is there a way to
strip out just the file name using vba code...

for example:

strPathFile = c:\windows\test.png

i need

strFileName = test.png

I need somthing to start at the right most character and then move left
till it finds "\"...

Thanks,

Dave

If you're using xl2k or higher, you can use InstrRev to get the position of the
last \.

dim LastSlash as long
dim strFileName as string
strPathFile = c:\windows\test.png
lastslash = instrrev(strpathfile, "\")
if lastslash 0 then
msgbox mid(strpathfile, lastslash + 1)
end if

If you're using xl97 or below, you can just loop from the far right back to the
beginning.

dim LastSlash as long
dim iCtr as long
dim strFileName as string
strPathFile = c:\windows\test.png
lastslash = 0
for ictr = len(strpathfile) to 1 step -1
if mid(strpathfile, ictr,1) = "\" then
lastslash = ictr
exit for
end if
next ictr
if lastslash 0 then
msgbox mid(strpathfile, lastslash + 1)
end if

(This will work for xl2k+, too.)

wrote:

I have a variable that contains a Path and filename.. is there a way to
strip out just the file name using vba code...

for example:

strPathFile = c:\windows\test.png

i need

strFileName = test.png

I need somthing to start at the right most character and then move left
till it finds "\"...

Thanks,

Dave

--

Dave Peterson

Assuming the text is in cell B2, here is a formula from one of John
Walkenbach's books that will do it. You don't need to use VBA.

=RIGHT(B2,LEN(B2)-FIND("*",SUBSTITUTE(B2,"\","*",LEN(B2)-LEN(SUBSTITUTE(B2,"\","")))))

Basically, it finds out how many "\" there are, finds the location of
the last one, and then takes the characters to the right of it.


Mark


wrote:
I have a variable that contains a Path and filename.. is there a way to
strip out just the file name using vba code...

for example:

strPathFile = c:\windows\test.png

i need

strFileName = test.png

I need somthing to start at the right most character and then move left
till it finds "\"...

Thanks,

Dave

Try:

Function getThatName(s As String) As String
s2 = Split(s, "\")
getThatName = s2(UBound(s2))
End Function

Will get the filename. Does not matter how deeply the path is nested.
--
Gary's Student


" wrote:

I have a variable that contains a Path and filename.. is there a way to
strip out just the file name using vba code...

for example:

strPathFile = c:\windows\test.png

i need

strFileName = test.png

I need somthing to start at the right most character and then move left
till it finds "\"...

Thanks,

Dave

Very nice.

Mark


Gary''s Student wrote:
Try:

Function getThatName(s As String) As String
s2 = Split(s, "\")
getThatName = s2(UBound(s2))
End Function

Will get the filename. Does not matter how deeply the path is nested.
--
Gary's Student


" wrote:

I have a variable that contains a Path and filename.. is there a way to
strip out just the file name using vba code...

for example:

strPathFile = c:\windows\test.png

i need

strFileName = test.png

I need somthing to start at the right most character and then move left
till it finds "\"...

Thanks,

Dave

Everyone thanks for the help.. Im hoping before to long I can learn
VBA and start to understand why things work the way they do.. :-)

So far my Sub is working with one exception and I can't figure it out.
My sFileName variable is returning a value of "57", but in the message
box it shows the correct file name... any thoughts?

Here is the code...
__________________________________________________ _______________
Private Sub cboUpdate_Click()

Dim hyplink As String
Dim retval As Long
Dim sFileName As String


'set hyplink value as the forms text box.
hyplink = Forms!frmGuides!hyperlink1

'get the file name only, start to the right and go until the
backslash
sFileName = InStrRev(hyplink, "\")
If sFileName 0 Then

'msgbox to show the right file name, which works....
MsgBox Mid(hyplink, sFileName + 1)
End If

'set the file Dest
retval = CopyFile(hyplink, "c:\Route\" & sFileName, 1)

Debug.Print hyplink
Debug.Print sFileName


If retval = 0 Then ' failure
MsgBox "Copy failed --" & hyplink & ".", vbCritical, "UPS DMS"

Else ' success
MsgBox "Copy succeeded."
End If

End Sub
__________________________________________________ __________________


Thanks!

Dave






Mark Driscol wrote:
Very nice.

Mark


Gary''s Student wrote:
Try:

Function getThatName(s As String) As String
s2 = Split(s, "\")
getThatName = s2(UBound(s2))
End Function

Will get the filename. Does not matter how deeply the path is nested.
--
Gary's Student


" wrote:

I have a variable that contains a Path and filename.. is there a way to
strip out just the file name using vba code...

for example:

strPathFile = c:\windows\test.png

i need

strFileName = test.png

I need somthing to start at the right most character and then move left
till it finds "\"...

Thanks,

Dave

This might get you further:

(Untested)

Option Explicit
Private Sub cboUpdate_Click()

Dim hyplink As String
Dim retval As Long
Dim sFileName As String
Dim SlashPos As Long

'set hyplink value as the forms text box.
hyplink = frmGuides.hyperlink1

'get the file name only, start to the right and go until the backslash
SlashPos = InStrRev(hyplink, "\")
If SlashPos 0 Then
sFileName = Mid(hyplink, sFileName + 1)
End If

'set the file Dest
retval = FileCopy(hyplink, "c:\Route\" & sFileName)

Debug.Print hyplink
Debug.Print sFileName

If retval = 0 Then ' failure
MsgBox "Copy failed --" & hyplink & ".", vbCritical, "UPS DMS"

Else ' success
MsgBox "Copy succeeded."
End If

End Sub

Notice that I used VBA's FileCopy--not FSO's CopyFile.

But if you want...

Option Explicit
Private Sub cboUpdate_Click()

Dim FSO As Object
Dim hyplink As String
Dim retval As Long
Dim sFileName As String
Dim SlashPos As Long

Set FSO = CreateObject("Scripting.FileSystemobject")

'set hyplink value as the forms text box.
hyplink = frmGuides.hyperlink1

'get the file name only, start to the right and go until the backslash
SlashPos = InStrRev(hyplink, "\")
If SlashPos 0 Then
'msgbox to show the right file name, which works....
sFileName = Mid(hyplink, sFileName + 1)
End If

'set the file Dest
retval = FSO.CopyFile(Source:=hyplink, Destination:="c:\Route\" & sFileName,
_
overwrite:=True)

Debug.Print hyplink
Debug.Print sFileName

If retval = 0 Then ' failure
MsgBox "Copy failed --" & hyplink & ".", vbCritical, "UPS DMS"

Else ' success
MsgBox "Copy succeeded."
End If

End Sub

And depending on what's in that hyperlink address, you might have to strip more
stuff off (does it start with "File://"????).


wrote:

Everyone thanks for the help.. Im hoping before to long I can learn
VBA and start to understand why things work the way they do.. :-)

So far my Sub is working with one exception and I can't figure it out.
My sFileName variable is returning a value of "57", but in the message
box it shows the correct file name... any thoughts?

Here is the code...
__________________________________________________ _______________
Private Sub cboUpdate_Click()

Dim hyplink As String
Dim retval As Long
Dim sFileName As String

'set hyplink value as the forms text box.
hyplink = Forms!frmGuides!hyperlink1

'get the file name only, start to the right and go until the
backslash
sFileName = InStrRev(hyplink, "\")
If sFileName 0 Then

'msgbox to show the right file name, which works....
MsgBox Mid(hyplink, sFileName + 1)
End If

'set the file Dest
retval = CopyFile(hyplink, "c:\Route\" & sFileName, 1)

Debug.Print hyplink
Debug.Print sFileName

If retval = 0 Then ' failure
MsgBox "Copy failed --" & hyplink & ".", vbCritical, "UPS DMS"

Else ' success
MsgBox "Copy succeeded."
End If

End Sub
__________________________________________________ __________________

Thanks!

Dave

Mark Driscol wrote:
Very nice.

Mark


Gary''s Student wrote:
Try:

Function getThatName(s As String) As String
s2 = Split(s, "\")
getThatName = s2(UBound(s2))
End Function

Will get the filename. Does not matter how deeply the path is nested.
--
Gary's Student


" wrote:

I have a variable that contains a Path and filename.. is there a way to
strip out just the file name using vba code...

for example:

strPathFile = c:\windows\test.png

i need

strFileName = test.png

I need somthing to start at the right most character and then move left
till it finds "\"...

Thanks,

Dave



--

Dave Peterson

Dave,

Thanks for the reply, Im a bit confused though.. My file copy does
work, its the sFilename that is holding the incorrrect value. In the
msgbox that pops up, it does show the correct value of the file name
being copied, on the othern had, but "immediate window" when I do
debug.print sFileName it prints "57" to the immediate window and in the
dest folder it names the file name "57". I dont understand why the
msgbox shows the correct filename, but the varaiable holds the value of
"57".

Thanks,

Dave



Dave Peterson wrote:
This might get you further:

(Untested)

Option Explicit
Private Sub cboUpdate_Click()

Dim hyplink As String
Dim retval As Long
Dim sFileName As String
Dim SlashPos As Long

'set hyplink value as the forms text box.
hyplink = frmGuides.hyperlink1

'get the file name only, start to the right and go until the backslash
SlashPos = InStrRev(hyplink, "\")
If SlashPos 0 Then
sFileName = Mid(hyplink, sFileName + 1)
End If

'set the file Dest
retval = FileCopy(hyplink, "c:\Route\" & sFileName)

Debug.Print hyplink
Debug.Print sFileName

If retval = 0 Then ' failure
MsgBox "Copy failed --" & hyplink & ".", vbCritical, "UPS DMS"

Else ' success
MsgBox "Copy succeeded."
End If

End Sub

Notice that I used VBA's FileCopy--not FSO's CopyFile.

But if you want...

Option Explicit
Private Sub cboUpdate_Click()

Dim FSO As Object
Dim hyplink As String
Dim retval As Long
Dim sFileName As String
Dim SlashPos As Long

Set FSO = CreateObject("Scripting.FileSystemobject")

'set hyplink value as the forms text box.
hyplink = frmGuides.hyperlink1

'get the file name only, start to the right and go until the backslash
SlashPos = InStrRev(hyplink, "\")
If SlashPos 0 Then
'msgbox to show the right file name, which works....
sFileName = Mid(hyplink, sFileName + 1)
End If

'set the file Dest
retval = FSO.CopyFile(Source:=hyplink, Destination:="c:\Route\" & sFileName,
_
overwrite:=True)

Debug.Print hyplink
Debug.Print sFileName

If retval = 0 Then ' failure
MsgBox "Copy failed --" & hyplink & ".", vbCritical, "UPS DMS"

Else ' success
MsgBox "Copy succeeded."
End If

End Sub

And depending on what's in that hyperlink address, you might have to strip more
stuff off (does it start with "File://"????).


wrote:

Everyone thanks for the help.. Im hoping before to long I can learn
VBA and start to understand why things work the way they do.. :-)

So far my Sub is working with one exception and I can't figure it out.
My sFileName variable is returning a value of "57", but in the message
box it shows the correct file name... any thoughts?

Here is the code...
__________________________________________________ _______________
Private Sub cboUpdate_Click()

Dim hyplink As String
Dim retval As Long
Dim sFileName As String

'set hyplink value as the forms text box.
hyplink = Forms!frmGuides!hyperlink1

'get the file name only, start to the right and go until the
backslash
sFileName = InStrRev(hyplink, "\")
If sFileName 0 Then

'msgbox to show the right file name, which works....
MsgBox Mid(hyplink, sFileName + 1)
End If

'set the file Dest
retval = CopyFile(hyplink, "c:\Route\" & sFileName, 1)

Debug.Print hyplink
Debug.Print sFileName

If retval = 0 Then ' failure
MsgBox "Copy failed --" & hyplink & ".", vbCritical, "UPS DMS"

Else ' success
MsgBox "Copy succeeded."
End If

End Sub
__________________________________________________ __________________

Thanks!

Dave

Mark Driscol wrote:
Very nice.

Mark


Gary''s Student wrote:
Try:

Function getThatName(s As String) As String
s2 = Split(s, "\")
getThatName = s2(UBound(s2))
End Function

Will get the filename. Does not matter how deeply the path is nested.
--
Gary's Student


" wrote:

I have a variable that contains a Path and filename.. is there a way to
strip out just the file name using vba code...

for example:

strPathFile = c:\windows\test.png

i need

strFileName = test.png

I need somthing to start at the right most character and then move left
till it finds "\"...

Thanks,

Dave



--

Dave Peterson