Auto classify external files into the folder

[gstylianou]

Good morning,

I need the help of the most experienced members for this problem:

In my database I can read the external files of each of my clients directly from their own folder as following example C: \ Program Files \ MyDatabase \ Common \ Customers \ 001 and then i can see them in a list (datasheet view form) for each client.

What I want to do is the following:
Because everyday I receive mails from each department with client files (word, excel, pdf etc) and each file is named with the client code (ex. 001_13012020_1.docx) i want automate this procedure so to classify the files into the folder of each customer without doing this manually.

Has anyone done something similar and can help me with an example?

thanks in advanced

 

[theDBguy]

Hi. Is the middle number the client number? If so, have you tried using the Split() function?

 

[jdraw]

001_13012020_1.docx

Is this the proper interpretation of your naming convention

001------------------------------Customer identification
13012020-----------------------Document date 13/Jan/2020
1---------------------------------Sequence number for this type of doc, this cust, this date
docx-----------------------------Document type

 

[The_Doc_Man]

If the beginning of the filename is the customer number, then theDBguy's suggestion would be useful. Is that number fixed in size or can it vary?

In overview, you could do the following:

1. Find each file. This could be done with the File System Object and its .GetFolder method that gives you an enumeration of every file in the named folder via the .Files collection. If you are not sure, here are the relevant documents.

https://docs.microsoft.com/en-us/of...e/user-interface-help/filesystemobject-object

https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/folder-object

https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/files-property

2. Take the file name apart using either the Split function if the first section is variable in length or the Left function if fixed in length. There are some methods within the FSO that can decompose a file spec into its parts, so this isn't that hard to do. See, for example, the .GetFileName method of the FSO, which you can find from the links above.

3. Synthesize a path string (probably starting from a fixed base) by tacking on the prefix from step 2. Using concatenation, make

NewPath= BasePath & "\" & prefix & "\" & filename

4. You already have the original filespec from step 2. Use the FSO.Move method to relocate that file for you from the old to the new path.

The example for the .Files collection shows a loop enumerating the files. This means that all of these steps (1-4) would be inside the loop performing that enumeration.

 

[gstylianou]

001_13012020_1.docx

Is this the proper interpretation of your naming convention

001------------------------------Customer identification
13012020-----------------------Document date 13/Jan/2020
1---------------------------------Sequence number for this type of doc, this cust, this date
docx-----------------------------Document type

Exactly this coding..!!

 

[jdraw]

Great. So given the example what exactly would be the desired result based on your
classify the files into the folder of each customer without doing this manually.
Maybe I'm missing the obvious, but I'd like to see the result you want. Doc has provided info on how.

 

[theDBguy]

Exactly this coding..!!

Hi. Thanks for the clarification. I think the Split() function still applies. Have you tried it?

 

[gstylianou]

Hello again,

Attached is the database with master folder (C:\) and all what i did for the moment without any result

I will be glad if someone can help me to modify the example in order to work

Note: Put the main folder into C_Drive

Thanks