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
