VBA reading files over the 256 limit

[hatstand]

Hi All,

I have read around a bit and still haven't found a solution as to how I read files that are longer than the 256 limit. I have built a folder/file tool to pull in all of the attributes that I need using Power Query. But this stops when it try's to read a file longer than 256. So it's back to VBA to find an answer.

Basically what I would like to do is list all files and folder/sun folders paths and sizes, but when it comes across a file that is to long, either report it as to long or ignore it.

Has anyone come a cross this problem before and found a solution? Thanks.

 

[pbornemeier]

I believe you are talking about the file path name limit. I needed to inventory a huge number of files on a network. When I attempted to access a file with a PathNameExt that was too long, I used the shell command to map part of that path to a new drive letter then continued in VBA to process the files.

 

[hatstand]

I'm not sure how to do that, but it's enough for me to do some research in the right direction. Thanks for the help, much appreciated.

 

[pbornemeier]

This code will generate a list of all folders.
Make a workbook with a sheet named 'FolderNames"

Option Explicit

Sub GetFolderNames()

    Dim sSearchRootPath As String
    Const lShellAndWaitDelay As Long = 100000    'Milliseconds to wait for Dir comand to complete
    Const lShellAndWaitWinStyle As Long = vbHide
    Dim sThisWbkPath As String
    Dim iFreeFile As Integer
    Dim lResult As Long
    Dim sSearchPattern As String
    
    sSearchPattern = ""
    
    sThisWbkPath = ThisWorkbook.Path & "\"
    sSearchRootPath = GetFolder
    
    'Delete existing / create empty test file to receive Dir output
    iFreeFile = FreeFile
    Open sThisWbkPath & "FolderNames.txt" For Output As #iFreeFile
    Close #iFreeFile
    
    'Clear 'Folders' worksheet
    Worksheets("FolderNames").UsedRange.Cells.ClearContents
    
    'Create Text File in this workbook's folder
'    lResult = ShellAndWait("cmd /c  ""Dir """ & sSearchRootPath & sSearchPattern & """ /a-d " & _
'        IIf(bSearchSubFolders, "/s", "") & " /b >> """ & _
'        sThisWbkPath & "Files.txt""", lShellAndWaitDelay, lShellAndWaitWinStyle, PromptUser)
    lResult = ShellAndWait("cmd /c  ""Dir """ & sSearchRootPath & sSearchPattern & """ /ad " & _
        IIf(True, "/s", "") & " /b >> """ & _
        sThisWbkPath & "FolderNames.txt""", lShellAndWaitDelay, lShellAndWaitWinStyle, PromptUser)
        
    If lResult <> 0 Then MsgBox "Dir did not complete increase value of lShellAndWaitDelay": End

    'Import Text File
    Workbooks.OpenText filename:= _
        sThisWbkPath & "FolderNames.txt", Origin:=437, StartRow:=1, DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, _
        FieldInfo:=Array(1, 1), TrailingMinusNumbers:=True
    Range("A1").CurrentRegion.Copy Destination:=ThisWorkbook.Worksheets("FolderNames").Range("A2")
    Application.CutCopyMode = False
    Workbooks("FolderNames.txt").Close SaveChanges:=False
    
    VBA.Beep
    
    MsgBox "Done"

End Sub

This post shows how to map a folder to a drive letter

Map drives via VBA

At work, a script runs as we log on to the network that deletes and re-maps our network drives for us (which really stinks when you want to add a new mapped drive). Sometimes this script only removes the drives and doesn't re-map them. I'm now considering making a project via vba that will map...

www.mrexcel.com

 

[hatstand]

Thanks, I will give this a go. Much appreciated.