Show documents from subfolders (1 Viewer)

[urjudo]

Hello,
I have a question, I want to able to show all the documents in a sharepoint folder to a list box in a form. These documents are in different subfolders, I'm having trouble to figure it out how to list all the documents in those subfolders inside the main folder. Attached is the DB, it's working when the documents are in a main folder but it's not working when the docs are in the subfolder.

Thanks for your time.

 

[arnelgp]

try this.

 

[neuroman9999]

if arne's example doesn't cover everything, this will get you to the subdirs. it prints the subdir names and the files inside of each of them. change it to pull the doc names and put them inside the list box:

Sub print_subdirs()
'this routine prints subdirectory names
Dim fso As Object
Dim folder
Dim subfolder
dim file

Set fso = CreateObject("scripting.filesystemobject")
Set folder = fso.getfolder("top_level_folder_path_here")

    For Each subfolder In folder.subfolders
        Debug.Print UCase(subfolder.Name & " files.....")
            For Each file In subfolder.Files
                Debug.Print file
            Next file
    Next subfolder

Set fso = Nothing
Set folder = Nothing

End Sub

 

[urjudo]

@arnelgp ,
Thanks for your reply. How do I just list the name of the docs inside the folders/subfolders not with the location name also is any way I can sort them?

@neuroman9999 ,
Where I can place the code your suggest? in a new module or inside the listbox On Load?

Thanks both!

 

[arnelgp]

only filename, will be confusing as to which path are being displayed (main or subfolder).

of course we can do that, but we also need the "full path" (maybe a second hidden column on the listbox)
so that you can display the correct file.

 

[urjudo]

@arnelgp ,
I will link to the caseNo to that folder and subfolders, that's why I only need to show the file name. do I need to modify in the module for that?

 

[urjudo]

@arnelgp ,
sorry, not sure where to change in the module.

 

[arnelgp]

i made changes and make the listbox to 2 column (1 column hidden).
also you "double-click" on the list to view the pdf.

edit: also added a 1 column listbox (it is under the main listbox).

 

[urjudo]

@arnelgp ,
I'm so sorry to bother you again. one more question. Your new DB is working great, but I have a small issue if you can help me for this too, I will be great appreciate as I'm trying to figure it out how to fix.
Here is the path our docs store look like:
Environ("UserProfile")\OneDrive\CaseDoc\c1234567_Doe, John vs. Doe, Jane\Correspondence\00E345678\all the pfd doc
I used the recordset for the SPCaseName.
so I set the sPath = Environ("UserProfile") & "\OneDrive\CaseDocs\" & SPCaseName & "\" & Correspondence"
however when the List box show, it shows c1234567_Doe instead the name of the pfds inside the 00E345678 subfolders but the count is correct (I have 10 pdfs inside the 00E345678 fsubfolder.
also the Double click is not opening to the correct location

I can see the hidden list box sheows: C:\Users\nameoftheuser\OneDrive\CaseDoc\c1234567_Doe

Please if you can help if you don't mind because I don't know what I missing here.

Thank yo so much!

 

[neuroman9999]

@neuroman9999 ,
Where I can place the code your suggest? in a new module or inside the listbox On Load?

anywhere. either of those is fine. you have to change it to throw the dirs into the list line items. arne can give you the rest

 

[urjudo]

@neuroman9999,
Thanks.

@arnelgp ,
I know what my problem, it's the SPCaseName folder cause the issue. .
the path is sPath = Environ("UserProfile") & "\OneDrive\CaseDocs\" & SPCaseName & "\" & Correspondence"
because I have the SPCaseName as C1234567_Doe, John vs. Doe, Jane , it cut off up to C1234567_Doe before the comma. it seems doesn't like the comma but I have to use this format for the name folder, How Can I fix it? I think I have to make the changes on the Form_Load but not sure what to do.
I tried: sPath = Environ("UserProfile") & "\OneDrive\CaseDocs\" & chr(34) & SPCaseName & chr(34) "\" & Correspondence" - > that doesn't work

 

[urjudo]

@arnelgp ,
I think I figure it out now. I changed the lst.AddIten varItem in the module of the ListFiles to lst.AddItem "'" & varItem & "'" that fixed the issue.
But trying to figure when click on open the pdfs, only the first Order# in the case will opens the second order# in the case will not open the pdfs
here is the screen shot. 00d123456 should be in folder 00d123456 not 00d123457.

 

[urjudo]

in the invisible list box shows the correct path on each OrderNo, but when tried to click to each to view then it will pick up the first OrderNo in the query and for every docs. here is the screen shot and the Database

 

[arnelgp]

try changing this:

Application.FollowHyperlink Environ("UserProfile") & "\OneDrive\CaseDocumentation\" & SPCaseName & "\" & "Correspondence" & "\" & NewOrder & "\" & Lst_Files & ""

To:

Application.FollowHyperlink "" & Me.Lst_Files.Column(1) & ""

 

[urjudo]

@arnelgp ,
Thank you for reply. I'm sorry to keep bothering you. I did try Application.FollowHyperlink "" & Me.Lst_Files.Column(1) & "", it doesn't open the docs. I just don't understand if the invisible list the shows the correct path, why it doesn't open to the correct path when click on it and this is only for if it's more than one OrderNo folders. It always only pick the first OrderNo in the query and not loop for the next OrderNo in the query. That's why it pick the wrong folder for the OrderNo and couldn't open it. If I used Application.FollowHyperlink Me.Lst_Files.Column(1) then I gor an error message "Invalid use of Null". I really appreciated your patience with me.

 

[arnelgp]

maybe you should Avoid using (space), (comma) and (dot) on your Folder/File name.
you are concern about the way the Folder is named, when Access has difficulty opening it.
you can just make it simpler:


Doe_John_vs_Doe_Jane\...

anybody can guess the connotation of the above.