vba in excel

[ProPlus]

getopenfile name retrieves C:\folder1\test.txt, I just want to retrieve test.txt. How do i do this?

[atomicrex]

ProPlus said:

getopenfile name retrieves C:folder1 est.txt, I just want to retrieve test.txt. How do i do this?

const csPath as string=C:folder1\test.txt
const csFind as string="\"


Public Sub TestPath()
Dim sFileName1 As String, sFileName2 As String
sFileName1 = GetFileName1(csPath)
sFileName2 = GetFileName2(csPath)
End Sub

Public Function GetFileName1(sPath As String) As String
Dim lOPos As Long, lNPos As Long

lNPos = 0
Do
lOPos = lNPos + 1
lNPos = InStr(lOPos, sPath, csFind)
If lNPos = 0 Then GetFileName1 = Mid(sPath, lOPos)
Loop Until lNPos = 0
End Function

Public Function GetFileName2(sPath As String) As String
GetFileName2 = Mid(sPath, InStrRev(sPath, csFind) + 1)
End Function

Edited by atomicrex on Sunday 10th September 08:45

[ProPlus]

atomicrex said:

ProPlus said:

getopenfile name retrieves C:folder1 est.txt, I just want to retrieve test.txt. How do i do this?

const csPath as string=C:folder1 est.txt
const csFind as string=""


Public Sub TestPath()
Dim sFileName1 As String, sFileName2 As String
sFileName1 = GetFileName1(csPath)
sFileName2 = GetFileName2(csPath)
End Sub

Public Function GetFileName1(sPath As String) As String
Dim lOPos As Long, lNPos As Long

lNPos = 0
Do
lOPos = lNPos + 1
lNPos = InStr(lOPos, sPath, csFind)
If lNPos = 0 Then GetFileName1 = Mid(sPath, lOPos)
Loop Until lNPos = 0
End Function

Public Function GetFileName2(sPath As String) As String
GetFileName2 = Mid(sPath, InStrRev(sPath, csFind) + 1)
End Function

Edited by atomicrex on Sunday 10th September 08:45

much appreciated.... was driving me crazy, in JAVA its so easy but using this crap programming language i am so limited..

[roadsweeper]

ProPlus, I emailed this solution to you yesterday (except using InStrRev) as PH wasn't allowing me to post it. Did you not receive it?

[ProPlus]

roadsweeper said:

ProPlus, I emailed this solution to you yesterday (except using InStrRev) as PH wasn't allowing me to post it. Did you not receive it?

in the end i used:

FName = Mid(FName, InStrRev(FName, csFind) + 1)

where csfind is
and fname is complete file path obtained via getopenfilename

and it works fine....

cheers.

[AtomicRex]

Its a bugger getting these "\" to show!

I gave you both....as it depends on the version of VBA you are using. I have to program in the cutting edge of office 97! And in that they have only just realised that water flows down hill....so asking for InstRev is way too much to ask for!

Plus if you need to get something else out of the string its always useful to keep the code using the loop.

Glad it helped

[roadsweeper]

AtomicRex said:

Its a bugger getting these "" to show!

Aye, I gave up pretty quickly!

AtomicRex said:

I have to program in the cutting edge of office 97!

My God man, you must have the patience of a saint!