Get FileName Function

[JohnLee]

Good morning folks,

I have the code below which I obtain via this forum and modified to suit my needs and it works fine. However, I now have a need to go a step further with another aspect of my project and I need some help in modifying the code below to achieve this new requirement. Let me explain…………….

[SIZE=3][FONT=Times New Roman][COLOR=blue]Function[/COLOR] ImportFolderList()[/FONT][/SIZE]
[FONT=Times New Roman][SIZE=3] [/SIZE][/FONT]
[SIZE=3][FONT=Times New Roman][COLOR=darkgreen]'This code gets the name of each file in the specified folder and adds[/COLOR][/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman][COLOR=darkgreen]'the name to the tblFiles table[/COLOR].[/FONT][/SIZE]
[FONT=Times New Roman][SIZE=3] [/SIZE][/FONT]
[SIZE=3][FONT=Times New Roman][COLOR=blue]Dim[/COLOR] fs, f, f1, fc, s[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman][COLOR=blue]Dim[/COLOR] rs [COLOR=blue]As[/COLOR] DAO.Recordset[/FONT][/SIZE]
[FONT=Times New Roman][SIZE=3] [/SIZE][/FONT]
[SIZE=3][FONT=Times New Roman][COLOR=blue]Set[/COLOR] rs = CurrentDb.OpenRecordset("tblFiles")[/FONT][/SIZE]
[FONT=Times New Roman][SIZE=3] [/SIZE][/FONT]
[SIZE=3][FONT=Times New Roman]folderspec = "G:\Scan - Verify\eFlow\BackupProdRegOnprdfs01\aeg.fof"[/FONT][/SIZE]
[FONT=Times New Roman][SIZE=3] [/SIZE][/FONT]
[SIZE=3][FONT=Times New Roman][COLOR=blue]Set[/COLOR] fs = CreateObject("Scripting.FileSystemObject")[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman][COLOR=blue]Set[/COLOR] f = fs.GetFolder(folderspec)[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman][COLOR=blue]Set[/COLOR] fc = f.Files[/FONT][/SIZE]
[FONT=Times New Roman][SIZE=3] [/SIZE][/FONT]
[SIZE=3][FONT=Times New Roman][COLOR=blue]For[/COLOR] Each f1 In fc[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]    rs.AddNew[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]    [COLOR=darkgreen]'Updates the strFileName field with the full path and file name[/COLOR][/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]    rs.Fields("strFileName") = "G:\Scan - Verify\eFlow\BackupProdRegOnprdfs01\aeg.fof\" & f1.Name[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]    [COLOR=darkgreen]'Updates teh dtmDatecreated field with teh date the file was created.[/COLOR][/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]    rs.Fields("dtmDateCreated") = Format(f1.DateCreated, "dd/mm/yyyy")[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]    rs.Update[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman][COLOR=blue]Next[/COLOR][/FONT][/SIZE]
[FONT=Times New Roman][SIZE=3] [/SIZE][/FONT]
[SIZE=3][FONT=Times New Roman][COLOR=blue]Set[/COLOR] rs = [COLOR=blue]Nothing[/COLOR][/FONT][/SIZE]
[FONT=Times New Roman][SIZE=3] [/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=blue]End Function[/COLOR][/SIZE][/FONT]

I have a directory in which a number of specified folders will permanently sit and are named as follows:

Cash Batches
Error Batches
Extended Cashless Business
Instant Take Ups Cash
Instant Take Ups Cashless
Mandates
Miele

Each of the above folders will have a subfolder which will be in a date format as follows ddmmyy [130611]
And each of these folders will have a subfolder which could be named anything, and they in turn will have any number of text files, which also could be named anything. For instance:

G:\Archiving\Mandates\130611\aeg.txt

So what I would like to be able to do is to modify the above code to cater for the different folder levels and get the text file name for each text file in the bottom folder level i.e. the date folder [130611].

I have read up on some information written by a Chip Pearson [www.cpearson.com] who wrote a “Get FileName” function, but I am struggling work out how to use what he wrote in my requirement.

Any assistance would be most appreciated.

John

 

[JamesMcS]

Bob Larson kindly helped me out with something similar in this thread:http://www.access-programmers.co.uk/forums/showthread.php?t=211105&goto=newpost

 

[JohnLee]

Thanks James,

The pointer was a great help.

John