Macro to strip out items in text file and paste into columns

Hello everyone;

I am looking for a simple macro to extract information from a text file.

I am creating the text file using the following command in command prompt: dir /S *.asf > VideoIndex.txt. This bypasses having to search hundreds of folders and manually extract the files names as it was being done previously.


Within this text file is the following:
------------------------------------------------------------------------------------------------

Volume in drive I is Seagate Backup Plus Drive
Volume Serial Number is 061B-9493

Directory of I:\VisualWorks\Projects\PROJECT\NE-LCP_SINS_2014_LC-SB-034_0001\DATA_20140901191709109

09/01/2014 05:18 PM 470,254,325 20140901191709109@DVR2_Ch1.asf
09/01/2014 05:18 PM 470,462,325 20140901191709500@DVR2_Ch2.asf
09/01/2014 05:18 PM 470,422,331 20140901191709656@DVR2_Ch3.asf
3 File(s) 1,411,138,981 bytes

Directory of I:\VisualWorks\Projects\PROJECT\NE-LCP_SINS_2014_LC-SB-034_0001\DATA_20140901194709813

09/01/2014 05:48 PM 470,390,325 20140901194709813@DVR2_Ch1.asf
09/01/2014 05:48 PM 470,478,331 20140901194710047@DVR2_Ch2.asf
09/01/2014 05:48 PM 470,414,331 20140901194710203@DVR2_Ch3.asf
3 File(s) 1,411,282,987 bytes

Total Files Listed:
445 File(s) 165,834,604,455 bytes
0 Dir(s) 1,850,604,470,272 bytes free

------------------------------------------------------------------------------------------------

I need to extract the the Video File Name, Project Task ID, Date, From Time, To Time and put that into a Excel sheet as shown below.

Please Login or Register  to view this content.

The Video File name is all the .asf extensions
The Project Task ID is contained in the Directory name
The Date is taken from the .asf file name ex. 20140901191709109@DVR2_Ch1.asf, gives me a date of > 20140901

The Times are a bit weird and are extracted from the .asf file name. In this particular case we are taking the UTC time in the file name and subtracting -2.5 hours to get the "From Time", then taking the file name and subtracting -2.0 hours to obtain the "To Time". Each video file should be 30 minutes.
ex. 20140901191709109@DVR2_Ch1.asf, gives a From Time of 16:47:09 and a To time of 17:17:09.


I have a spreadsheet that does this but it uses a complex array of filtering, cell referencing and other formula to derive what I want. It works for me but I am the only one who knows how to use it I would like to make others peoples lives easier if I can and automate this better.

Any help is greatly appreciated.

Hi reesjordan

Will you be so kind as to post your solution?

@ reesjordan
@ LJMetzger

I had also developed a solution but as I saw the Thread was marked SOLVED I didn't post it...the Code is in the attached.