Routine shows list of PDF files in a windows folder but only to a certain file name length

[neilshaw123]

I have a piece of code to list PDF files that I am using from the answer on this thread Fill a combo box with filenames in a directory. It works until a certain condition is met; I have noticed that the files do not get picked up/shown when the file name length is beyond a certain length. The file that is not being picked up is as named as follows:

"Testfile GGGGG XXXX SSSSS AAAAAAAA aa Wwww Declaration form_XXXX1234.04.AUG16_31AUG16.pdf"

Does anyone have any ideas how to get around this?

 

[gallen]

Hello and welcome.

I can't see your thread you've linked to for some reason. Work Firewall not allowing it.

However I just tired using DIR with a directory containing the filename you have there and it lists the file no problem. What code are you using?

 

[neilshaw123]

Hello and welcome.

I can't see your thread you've linked to for some reason. Work Firewall not allowing it.

However I just tired using DIR with a directory containing the filename you have there and it lists the file no problem. What code are you using?

Here is the code I have based mine on:

[COLOR=blue]Option Explicit[/COLOR][COLOR=#333333][/COLOR] 
[COLOR=blue]Private[/COLOR] [COLOR=blue]Sub[/COLOR] UserForm_Initialize() 
     [COLOR=darkgreen]'Files in folder listed in Listbox1[/COLOR]
    [COLOR=blue]Dim[/COLOR] FSO [COLOR=blue]As[/COLOR] [COLOR=blue]Object[/COLOR], fld [COLOR=blue]As[/COLOR] [COLOR=blue]Object[/COLOR], Fil [COLOR=blue]As[/COLOR] [COLOR=blue]Object[/COLOR] 
    [COLOR=blue]Dim[/COLOR] SubFolderName [COLOR=blue]As[/COLOR] [COLOR=blue]String[/COLOR] 
    [COLOR=blue]Dim[/COLOR] i [COLOR=blue]As[/COLOR] [COLOR=blue]Integer[/COLOR] 
    [COLOR=blue]Set[/COLOR] FSO = CreateObject("Scripting.FileSystemObject") 
    Me.ListBox1.Clear [COLOR=darkgreen]'clear previous entries[/COLOR]
    SubFolderName = "C:\" 
    [COLOR=blue]Set[/COLOR] fld = FSO.GetFolder(SubFolderName) 
    [COLOR=blue]For Each[/COLOR] Fil [COLOR=blue]In[/COLOR] fld.Files 
        i = i + 1 
        Me.ListBox1.AddItem Fil.Name 
         
    [COLOR=blue]Next[/COLOR] Fil 
     
     
     
[COLOR=blue]End Sub[/COLOR]

 

[gallen]

I used your code and it picked up the file no problem (although it picks up all files not just pdf). My Listbox was populated with all filenames from the directory. Out of interest, try this code which also works fine but without any late binding.

Private Sub UserForm_Initialize()
    Dim sPath As String
    Dim sFileName As String
    
    Me.ListBox1.Clear 'clear down Listbox
    
    sPath = "C:\" 'Change as required ensuring you have the final backslash '\'
    
    sFileName = Dir(sPath & "*.pdf") 'Get all pdf files from the path
    
    'loop through all files in the location
    Do While sFileName <> ""
        Me.ListBox1.AddItem sFileName
        sFileName = Dir 'Get next file
    Loop
End Sub

 

[neilshaw123]

Thank you for your reply gallen. I have just realised what the problem might be. It seems that there is a restriction on the length of the directory (perhaps over 256 characters). It turns out that I am unable to control where users place files so this has to stay in place.

In order to replicate this issue, create 2 directories as follows and place the file there, change your code to point to this directory instead:

C:\Testfile GGGGG XXXX SSSSS AAAAAAAA aa Wwww Declaration form_XXXX1234.04.AUG16_31AUG16\Testfile GGGGG XXXX SSSSS AAAAAAAA aa Wwww Declaration form_XXXX1234.04.AUG16_31AUG16\Testfile GGGGG XXXX SSSSS AAAAAAAA aa Wwww Declaration form_XXXX1234.04.AUG16_31AUG16.pdf I am still at a loss though so any thoughts would be very much appreciated.

<tbody>
</tbody>

 

[gallen]

I still had no issues with your suggestion. I used this code:

Private Sub UserForm_Initialize()
    Dim sPath As String
    Dim sFileName As String
    
    Me.ListBox1.Clear 'clear down Listbox
    
    sPath = "C:\Test\Testfile GGGGG XXXX SSSSS AAAAAAAA aa Wwww Declaration form_XXXX1234.04.AUG16_31AUG16\" 'Change as required ensuring you have the final backslash '\'
    
    sFileName = Dir(sPath & "*.pdf") 'Get all pdf files from the path
    
    'loop through all files in the location
    Do While sFileName <> ""
        Me.ListBox1.AddItem sFileName
        sFileName = Dir 'Get next file
    Loop
End Sub

 

[neilshaw123]

Hi gallen,

Yours was successful because your file directory name length is not as per my example (over 255 characters), which is the upper acceptable limit in this regard. I have since found that a workaround to this is to use the DOS/shortened directory approach so where a file directory is long:

C:\Testfile GGGGG XXXX SSSSS AAAAAAAA aa Wwww Declaration form_XXXX1234.04.AUG16_31AUG16\Testfile GGGGG XXXX SSSSS AAAAAAAA aa Wwww Declaration form_XXXX1234.04.AUG16_31AUG16\Testfile GGGGG XXXX SSSSS AAAAAAAA aa Wwww Declaration form_XXXX1234.04.AUG16_31AUG16.pdf

You shorten it as per the following:

C:\Testfil~1\Testfile GGGGG XXXX SSSSS AAAAAAAA aa Wwww Declaration form_XXXX1234.04.AUG16_31AUG16\Testfile GGGGG XXXX SSSSS AAAAAAAA aa Wwww Declaration form_XXXX1234.04.AUG16_31AUG16.pdf


This isn't a 100% foolproof approach but it has gotten me out of jail free this time.

 

[gallen]

So I did. Apologies.

 

[gallen]

Yes, Win 7 won't even let me create the file

C:\Testfile GGGGG XXXX SSSSS AAAAAAAA aa Wwww Declaration form_XXXX1234.04.AUG16_31AUG16\Testfile GGGGG XXXX SSSSS AAAAAAAA aa Wwww Declaration form_XXXX1234.04.AUG16_31AUG16\Testfile GGGGG XXXX SSSSS AAAAAAAA aa Wwww Declaration form_XXXX1234.04.AUG16_31AUG16.pdf

I get a message saying path too long. So I think your only answer is to shorten your file's/folder's name(s)