I searched around and found some code that allows me to create a list of the files in a given directory. I just need to figure out how to pull the comment data from the file...... any suggestions? What I have so far looks like this:
Option Explicit Sub GetFileList() Dim strFolder As String Dim varFileList As Variant Dim FSO As Object, myFile As Object Dim myResults As Variant Dim l As Long ' Get the directory from the user With Application.FileDialog(msoFileDialogFolderPicker) .Show If .SelectedItems.Count = 0 Then Exit Sub 'user cancelled strFolder = .SelectedItems(1) End With ' Get a list of all the files in this directory. ' Note that this isn't recursive... although it could be... varFileList = fcnGetFileList(strFolder) If Not IsArray(varFileList) Then MsgBox "No files found.", vbInformation Exit Sub End If ' Now let's get all the details for these files ' and place them into an array so it's quick to dump to XL. ReDim myResults(0 To UBound(varFileList) + 1, 0 To 5) ' place make some headers in the array myResults(0, 0) = "Filename" myResults(0, 1) = "Size" myResults(0, 2) = "Created" myResults(0, 3) = "Modified" myResults(0, 4) = "Accessed" myResults(0, 5) = "Full path" Set FSO = CreateObject("Scripting.FileSystemObject") ' Loop through our files For l = 0 To UBound(varFileList) Set myFile = FSO.GetFile(CStr(varFileList(l))) myResults(l + 1, 0) = CStr(varFileList(l)) myResults(l + 1, 1) = myFile.Size myResults(l + 1, 2) = myFile.DateCreated myResults(l + 1, 3) = myFile.DateLastModified myResults(l + 1, 4) = myFile.DateLastAccessed myResults(l + 1, 5) = myFile.path Next l ' Dump these to a worksheet fcnDumpToWorksheet myResults 'tidy up Set myFile = Nothing Set FSO = Nothing End Sub Private Function fcnGetFileList(ByVal strPath As String, Optional strFilter As String) As Variant ' Returns a one dimensional array with filenames ' Otherwise returns False Dim f As String Dim i As Integer Dim FileList() As String If strFilter = "" Then strFilter = "*.*" Select Case Right$(strPath, 1) Case "\", "/" strPath = Left$(strPath, Len(strPath) - 1) End Select ReDim Preserve FileList(0) f = Dir$(strPath & "\" & strFilter) Do While Len(f) > 0 ReDim Preserve FileList(i) As String FileList(i) = f i = i + 1 f = Dir$() Loop If FileList(0) <> Empty Then fcnGetFileList = FileList Else fcnGetFileList = False End If End Function Private Sub fcnDumpToWorksheet(varData As Variant, Optional mySh As Worksheet) Dim iSheetsInNew As Integer Dim sh As Worksheet, wb As Workbook Dim myColumnHeaders() As String Dim l As Long, NoOfRows As Long If mySh Is Nothing Then 'make a workbook if we didn't get a worksheet iSheetsInNew = Application.SheetsInNewWorkbook Application.SheetsInNewWorkbook = 1 Set wb = Application.Workbooks.Add Application.SheetsInNewWorkbook = iSheetsInNew Set sh = wb.Sheets(1) Else Set mySh = sh End If With sh Range(.Cells(1, 1), .Cells(UBound(varData, 1) + 1, UBound(varData, 2) + 1)) = varData .UsedRange.Columns.AutoFit End With Set sh = Nothing Set wb = Nothing End Sub This I can modify for my purpose and appears to work good. Thanks John On Sep 15, 2:48 pm, johnofeasttex <j...@demco-mfg.com> wrote: > Hi Noorain, > > The following are two examples of the text files that would be > encountered. I have only shown the first and last 4 rows of the files. > The over all length of the files is very variable but hold to the same > format. Some files may end in *.out, *.txt or just *.. The files > should all begin and end with the percent sign. There may be blank > rows between data rows. Is this what you ask? > > 1st file "3007.out" > % > N0010 O3007 (UNDERSIDE OF REMOTE COVER) > N0020 G0 G17 G40 G80 G90 H0 E0 > N0030 M6 T1 (0.750 DIA - END MILL) > N0040 M3 S650 > ......... > N2290 E0 H0 X0 Y0 Z0 > N2300 G53 X0 Y9.75 > N2310 M0 > N2320 M2 > % > > 2nd file "8228." > % > N1O8228(790 SMART CHUCK CAMDEN) > N2M6T1 > N3M3S1200 > N4G0G90 > ......... > N73E0H0X0Y0Z0 > N74G53Y9.75 > N75M0 > N76M2 > % > > The desired output would be like: > > File ID Comment > 3007.out 3007 UNDERSIDE OF REMOTE COVER > 8228. 8228 790 SMART CHUCK CAMDEN > > The ID is always after the letter O. > The comment is always between the perentheses. > > Thanks for looking at it. > > John > > On Sep 15, 12:52 pm, NOORAIN ANSARI <noorain.ans...@gmail.com> wrote: > > > > > Dear John, > > > Please share sample sheet with us > > > On Thu, Sep 15, 2011 at 9:39 PM, johnofeasttex <j...@demco-mfg.com> wrote: > > > Hello, > > > I need to look in individual directories of text files and create a > > > list in Excel showing all files in that directory in one column with > > > data from the first row starting with the letter "N" parsed into the > > > adjacent 2 columns. The data will be of various lengths but the format > > > will look like "N1O8214(CUT RH MAG4)". There may oy may not be spaces > > > in this row. The first column would be the file name, the second > > > column would be the "O" word from the first "N" row, in this case > > > "8214". The third row would be the comment in the brakets, in this > > > case "CUT RH MAG4". I have some experience with macros and basic > > > programming but have very little experience with file data extracting > > > and parsing. > > > > Thanks > > > > -- > > > > ---------------------------------------------------------------------------------- > > > Some important links for excel users: > > > 1. Follow us on TWITTER for tips tricks and links : > > >http://twitter.com/exceldailytip > > > 2. Join our LinkedIN group @http://www.linkedin.com/groups?gid=1871310 > > > 3. Excel tutorials athttp://www.excel-macros.blogspot.com > > > 4. Learn VBA Macros athttp://www.quickvba.blogspot.com > > > 5. Excel Tips and Tricks athttp://exceldailytip.blogspot.com > > > > To post to this group, send email to excel-macros@googlegroups.com > > > > <><><><><><><><><><><><><><><><><><><><><><> > > > Like our page on facebook , Just follow below link > > >http://www.facebook.com/discussexcel > > > -- > > Thanks & regards, > > Noorain Ansari > > *http://excelmacroworld.blogspot.com/*<http://excelmacroworld.blogspot.com/> > > *http://noorain-ansari.blogspot.com/*<http://noorain-ansari.blogspot.com/>- > > Hide quoted text - > > > - Show quoted text -- Hide quoted text - > > - Show quoted text - -- ---------------------------------------------------------------------------------- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel