How do get the folder name using FilesystemObject

[renovator]

Hu,
My program adds the file names in all the sub folders of a folder. I want to add the subfolder path
eg file name, folder name

If the full path is
C:\red\blue.txt

I want to insert blue.txt, red.
I thought is would be the parentfolder but it shows the full path

thanks

 

[MrIfOnly]

Re: How do get the folder name using FilesystemObejct

Please post your code.

CJ

 

[renovator]

Re: How do get the folder name using FilesystemObejct

Please post your code.

CJ

[COLOR=#465584][FONT=Courier]' Input Variables:[/FONT][/COLOR]
[COLOR=#465584][FONT=Courier]' ~~~~~~~~~~~~~~~~[/FONT][/COLOR]
[COLOR=#465584][FONT=Courier]' sPath     : Full path of folder to examine with trailing \[/FONT][/COLOR]
[COLOR=#465584][FONT=Courier]' sFilter   : specific file extension to limmit search to, leave blank to list all files[/FONT][/COLOR]
[COLOR=#465584][FONT=Courier]Function fGetDirFileInfo(sPath As String, Optional sFilter As String = "*")[/FONT][/COLOR]
[COLOR=#465584][FONT=Courier]On Error GoTo Error_Handler[/FONT][/COLOR]
[COLOR=#465584][FONT=Courier]    Dim db              As DAO.Database[/FONT][/COLOR]
[COLOR=#465584][FONT=Courier]    Dim rs              As DAO.Recordset[/FONT][/COLOR]
[COLOR=#465584][FONT=Courier]    Dim sFile           As String[/FONT][/COLOR]
[COLOR=#465584][FONT=Courier]    Dim fso             As Object[/FONT][/COLOR]
[COLOR=#465584][FONT=Courier]    Dim f               As Object[/FONT][/COLOR]

[COLOR=#465584][FONT=Courier]    sPath = TrailingSlash(sPath)[/FONT][/COLOR]
[COLOR=#465584][FONT=Courier]    Set db = CurrentDb()[/FONT][/COLOR]
[COLOR=#465584][FONT=Courier]    db.Execute "Delete * FROM tblFiles", dbFailOnError    'Wipe previous entries in the table so we only have the most recent request's data to work with[/FONT][/COLOR]
[COLOR=#465584][FONT=Courier]    Set rs = db.OpenRecordset("SELECT * FROM tblFiles")[/FONT][/COLOR]
[COLOR=#465584][FONT=Courier]    Set fso = CreateObject("Scripting.FileSystemObject")    'Ref: http://msdn.microsoft.com/en-us/library/ea5ht6ax%28v=vs.84%29.aspx[/FONT][/COLOR]
[COLOR=#465584][FONT=Courier]                                                            'http://ss64.com/vb/filesystemobject.html[/FONT][/COLOR]

[COLOR=#465584][FONT=Courier]    sFile = Dir(sPath & "*." & sFilter)[/FONT][/COLOR]
[COLOR=#465584][FONT=Courier]    Do While sFile <> vbNullString[/FONT][/COLOR]
[COLOR=#465584][FONT=Courier]        If sFile <> "." And sFile <> ".." Then[/FONT][/COLOR]
[COLOR=#465584][FONT=Courier]            Set f = fso.GetFile(sPath & "\" & sFile)[/FONT][/COLOR]
[COLOR=#465584][FONT=Courier]            With rs[/FONT][/COLOR]
[COLOR=#465584][FONT=Courier]                .AddNew[/FONT][/COLOR]
[COLOR=#465584][FONT=Courier]                rs![FileName] = sFile   'Could also use f.Name if we wanted to[/FONT][/COLOR]
[COLOR=#465584][FONT=Courier]                rs![FileSize] = f.Size    'We could just as easily use FileLen(sFile)[/FONT][/COLOR]
[COLOR=#465584][FONT=Courier]                rs![FileDateCreated] = f.DateCreated[/FONT][/COLOR]
[COLOR=#465584][FONT=Courier]                rs![FileDateLastModified] = f.DateLastModified[/FONT][/COLOR]
[COLOR=#465584][FONT=Courier]                rs![FileDateLastAccessed] = f.DateLastAccessed[/FONT][/COLOR]
[COLOR=#465584][FONT=Courier]                rs![FileType] = f.Type[/FONT][/COLOR]
[COLOR=#465584][FONT=Courier]                'paul wakelam[/FONT][/COLOR]
[COLOR=#465584][FONT=Courier]                rs![FileAttributes] = f.Attributes[/FONT][/COLOR]
[COLOR=#465584][FONT=Courier]                rs![FileShortDirectory] = f.Folder  '???????[/FONT][/COLOR]
[COLOR=#465584][FONT=Courier]                rs![FileHoldenDate] = Date[/FONT][/COLOR]
[COLOR=#465584][FONT=Courier]                .Update[/FONT][/COLOR]
[COLOR=#465584][FONT=Courier]            End With[/FONT][/COLOR]
[COLOR=#465584][FONT=Courier]        End If[/FONT][/COLOR]
[COLOR=#465584][FONT=Courier]        sFile = Dir    'Loop through the next file that was found[/FONT][/COLOR]
[COLOR=#465584][FONT=Courier]    Loop[/FONT][/COLOR]

[COLOR=#465584][FONT=Courier]Error_Handler_Exit:[/FONT][/COLOR]
[COLOR=#465584][FONT=Courier]    On Error Resume Next[/FONT][/COLOR]
[COLOR=#465584][FONT=Courier]    Set f = Nothing[/FONT][/COLOR]
[COLOR=#465584][FONT=Courier]    Set fso = Nothing[/FONT][/COLOR]
[COLOR=#465584][FONT=Courier]    rs.Close[/FONT][/COLOR]
[COLOR=#465584][FONT=Courier]    Set rs = Nothing[/FONT][/COLOR]
[COLOR=#465584][FONT=Courier]    Set db = Nothing[/FONT][/COLOR]
[COLOR=#465584][FONT=Courier]    Exit Function[/FONT][/COLOR]

[COLOR=#465584][FONT=Courier]Error_Handler:[/FONT][/COLOR]
[COLOR=#465584][FONT=Courier]    MsgBox "The following error has occured." & vbCrLf & vbCrLf & _[/FONT][/COLOR]
[COLOR=#465584][FONT=Courier]           "Error Number: " & Err.Number & vbCrLf & _[/FONT][/COLOR]
[COLOR=#465584][FONT=Courier]           "Error Source: fGetDirFileInfo" & vbCrLf & _[/FONT][/COLOR]
[COLOR=#465584][FONT=Courier]           "Error Description: " & Err.Description, _[/FONT][/COLOR]
[COLOR=#465584][FONT=Courier]           vbCritical, "An Error has Occured!"[/FONT][/COLOR]
[COLOR=#465584][FONT=Courier]    Resume Error_Handler_Exit[/FONT][/COLOR]
[COLOR=#465584][FONT=Courier]End Function[/FONT][/COLOR]

 

[MrIfOnly]

Re: How do get the folder name using FilesystemObejct

Try this:

[FONT=Courier][COLOR=#465584]rs![FileShortDirectory] = fso.GetFileName(f.parentfolder)[/COLOR][/FONT]

CJ

 

[xenou]

Re: How do get the folder name using FilesystemObejct

sPath is the folder path so you just need:

fso.GetFolder(sPath).Name

Note: since you have sPath you can do some old-fashioned string manipulation too (without using FSO).

Function myGetFolderName(ByVal sPath As String) As String
Dim a

    '//Ignore zero-length strings
    If Len(sPath) = 0 Then
        Exit Function
    End If
        
    '//Ignore trailing slashes
    If Right(sPath, 1) = "\" Then
        sPath = Left(sPath, Len(sPath) - 1)
    End If
    
    '//Ignore strings without a slash in them - these could only be paths to root folders
    '    (we would require special handling if we want to allow this function to return paths to drives or root folders).
    If InStr(sPath, "\") = 0 Then
        Exit Function
    End If
    
    '//Return the last folder listed in the path
    a = Split(sPath, "\")
    myGetFolderName = a(UBound(a))

End Function