Auto Number from Folder Name

Question

Hello, 

I am trying to determine if the following is possible...

Create an employee ID number by reading existing folders.  The employee folders will be located at C:\Folder01\Folder02...

In Folder02, each employee will get their own folder labelled as follows:

Simpson, Homer J. (YYPPAAA##)

Last Name, First N. (YYPPAAA##)

YY - Last 2 digits of Year

PP - 2 digit Pay Period Number

AAA - Location Number

would be what would need to be read and increase by 1 as needed.  It would just reset after 99 and go back to the start.

I can get the YYPPAAA by using formulas.  Reading the last 2 digits of existing folders is where I need help.

The numbering system when working would be as follows:

19 (Year 2019)

10 Pay Period 10 (formula works by increasing every two weeks based on dates.)

014 - Location Number

01 ---99 (inclusive).  It will not need to reset because of a change in YY/PP/AAA.  Just count until 99 then back to 01.  Part of the formula would require the vba to look at the existing folder's "Created" date to determine the most recent folder - read and then add 1.

Help on this would be appreciated.

Thank you,

K.

Answer 1

Hi Kala Sha Jahan

Please find in the link below a file containing my approach to find a solution to your problem

https://www.dropbox.com/s/j92a8l000ft83qmvs/MakeEmployeesFoldersWeb.xlsm?dl=0

Check picture below

If this answer would be the solution to your question, I'll appreciate you mark it as answered

Do let me know if you require any further help on this. 

Thanks

Regards

Jeovany CV

Answer 2

Good evening, after some more research - i found this, and have reposted here:

Adjust Macro from Extended Office to List All Folders in Excel with Date Created

thank you,

K.

Answer 3

This gives me a good idea to work from.  Sometimes - that's were my brain just needs a different perspective to solve a simple problem.

Thank you,

K.

Answer 4

Try this one and let me know how it goes

https://www.dropbox.com/s/j92a8l0ft83qmvs/MakeEmployeesFoldersWeb.xlsm?dl=0

Answer 5

Good morning,

the link did not work - keep getting a 404 error.

thank you,

K.