A project File listing Drive managment

[AkaTrouble]

Hi
I am a relatively new member to the forums although I have read a few posts before joining

I am a relative newbie to excel projects and VBA but have had my interested peeked in how Awesome both this forum and the capabilities of excel can be.

I have viewed a lot of Utube videos both from Mr excel but mostly Excel is fun this along with reading posts here has taught me there is a community out there that want to share and help.

Ok long winded start to post but this is done because I learn more if I apply lessons to actually achieving something. That is what this thread is going to be about (if allowed by mods to stay)

I am not asking for anyone to do it for me, I want this thread to benefit all those reading it not just me, I want to know what and how we do the things discussed, I also want others to feel free to add suggestions not just solutions, this can be to benefit them or the project or just to add some structure.


Ok synopsis of project
Create a workbook that you can import multiple drive file listings into separate worksheets. To split the information into its component parts such as

DRIVE \ PATH \ FILENAME \ FILE EXTENSION \ (POSSIBLE OTHE META DATA TAG INFORMATION)

Also to be able to combine all off the separate sheets into a master index or at least using look-ups or slicers to create a filtered list or report based on required info as example

5 EXTERNAL DRIVES FILE LISTINGS ON 5 IMPORTED SHEETS TO PULL OUT INFORMATION FROM ALL DRIVES SHOWING ALL EXCEL SPREADSHEETS OR ALL MP3 FILES ETC


Following is how I do this now which could be considered doing it in longhand rather than shorthand

First is to get the file list ( I will explain this in a little detail as may help others solve their own issue)

I create a notepad (TXT) file called filelisting.bat
(This .bat extension means that it is treated as a batch file in CMD prompt and will execute commands inside when double clicked)

Inside this file I type

dir /s /b /a /-c /-p /o:gen >filelisting.txt

Save the file

This file is then copied into either the route folder or the parent folder that I want to create my file listing from, once a copy is in the folder you double click the filelisting.bat, a CMD window will pop up (if not many files in path this could just be a flash) and a new TXT file will be created in the folder called filelisting.txt. This file will contain a complete file listing of the folders and sub-folders.

This file can then be imported into a worksheet in excel

This gives the raw data source inside excel next I use a multiple of left/mid/right/len. commands to split the text string into separate columns of more useful data
(text to columns would seem an obvious choice here but as the main delimiter is “\” and basically the path could contain several of these delimiters but the path itself apart from maybe drive and filename will be only info required to be separate)

After this I proceed to manually manipulate by cut paste sort etc. to arrive at a list


Ok this gives general idea of project. I do not want to go into much more detail as that is the point of this post. I want to learn what I should look for at the beginning of the project how to plan the steps to go forward the suggestions of pitfalls to avoid, none of this advice has to be relevant to this project just in general.


I hope the mods and admin of forum are ok with this post, but more importantly that the community is and joins in I think it could be a great discussion topic that not only I can learn from but others too. Also it is a general topic that portions off could help many and it is not a retail or work project although many of the points can be applied to other projects.


Thanks for reading thus far hopefully will be many replies and many replies that teach and help me and others.

Also feel free to criticise I have broad shoulders

 

[oldbrewer]

I prefer specific single problems to solve or post. I have a separate life, and many other interests. I do not wish to join a community or club. But good luck.