PDA

View Full Version : VBA to format windows directory listing?



Austin
10-31-2012, 12:19 PM
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
10-31-2012, 12:34 PM
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
10-31-2012, 12:48 PM
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
10-31-2012, 12:57 PM
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
10-31-2012, 01:13 PM
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
10-31-2012, 02:18 PM
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
10-31-2012, 02:51 PM
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
10-31-2012, 03:27 PM
You can zip any file and post it here.

What you are looking for

sub snb()
sn=filter(filter(split(createobject("scripting.filesystemobject").opentextfile("G:\Of\example.txt").readall,vbcrlf),"<",false),",")
sheets(1).cells(1).resize(ubound(sn)+1)=application.transpose(sn)
end sub


Now you can use the method texttocolumns, fixed widths :

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

Kenneth Hobs
10-31-2012, 08:38 PM
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
11-01-2012, 07:41 AM
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.