VBA to format windows directory listing?

[Austin]

So, I've been trying to make a Macro that will format a windows directory command from the raw text file. Has anyone ever done this? I've tried searching all over the place because I figured someone must've done this, but it doesn't appear to be the case.

I simply want to take the command (from windows prompt) 'dir /s > output.txt' and then parse it into a format that allows you to see the date/time in one column, filename in another column, and directory in another column. That would allow you to filter on name, date, or folder.

I should probably post my code, but it is awful from just recording a macro. It doesn't repeat if the folders change.

Edit: I should also mention that I don't have a way to change how the raw files are produced. I've found ways in excel to read the directory into a better format directly, but these files are provided externally from other people.

[Kenneth Hobs]

You don't need a text file to store the results of a DOS shell command. See http://www.vbaexpress.com/forum/showthread.php?t=44206

If you type cmd and press Enter key in the Start window, you will be in the DOS shell. Type Help DIR and press Enter key to get help. You can see that /od means order by date.

[Austin]

I realize this, the issue is that I'm not the one running the directory commands. Also, I need excels filtering capabilities. The goal isn't to change the order of files based on date.

[Kenneth Hobs]

If you are not the one running the DIR commands then you could wind up with most anything or nothing at all.

It is a big kludge to not just do the task in VBA to begin with. Post an example text file and Excel file formatted the way you want and we will try to apply a sledge hammer to it.

[Austin]

I agree it is a little bit like trying to fit a square peg in a round hole, but we receive file lists in this format all the time. I know a lot of other people in my industry that have the same issue with formatting these types of files and they will find this extremely useful if it is possible.

It will only let me upload one file at a time. The text file looks like this:

 Directory of C:\Program Files (x86)\TriZetto\Facets

10/16/2012  04:58 PM    <DIR>          .
10/16/2012  04:58 PM    <DIR>          ..
02/17/2012  03:40 PM    <DIR>          481
10/16/2012  04:58 PM                 0 facets.txt
10/16/2012  04:58 PM                26 info.bat
               2 File(s)             26 bytes

 Directory of C:\Program Files (x86)\TriZetto\Facets\481

02/17/2012  03:40 PM    <DIR>          .
02/17/2012  03:40 PM    <DIR>          ..
02/17/2012  03:41 PM    <DIR>          System
02/17/2012  03:40 PM    <DIR>          Utilities
               0 File(s)              0 bytes

 Directory of C:\Program Files (x86)\TriZetto\Facets\481\System

02/17/2012  03:41 PM    <DIR>          .
02/17/2012  03:41 PM    <DIR>          ..
09/06/2012  05:57 PM    <DIR>          Bin
09/17/2012  09:44 AM    <DIR>          Config
09/13/2012  03:33 PM    <DIR>          Hlp
02/17/2012  03:40 PM    <DIR>          Setup
09/17/2012  10:21 AM    <DIR>          Work
               0 File(s)              0 bytes

 Directory of C:\Program Files (x86)\TriZetto\Facets\481\System\Bin

09/06/2012  05:57 PM    <DIR>          .
09/06/2012  05:57 PM    <DIR>          ..
02/22/2012  11:35 AM           323,584 armldlg0481.dll
02/29/2012  01:30 PM           417,792 armlwin0481.dll
06/27/2012  12:07 PM           180,224 bpaldlg0481.dll
06/07/2012  11:55 AM           110,592 bpalqry0481.dll
11/01/2010  02:09 AM           421,888 bpalrec0481.dll
06/20/2012  12:30 PM           233,472 bpalwin0481.dll
11/01/2010  01:51 AM            26,624 c32autil481.exe
11/01/2010  02:13 AM           233,472 caclapp0481.dll
07/18/2012  05:28 PM           720,896 caclbat0481.dll
11/01/2010  02:05 AM           237,568 caclqry0481.dll
11/01/2010  01:57 AM         1,814,528 caclrec0481.dll
03/18/2011  02:20 PM           925,696 caclwin0481.dll
01/12/2011  03:50 PM         1,142,784 caplwin0481.dll
05/16/2012  07:32 PM           462,848 caslapp0481.dll
06/13/2012  07:02 PM         1,703,936 caslwin0481.dll
07/25/2012  03:17 PM         1,269,760 cbclwin0481.dll
07/18/2012  05:29 PM         3,338,240 cbllapp0481.dll 

[Kenneth Hobs]

To make sure that you get the best solution, go ahead and attach the text file. For future posts of multiple files, zip the file and post the zip file.

[Austin]

That file format is disallowed on these forums.

Valid file extensions: bmp csv doc docm docx gif jpeg jpg png xls xlsm xlsx zip

[snb]

You can zip any file and post it here.

What you are looking for
[vba]
sub snb()
sn=filter(filter(split(createobject("scripting.filesystemobject").opentextf ile("G:\Of\example.txt").readall,vbcrlf),"<",false),",")
sheets(1).cells(1).resize(ubound(sn)+1)=application.transpose(sn)
end sub
[/vba]

Now you can use the method texttocolumns, fixed widths :

first column : 20 characters,
second column: 15 characters
third column: >35

[Kenneth Hobs]

For the part the snb said about the textcolumns, you can record a macro for that or we can help there too.

Do you not know how to zip files? Most Windows has zip. Right the file(s) in Explorer and send to a zip file. Of course a csv file is a text file. Just rename it to fool the forum.

[Austin]

This appears to have worked, but the tricky part is including the directory that each file is within. The directory information is at the top of each set of files.

This is the part that I haven't been able to automate. It has required me to make a crazy formula and then run some excel tricks to copy it down.