Extract parent folder name from file path

[nath12345]

i have inherited an access tool that imports a file into its database, makes a series of changes, then in its final stages exports the file back into the folder it originally came from with changes. this all works fine.

however, what i want it to do, is export the file, but rename it to reflect parent folder.

For example, V:HQ\red\yellow\blue\0000.txt

what i want is for the file to be named blue in this instance (will change, path is variable so cannot hard code it).</SPAN>


What i have so far:</SPAN>


Dim sL2ImportFileName As String, sNewName As String, sL2FilePath As String
Dim iStartPos As Integer, iSlashPos As Integer

sL2FilePath = DLookup("FilePath", "[tbl L2 Import Details]", "[File Type] = 'L2D'")</SPAN>
iPos = Len(sL2FilePath)

While Mid(sL2FilePath, iPos, 1) <> "\"
iPos = iPos - 1
Wend


If Dir(sL2FilePath & "l2.xls") <> "" Then Kill sL2FilePath & ".l2.xls"

sNewName = Left(sL2FilePath, iPos) & "l2d.txt"
DoCmd.TransferText acExportDelim, "xls export", "colour", sNewName, True


Name sNewName As sL2FilePath & "l2.xls" </SPAN>


As i say currently this is exporting the file named 0000. that works fine. What I want, is for the file to take the name of the parent folder the file is contained in. </SPAN></SPAN>

The File path is contained in the Access table referenced in the D look up. It uses this reference to find the location where it needs to write back to. </SPAN></SPAN>

I am using Excel and Access 2003</SPAN></SPAN>

 

[Rosen]

While I am not familiar with the Access, if the line sNewName = Left(sL2FilePath, iPos) & "l2d.txt" is generating your V:HQ\red\yellow\blue\0000.txt and you want it to be V:HQ\red\yellow\blue\blue.txt then you should simply be able to run sNewName as the parameter for this following function to get your correct file name:

Private Function GetNewName(ByVal sName As String) As String
    Dim n1Pos As Long, n2Pos As String
    Dim strPath As String, strFileName As String, strExtention As String
        
    n1Pos = VBA.Strings.InStrRev(sName, "\")
    n2Pos = VBA.Strings.InStrRev(sName, "\", n1Pos - 1)
    
    strPath = VBA.Strings.Left(sName, n1Pos)
    strFileName = VBA.Strings.Mid(sName, n2Pos + 1, n1Pos - n2Pos - 1)
    If VBA.Strings.InStrRev(sName, ".") > n1Pos Then strExtention = VBA.Strings.Right(sName, VBA.Strings.Len(sName) - VBA.Strings.InStrRev(sName, ".") + 1)
    
    GetNewName = strPath & strFileName & strExtention
End Function