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
