hi John can you attach the notepad file and output "excel file"
On Mon, Sep 19, 2011 at 6:40 PM, johnofeasttex <j...@demco-mfg.com> wrote: > Can anyone show me how to import the first 2 rows of data from the > text file into excel as the following code builds the list? > > Thank You for ANY help.. > > John > > On Sep 16, 2:04 pm, johnofeasttex <j...@demco-mfg.com> wrote: > > 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 -- 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 > -- *Regards* * * *Ashish Koul* *http://www.excelvbamacros.com/* P Before printing, think about the environment. -- ---------------------------------------------------------------------------------- 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