Extract parent folder name from file path

nath12345

New Member
Joined
Oct 8, 2013
Messages
1
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>
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
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:
Code:
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
 
Upvote 0

Forum statistics

Threads
1,212,927
Messages
6,110,730
Members
448,294
Latest member
jmjmjmjmjmjm

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top