' Demand Explicit Variable declaration
Option Explicit
' A subroutine for extracting data from a text file
Sub testme()
' Create space for local variables
Dim fd As FileDialog ' Create space for folder dialog
Dim myFiles() As String ' Create space for dynamic array
Dim mySamples() As String ' ""
Dim fCtr As Long ' This is short for "File Counter"
Dim sCtr As Long ' This is short for "Sample Counter"
Dim rowN As Long
Dim mySample As String
Dim myFile As String
Dim myPath As String
Dim wkbk As Workbook
Dim wks As Worksheet
' ========== LOOK ====================
' The user is prompted for a directory from which all the files
' are in child directories.
Set fd = Application.FileDialog(msoFileDialogFolderPicker)
With fd
If .Show = -1 Then
myPath = .SelectedItems(1) ' TODO: error handling
End If
End With
Set fd = Nothing
' This conditional checks to make sure myPath ends with a backslash.
' TODO: I might need to use this function later on in the next
' level - using directory paths for report.txt.
If Right(myPath, 1) <> "\" Then
myPath = myPath & "\"
End If
' The way this line works isn't very intuite until after you read the
' excel documentation on the Dir function. Dir can work differently
' each time it is called. If you call Dir() without arguments again,
' it will return a string of the next (different) .txt file in the
' directory. So, calling Dir once only stores the first matching
' .txt file in the directory.
mySample = Dir(myPath & "*.D", vbDirectory)
' This conditional checks to see if there were any .D dirs found
' in the directory specified by "myPath". It does not think to
' check subdirectories.
If mySample = "" Then
MsgBox "No Samples found"
Exit Sub
End If
' If there is atleast one directory, then that directory needs to
' have a \. (I think)
If Right(mySample, 1) <> "\" Then
mySample = mySample & "\"
End If
' Process the matching filenames into the dynamic array "myFiles()".
' The "ReDim Preserve" crap tells VBA to keep the previous values
' in the array intact while it extends the size of the array.
' "myFile" contains the path to the first matching *.D dir in the
' directory. Calling Dir() without arguments, replaces the return
' value to the next wildcard match.
sCtr = 0
Do While mySample <> ""
sCtr = sCtr + 1
If Right(mySample, 1) <> "\" Then ' This is hackish!
mySample = mySample & "\"
End If
ReDim Preserve mySamples(1 To sCtr)
mySamples(sCtr) = mySample
mySample = Dir() ' find the next *.D match
Loop
' Now I should have a dynamic array of .D directories containing
' Report.TXT files. I need to go through each of them, looking
' for that file.
' HELP: I have no idea what this does except that it creates a
' worksheet and then puts three headers in.
Set wks = Workbooks.Add(1).Worksheets(1)
wks.Range("a1").Value = "Found Sample Reports:"
' DEBUGGING: Make sure all the sample directories are being found
' Go through all the .D files in mySamples and print their names
' in the A column.
Dim reportPath As String
rowN = 2
For sCtr = LBound(mySamples) To UBound(mySamples)
reportPath = myPath & mySamples(sCtr) & "Report.TXT"
' ==== Should become new subroutine ====
' Needs: Microsoft Scripting Runtime as reference
Dim reportObject As New Scripting.FileSystemObject
Dim report
Dim lineNum As Long
lineNum = 0
Set report = reportObject.OpenTextFile(reportPath, ForReading)
Do Until report.AtEndOfStream
Dim rLine As String
lineNum = lineNum + 1
rLine = report.ReadLine
If lineNum = 2 Then
Debug.Print rLine
If InStr(rLine, ":") <> 0 Then
Debug.Print lineNum
End If
End If
' Look for Sample Name
Loop
' ==== Should become new subroutine ====
wks.Cells(rowN, 1).Value = reportPath
rowN = rowN + 1
Next sCtr
' TODO this block needs to include the subroutine that adds the
' requested data to the spreadsheet in the requested spots.
' Autofit the used columns to their appropriate size.
wks.UsedRange.Columns.AutoFit
End Sub