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
