Excel: Array/List of Lines from File


Hi, I'm after some assistance on converting some VB.Net code to VBA. The VB.Net Code is: -


        Dim strTextLine As String
        Dim strCurrSystem As String
        Dim strLogFile As String
        Dim strLines As New List(Of String)()

        Dim intFirstPos As Integer
        Dim intLastPos As Integer
        Dim intLength As Integer

        Dim strFiles() As String = System.IO.Directory.GetFiles(strLogPath, "netLog*")

        System.Array.Sort(strFiles, New FileInfoCompare)

        If System.IO.File.Exists(strFiles(strFiles.Length - 1)) Then
            Using FS As New System.IO.FileStream(strFiles(strFiles.Length - 1), IO.FileMode.Open, IO.FileAccess.Read, IO.FileShare.ReadWrite)
                Using MyStreamReader As New System.IO.StreamReader(FS)
                    While (MyStreamReader.Peek() > -1)
                        strLines.Add(MyStreamReader.ReadLine)
                    End While
                    MyStreamReader.Close()
                End Using
            End Using
        Else
            MsgBox("File Not Found")
        End If
        Dim strLine As String

        strLines.Reverse()

        'Array.Reverse(strLines)

        For Each strLine In strLines
            strTextLine = strLine
            If strTextLine.Contains("System:") Then
                intFirstPos = InStr(1, strTextLine, "(", CompareMethod.Text)
                intLastPos = InStr(1, strTextLine, ")", CompareMethod.Text)
                intLength = intLastPos - intFirstPos
                strCurrSystem = Mid(strTextLine, intFirstPos + 1, intLength - 1)
                Exit For
            End If
        Next
I currently have the following code which gets the file that I want to read: -

Option Explicit

Dim strLogFile As String

Sub FindMe()

    Dim iFilesNum As Integer
    Dim iCount As Integer
    Dim recMyFiles() As FoundFileInfo
    Dim blFilesFound As Boolean

    blFilesFound = FindFiles("C:\Users\Andrew\AppData\Local\Frontier_Developments\Products\FORC-FDEV-D-1001\Logs", _
        recMyFiles, iFilesNum, "netLog*", False)
    If blFilesFound Then
        For iCount = 1 To iFilesNum
        Next
    Else
        MsgBox "No file(s) found matching the specified file spec.", _
            vbInformation, "File(s) not Found"
    End If
    
    With recMyFiles(iFilesNum)
        MsgBox "Path:" & vbTab & .sPath & _
            vbNewLine & "Name:" & vbTab & .sName, _
            vbInformation, "Found Files"
        strLogFile = .sPath & .sName
    End With

End Sub
Function used to read files in directory: -

Option Explicit

'*
'* Properties that will be collected for each found file
'*
Type FoundFileInfo
    sPath As String
    sName As String
End Type

Function FindFiles(ByVal sPath As String, _
    ByRef recFoundFiles() As FoundFileInfo, _
    ByRef iFilesFound As Integer, _
    Optional ByVal sFileSpec As String = "*.*", _
    Optional ByVal blIncludeSubFolders As Boolean = False) As Boolean
'
' FindFiles
' ---------
' Finds all files matching the specified file spec starting from the specified path and
' searches sub-folders if required.
'
' Parameters
' ----------
' sPath (String): Start-up folder, e.g. "C:\Users\Username\Documents"
'
' recFoundFiles (User-defined data type): a user-defined dynamic array to store the path
' and name of found files. The dimension of this array is (1 To nnn), where nnn is the
' number of found files. The elements of this array are:
'   .sPath (String) = File path
'   .sName (String) = File name
'
' iFilesFound (Integer): Number of files found.
'
' sFileSpec (String): Optional parameter with default value = "*.*"
'
' blIncludeSubFolders (Boolean): Optional parameter with default value = False
'   (which means sub-folders will not be searched)
'
' Return values
' -------------
' True: One or more files found, therefore
'   recFoundFiles = Array of paths and names of all found files
'   iFilesFound = Number of found files
' False: No files found, therefore
'   iFilesFound = 0
'
' Using the function (sample code)
' --------------------------------
'    Dim iFilesNum As Integer
'    Dim iCount As Integer
'    Dim recMyFiles() As FoundFileInfo
'    Dim blFilesFound As Boolean
'
'    blFilesFound = FindFiles("C:\Users\MBA\Desktop", _
'        recMyFiles, iFilesNum, "*.txt?", True)
'    If blFilesFound Then
'        For iCount = 1 To iFilesNum
'            With recMyFiles(iCount)
'                MsgBox "Path:" & vbTab & .sPath & _
'                    vbNewLine & "Name:" & vbTab & .sName, _
'                    vbInformation, "Found Files"
'            End With
'        Next
'    Else
'        MsgBox "No file(s) found matching the specified file spec.", _
'            vbInformation, "File(s) not Found"
'    End If
'
'
' Constructive comments and Reporting of bugs would be appreciated.
'

    Dim iCount As Integer           '* Multipurpose counter
    Dim sFileName As String         '* Found file name
    '*
    '* FileSystem objects
    Dim oFileSystem As Object, _
        oParentFolder As Object, _
        oFolder As Object, _
        oFile As Object

    Set oFileSystem = CreateObject("Scripting.FileSystemObject")
    On Error Resume Next
    Set oParentFolder = oFileSystem.GetFolder(sPath)
    If oParentFolder Is Nothing Then
        FindFiles = False
        On Error GoTo 0
        Set oParentFolder = Nothing
        Set oFileSystem = Nothing
        Exit Function
    End If
    sPath = IIf(Right(sPath, 1) = "\", sPath, sPath & "\")
    '*
    '* Find files
    sFileName = Dir(sPath & sFileSpec, vbNormal)
    If sFileName <> "" Then
        For Each oFile In oParentFolder.Files
            If LCase(oFile.Name) Like LCase(sFileSpec) Then
                iCount = UBound(recFoundFiles)
                iCount = iCount + 1
                ReDim Preserve recFoundFiles(1 To iCount)
                With recFoundFiles(iCount)
                    .sPath = sPath
                    .sName = oFile.Name
                End With
            End If
        Next oFile
        Set oFile = Nothing         '* Although it is nothing
    End If
    If blIncludeSubFolders Then
        '*
        '* Select next sub-forbers
        For Each oFolder In oParentFolder.SubFolders
            FindFiles oFolder.Path, recFoundFiles, iFilesFound, sFileSpec, blIncludeSubFolders
        Next
    End If
    FindFiles = UBound(recFoundFiles) > 0
    iFilesFound = UBound(recFoundFiles)
    On Error GoTo 0
    '*
    '* Clean-up
    Set oFolder = Nothing           '* Although it is nothing
    Set oParentFolder = Nothing
    Set oFileSystem = Nothing

End Function

Now, the bit I'm struggling to find how to do in Excel VBA is the reading of the file found from the bottom up to check for a match on the line I'm after. In the VB.Net code there is an Array "Dim strLines As New List(Of String)()" which is then Reversed for me to read from the bottom up. How, or rather what do you suggest I do to achieve the same in VBA? All thoughts & help appreciated. Thanks & Regards


This question generated 9 answers. To proceed to the answers, click here.

This thread is current as of May 24, 2015.


For more resources for Microsoft Excel