Search code examples
excelvbafilesystemobjectfso

Access files with long paths (over 260)

I'm using Microsoft Scripting Runtime (FSO) to parse folders and produce a list of all of its contents, the folders are on a network and resultant paths end up longer than 260. The minimum code I have is as below:-

Private Sub ProcessFolder(ByVal StrFolder As String)
Dim Fl              As File
Dim Fldr            As Folder
Dim RootFldr        As Folder

Set RootFldr = FS.GetFolder(StrFolder)

    For Each Fl In RootFldr.Files
        Debug.Print Fl.Path
    Next

    For Each Fldr In RootFldr.SubFolders
        DoEvents
        ProcessFolder Fldr.Path
    Next

Set RootFldr = nothing    
End sub

At a certain level StrFolder length became 259, the Set RootFldr ... folder line worked but For Each Fl In RootFldr.Files gave the error of 76: Path not found, presumably because the content causes the path to breach the 260 limit.

There were files in the folder when looking in Windows Explorer. I am using Excel as the host for this code as I'm outputting the result to workbooks.

Just to be super clear on my question and its background, I need to use FSO (happy to be shown alternatives if they exist) to access files deeper than 260 characters deep in their network path. I need it as FSO as the tool I have is taking the folder paths and the file paths, name, size created, and modified.

Solution

  • This took a little creative coding but the use of ShortPath was the answer.

    This tool was to create a list of every folder and file in a root folder, the files also showing their size, and created/modified dates. The issue was when the resultant path of a file or folder was over 260, then the error Error 76: Path Not Found was thrown and the code would not capture the content of that area.

    Using Microsoft Scripting Runtime (FSO) ShortPath would get around this issue but the path went from human readable to coded:-

    Full path \\ServerName00000\Root_Root_contentmanagement\DPT\STANDARDS_GUIDELINES\VENDOR_CERTIFICATION_FILES\PDFX_CERTIFICATION_ALL\2006_2007\DPT\CompantName0\Approved\Quark\India under Colonial Rule_structure sample\058231738X\Douglas M. Peers_01_058231738X\SUPPORT\ADDITIONAL INFORMATION\IUC-XTG & XML file

    Short Path \\lo3uppesaapp001\pesa_cmcoe_contentmanagement\CTS\S4SJ05~5\V275SE~8\PDM5D9~G\2N52EQ~5\HPE\GS9C6L~U\Approved\Quark\IQPSJ5~F\0CWHH1~G\DOFNHA~8\SUPPORT\A6NO7S~K\IUC-XTG & XML file

    (Note I've altered the full path to protect IP and company info but the size is the same)

    You can see while I could pass short path to someone and they could put it into Windows Explorer to get there, they would know know where it went by simply looking, to get around this a used a global variable that kept the folder path as a full string and followed what the short path was doing. this string is then what I output to the user. The below code is cut down but shows how I achieved it.

    The short answer is ShortPath in FSO will get past the issue but the path will not be pretty.

    Dim FS              As New FileSystemObject
Dim LngRow          As Long
Dim StrFolderPath   As String
Dim WkBk            As Excel.Workbook
Dim WkSht           As Excel.Worksheet

Public Sub Run_Master()

Set WkBk = Application.Workbooks.Add

    WkBk.SaveAs ThisWorkbook.Path & "\Data.xlsx"

    Set WkSht = WkBk.Worksheets(1)

        WkSht.Range("A1") = "Path"
        WkSht.Range("B1") = "File Name"
        WkSht.Range("C1") = "Size (KB)"
        WkSht.Range("D1") = "Created"
        WkSht.Range("E1") = "Modified"

        LngRow = 2

        Run "\\ServerName00000\AREA_DEPT0_TASK000"

    Set WkSht = Nothing

    WkBk.Close 1
Set WkBk = Nothing

MsgBox "Done!"

End Sub

Private Sub Run(ByVal StrVolumeToCheck As String)
Dim Fldr            As Folder
Dim Fldr2           As Folder

Set Fldr = FS.GetFolder(StrVolumeToCheck)

    'This is the variable that follows the full path name
    StrFolderPath = Fldr.Path

    WkSht.Range("A" & LngRow) = StrFolderPath
    LngRow = LngRow +1

    For Each Fldr2 In Fldr.SubFolders
        If (Left(Fldr2.Name, 1) <> ".") And (UCase(Trim(Fldr2.Name)) <> "LOST+FOUND") Then
            ProcessFolder Fldr2.Path
        End If
    Next

Set Fldr = Nothing

End Sub

Private Sub ProcessFolder(ByVal StrFolder As String)
'This is the one that will will be called recursively to list all files and folders
Dim Fls             As Files
Dim Fl              As File
Dim Fldrs           As Folders
Dim Fldr            As Folder
Dim RootFldr        As Folder

Set RootFldr = FS.GetFolder(StrFolder)

    If (RootFldr.Name <> "lost+found") And (Left(RootFldr.Name, 1) <> ".") Then

        'Add to my full folder path
        StrFolderPath = StrFolderPath & "\" & RootFldr.Name

        WkSht.Range("A" & LngRow) = StrFolderPath
        WkSht.Range("D1") = RootFldr.DateCreated
        WkSht.Range("E1") = RootFldr.DateLastModified
        Lngrow = LngRow + 1

        'This uses the short path to get the files in FSO
        Set Fls = FS.GetFolder(RootFldr.ShortPath).Files

            For Each Fl In Fls
                'This output our string variable of the path (i.e. not the short path)
                WkSht.Range("A" & LngRow) = StrFolderPath
                WkSht.Range("B" & LngRow) = Fl.Name
                WkSht.Range("C" & LngRow) = Fl.Size /1024 '(bytes to kilobytes)
                WkSht.Range("D" & LngRow) = Fl.DateCreated
                WkSht.Range("E" & LngRow) = Fl.DateLastModified

                LngRow = LngRow + 1

            Next
        Set Fls = Nothing

        'This uses the short path to get the sub-folders in FSO
        Set Fldrs = FS.GetFolder(RootFldr.ShortPath).SubFolders
            For Each Fldr In Fldrs
                'Recurse this Proc
                ProcessFolder Fldr.Path
                DoEvents
            Next
        Set Fldrs = Nothing

        'Now we have processed this folder, trim the folder name off of the string
        StrFolderPath = Left(StrFolderPath, Len(StrFolderPath) - Len(RootFldr.Name)+1)

    End If
Set RootFldr = Nothing

End Sub

    As mentioned this is a cut version of the code that is working for me to exemplify the the method used to get past this limit. Actually seems quite rudimentary once I'd done it.