UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
2 Pages V  1 2 >  (Go to first unread post)
   Reply to this topicStart new topic
> Allen Browne Listfiles Recursively: Can This Be Modified To Work With Folder Levels And Related Tables ?, Access 2016    
 
   
Oblio
post Jun 27 2017, 09:20 AM
Post#1



Posts: 266
Joined: 5-February 15



Tables are related 1 to many in a structure:
Parent Folder
Sub-folder
Files

Parent Folder holds the Main starting Folder Name
Sub-Folder holds all of the names of the individual folders in this first layer
Files holds all of the names of each file contained in each sub-folder.

Can this be done using VBA ?

Example Data:

Parent Folder: 2017-06-29
Subfolders: 1, 2, 3, 4, 5, 6, 7, etc (always numerical)
Files: (In Subfolder 1: 01.pdf, 02.pdf, o3.pdf)

Or, do I just record the entire details of the structure and then break it out with an action query ?

Thanks,

Bill
Go to the top of the page
 
BruceM
post Jun 27 2017, 09:24 AM
Post#2


UtterAccess VIP
Posts: 8,134
Joined: 24-May 10
From: Downeast Maine


QUOTE
Can this be done using VBA ?
Allen's code lists files in folders and subfolders. What further requirements do you have?
Go to the top of the page
 
ADezii
post Jun 27 2017, 09:35 AM
Post#3



Posts: 3,086
Joined: 4-February 07
From: USA, Florida, Delray Beach


  1. Are you referring to the following Table Relationships?
    CODE
    ParentFolder[PID]{1} ==> {MANY}[PID]SubFolder{1}[SID] ==> {MANY}[SID]Files
  2. Do you want to adapt Allen Browne's Code to these Table Structures? If yes, why would you even need a Files Table? All you would need is a Parent and a related Sub Folder.
  3. I am assuming you want the Files displayed in a Table, correct?
Go to the top of the page
 
Oblio
post Jun 27 2017, 09:49 AM
Post#4



Posts: 266
Joined: 5-February 15



I do not end up with related tables is the issue...

So when I run ListFilesToTable, I have the list of files but not the "Parsed related Subfolder_ID recorded in the table... Or the Main Level Parent ID in the Subfolder, as well).


So, for example, when I run ListFilesToTable, I end up with:

In the Files Level, Headings: Files_ID (Autonumber PK), Folders_ID (Foreign Key from Parent Level), fName (the actual File Name, Text), fPath (the actual File Pathway, Text)

So, for these headings as listed:

Fields_ID = 47588
Folders_ID =
fName = 01.pdf
fPath = C:/Bill/1

Sorry, I hope I am explaining this correctly....

Bill
Go to the top of the page
 
Oblio
post Jun 27 2017, 09:58 AM
Post#5



Posts: 266
Joined: 5-February 15



Answers to your questions:

# 1 Yes, exactly !

#2 Yes, I do want to modify his code to reflect these table relationships.

The goal is to have the Parent Folder only listed once, the subfoldersID once, and the related files. the reason I want all three parsed out into tables is we run various checks and cross checks based especially on the numbers of certain file type names and numbers . We also run queries such as "List the Highest Sub-folder Number, then find all files named 01.pdf within the subfolder level only to insure there is at least 1 01.pdf in each subfolder (it is the manifest of the items in that folder). Often the person scanning forgets to fill a folder, or a folder number sequentially and various other things.
Lastly, using a form and subforms, I want to create a hyperlinked File name to open the .pdf in a webbrowser control for viewing emailing etc.

#3 Yes, the files in the file table, the subfolders in a subfolder table and the Main folder as the Parent table with related Foreign keys between each as in # 1.

Thank you for looking at this... I just cannot seem to get it done :/

Bill
Go to the top of the page
 
ADezii
post Jun 27 2017, 10:28 AM
Post#6



Posts: 3,086
Joined: 4-February 07
From: USA, Florida, Delray Beach


Allen Browne's Code then needs to be adapted to populate 3 Interrelated Tables for any Parent Folder/Filespec combination. At little tricky, actually a lot tricky, but definitely doable. As long as you remain patient, I'll be happy to have a look at how this can be done.
Go to the top of the page
 
Oblio
post Jun 27 2017, 11:02 AM
Post#7



Posts: 266
Joined: 5-February 15



OMG! notworthy.gif woohoo.gif thanks.gif

THANK YOU... I was so alone...now there is hope where there was none !!!

Cannot thank you enough for that !!! I will be patient believe me and thank you so much !!!

This stuff is so hard to do when you are new...

Thanks so much smile.gif

Bill
Go to the top of the page
 
ADezii
post Jun 27 2017, 11:36 AM
Post#8



Posts: 3,086
Joined: 4-February 07
From: USA, Florida, Delray Beach


Before I can attempt anything, I will need to see how you would expect to see the following Data (from Allen Browne's Code), actually a subset of it, spread across the 3 Tables (Parent, Sub-Folder, Files). I especially need to see how SubSub Folders and beyond are handled.
MediaPathMediaFileName
C:\Parent\File1.txt
C:\Parent\SubFolder\File2.txt
C:\Parent\SubFolder\File3.txt
C:\Parent\SubFolder\SubSubFolder\File4.txt
C:\Parent\SubFolder\SubSubFolder\File5.txt
C:\Parent\SubFolder\SubSubFolder\File6.txt
C:\Parent\SubFolder\SubSubFolder\SubSubSubFolder\File7.txt
C:\Parent\SubFolder\SubSubFolder\SubSubSubFolder\File8.txt
C:\Parent\SubFolder\SubSubFolder\SubSubSubFolder\SubSubSubSubFolder\File10.txt
C:\Parent\SubFolder\SubSubFolder\SubSubSubFolder\SubSubSubSubFolder\File11.txt
C:\Parent\SubFolder\SubSubFolder\SubSubSubFolder\SubSubSubSubFolder\File12.txt
C:\Parent\SubFolder\SubSubFolder\SubSubSubFolder\SubSubSubSubFolder\File9.txt
C:\Parent\SubFolder\SubSubFolder\SubSubSubFolder\SubSubSubSubFolder\SubSubSubSubSubFolder\File13.txt
C:\Parent\SubFolder\SubSubFolder\SubSubSubFolder\SubSubSubSubFolder\SubSubSubSubSubFolder\File14.txt
C:\Parent\SubFolder\SubSubFolder\SubSubSubFolder\SubSubSubSubFolder\SubSubSubSubSubFolder\File15.txt
Go to the top of the page
 
Oblio
post Jun 27 2017, 07:32 PM
Post#9



Posts: 266
Joined: 5-February 15



The Parent Folder does not contain any files, it only contains subfolders. The files level is the only place to contain files.

So, C:\Parent contains no media files whatsoever. it is simply the parent folder.

C:\Parent\Subfolder is simply the collection of subfolder names (numbers ranging from 1 to 1000 max) Again, no media files at this level.

C:\Parent\Subfolder\Files shows the files in each subfolder above...

so, Parent Folder 2017/06/27 (Parent_ID = 1)

Parent\Subfolder\ Parent_ID 1, Subfolder_ID =1, Subfolder ID =2,Subfolder_ID = 3 ALL with Parent_ID= 1

Parent\Subfolder\Files Subfolder=1, Files_ID = 1, Files_ID=2, Files_ID =3
Subfolder=2, Files_ID = 1 etc etc


I'm sorry if I am not making sense...
Go to the top of the page
 
Oblio
post Jun 27 2017, 07:36 PM
Post#10



Posts: 266
Joined: 5-February 15



The last three folders of the pathway will always be: Parent\Subfolder\Files...
Go to the top of the page
 
moke123
post Jun 28 2017, 05:29 AM
Post#11



Posts: 1,390
Joined: 26-December 12
From: Berkshire Mtns.


I'm having a hard time following what you want but recall you asked a similiar question in the past. here's the prior post
Do you want to wind up with a list of all the folders?
for instance, using Adezis list of files you would end up with
CODE
Parent
SubFolder
SubSubFolder
SubSubSubFolder
SubSubSubSubFolder
SubSubSubSubSubFolder

to do this you would select the distinct file paths and parse out the last folder name in the path.
Go to the top of the page
 
Oblio
post Jun 28 2017, 07:43 AM
Post#12



Posts: 266
Joined: 5-February 15



Ok, I have been using a bunch of queries and functions to do so, and it all seems like a jumble. With help, I have been able to add the Parent ID into the subfolders table using VBA. I do not know how to add the Subfolders_ID using VBA. The whole Idea is to make it easy for the scan op. at the end of the day to run their stats and what they have done for the day.

While looking around last night I stumbled on a Database by Crystal that modifies Allen Browne's Functions and is a very sophisticated application which seems to do what I need. I will attach it here in addition to my project for the original question I asked here in case it helps anyone out there searching for an answer to this VBA question.

It's like I need to parse out the Parent name from the pathway chosen, write it to a table, retrieve the auto-number of the Parent ID and add it to each subfolder found, grab the subfolder ID and write it to each file for each subfolder.

On the form Batch_Scan_Details, the Scan Op. would move through a set of command buttons to get VBA to write the Parent_ID and name of the pathway and insert that Parent ID into the subfolders...

Any ways, here is what I have attached to help see what I am trying to do...

I really apologize for the confusion...I am having difficulty explaining this !!!

Thanks for sticking with me !

Bill
Attached File(s)
Attached File  ListFiles_080323_Crystal_090720CR.zip ( 207.59K )Number of downloads: 13
Attached File  Scan_Op_v_1.zip ( 125.98K )Number of downloads: 7
 
Go to the top of the page
 
the_captain_slog
post Jun 28 2017, 07:51 AM
Post#13



Posts: 1,036
Joined: 1-July 09
From: England - UK A small island north of France


add this into a module it will print out the file details - you will need to work out how to add / lookup the existing parent folder names already stored in the tables


edit :- change mask to suit you file naming


CODE
Option Compare Database


Public prntpath As String, subpath As String  ', filename As String


Sub Demo()
    Dim fso As Object 'FileSystemObject
    Dim fldStart As Object 'Folder
    Dim fld As Object 'Folder
    Dim fl As Object 'File
    Dim Mask As String
    'Dim prntpath As String, subpath As String, filename As String

    Set fso = CreateObject("scripting.FileSystemObject") ' late binding
    'Set fso = New FileSystemObject 'or use early binding (also replace Object types)

    Set fldStart = fso.GetFolder("Your folder names here") ' <-- use your FileDialog code here

    Mask = "*.txt"
    Debug.Print fldStart.Path & "\"
    'ListFiles fldStart, Mask
    For Each fld In fldStart.SubFolders
        'Debug.Print fld
        prntpath = fld
        prntpath = Mid(prntpath, InStrRev(prntpath, "\") + 1)
        Debug.Print prntpath
        'ListFiles fld, Mask
        ListFolders fld, Mask
    Next
End Sub


Sub ListFolders(fldStart As Object, Mask As String)
    Dim fld As Object 'Folder
    'Dim subpath As String
    For Each fld In fldStart.SubFolders
'        Debug.Print fld.Path & "\"
        'Debug.Print fld.Path
        
        subpath = fld
        subpath = Mid(subpath, InStrRev(subpath, "\") + 1)
        Debug.Print subpath
        
        ListFiles fld, Mask
        ListFolders fld, Mask
    Next

End Sub

Sub ListFiles(fld As Object, Mask As String)
    Dim fl As Object 'File
    For Each fl In fld.Files
        If fl.Name Like Mask Then
'            Debug.Print fld.Path & "\" & fl.Name
            Debug.Print prntpath, subpath, fl.Name, fld.Path
        End If
    Next
End Sub
Go to the top of the page
 
ADezii
post Jun 28 2017, 09:59 AM
Post#14



Posts: 3,086
Joined: 4-February 07
From: USA, Florida, Delray Beach


  1. First and foremost, let me state that the Demo I am uploading is only preliminary in nature. I am not even sure that this is what you are looking for.
  2. I figured that Allen Browne did most of the work already so I used his Code as a base from which to start. The only functionality that I added is the introduction of a Folder Dialog Box that will allow you to select the Top/Parent Folder from which to start.
  3. Once a Folder is selected, the Code assumes a Filespec of *.* and True for processing all SubFolders.
  4. Allen's Code generates the Table zzLibScan which basically contains all the information that we need, only duplicated.
  5. I added Code that parses the Data in zzLibScan and places it into three interrelated Tables named: tblTopFolder, tblSubFolders, and tblFiles. Records in each of these Tables are unique with tblTopFolder containing only the Top Folder name.
    CODE
    tblTopFolder.[TopFolder_ID]{1} ==> tblSubFolders.[TopFolder_ID]{MANY}
    tblSubFolders.[SubFolder_ID]{1} ==> tblFiles.[SubFolder_ID]{MANY}
  6. The final step in the Code execution is the opening of tblFiles.
  7. I will not go into the details of how all this is accomplished but simply download this initial Demo to see for yourself.

Attached File(s)
Attached File  Allen_Browne_Modified.zip ( 641.32K )Number of downloads: 14
 
Go to the top of the page
 
Oblio
post Jun 28 2017, 10:03 AM
Post#15



Posts: 266
Joined: 5-February 15



lmao... a little island north of France... thanks for that laugh !!!

Ok, I will give this code a try... thank you so much for having a look and I cannot thank you enough for helping me with my problem... I really appreciate it very much hat_tip.gif

I will post back once I get it working for anyone to see...

Thanks again, and hope you have a great day !

Bill
Go to the top of the page
 
Oblio
post Jun 28 2017, 10:08 AM
Post#16



Posts: 266
Joined: 5-February 15



WOW !!!!!!!!!!!!!!!!!!!!!!!!!!! pompom.gif

From the description you have provided it seems perfect !!! I cannot wait to open it thanks.gif

SO VERY EXCITED woohoo.gif

I promise to post back soon...

THANK YOU DEAR SIR !!!!! cheers.gif

Bill
Go to the top of the page
 
Oblio
post Jun 28 2017, 01:45 PM
Post#17



Posts: 266
Joined: 5-February 15



What a work of art sir.... I am truly honoured and would never have come up with something so beautiful...

and the code documentation is so amazing... I wish more people would be so detailed as it really helps when you are learning...so thank you so much for that too !

The only problem was I wasn't sure which button to click on the main form iconfused.gif lol... I had a good laugh at that...

So it is what I am looking for... I added fields in the tables to show what the parsed out targets names I was looking for...hoping it will clear up what I was looking for as far as the parsed out name at each level...

We do not use the folders that end in z... I had been removing them in a query <> "z"

Truly cannot thank you enough for this and I hope someone is as thoughtful and generous to you as you have been to all of us !!!

guiness.gif
woohoo.gif cheers.gif
Attached File(s)
Attached File  Allen_Browne_Modified_v_2.zip ( 44.21K )Number of downloads: 12
 
Go to the top of the page
 
ADezii
post Jun 28 2017, 02:49 PM
Post#18



Posts: 3,086
Joined: 4-February 07
From: USA, Florida, Delray Beach


yw.gif As stated earlier, it is by no means the 'Final Product', I'll leave that up to you.
Go to the top of the page
 
ADezii
post Jun 28 2017, 06:24 PM
Post#19



Posts: 3,086
Joined: 4-February 07
From: USA, Florida, Delray Beach


One departing comment: Every time that the Code is executed it involves a continuing cycle of Deleting/Appending Records to four separate Tables which eventually would take its toll on your DB. I would strongly suggest enabling the Compact on close Option.
Go to the top of the page
 
Oblio
post Jun 29 2017, 08:30 AM
Post#20



Posts: 266
Joined: 5-February 15



What a great day thanks to you and your hard work !!!

I will definitely select the Compact and Repair option.

As the files are actually moved over to a separate Data Entry share, I may need to create a database for each month and link to them as I do not have a SQL backend...

Have not thought through that yet...I guess we will see once we have a few months under our belt how much storage is required...

Again THANK YOU SO MUCH...

Hope you have an awesome day too woohoo.gif notworthy.gif

Bill
Go to the top of the page
 
2 Pages V  1 2 >


Custom Search


RSSSearch   Top   Lo-Fi    2nd July 2020 - 05:34 PM