Need to be able to iteratively add strings to a variable length data structure (string array?) RRS feed

  • Question

  • I have some code that examines a directory for text files that start with the letter "l" and the letter "s". I need to have the file names stored in one of two separate data structures, one for the "l" files and one for the "s" files. This code is in a module and can't rely on a Form object to store the data, although a list would be perfect. I can't process the file name as I'm grabbing it as I need all of the "l" files processed prior to working on any of the "s" files. I can think of a couple different workarounds for this that would probably work but none of them seem satisfactory when there is probably a much more elegant solution. I could create a couple temporary tables but that sounds like overkill. I could append each file name into a delimited string and then use Split to break it into a string array, but this seems very sloppy. The best solution that comes to mind would be iterate through the file list twice, processing each file in situ rather than stuffing them into another set of data structures. However, the larger point is that this reveals that I don't know how to do something that seems it should be relatively easy yet important. I feel as if there should be a way to either accomplish this with a string array or some other data structure. I've included some code below that gives a good impression of what I'm trying to accomplish although the code contains some errors as it is making improper use of the string arrays LotList and ShipList:

    Public Sub Import(FilePath As String)
        Dim fs, fl, f As Variant
        Dim ext, trans As String
        Dim LotList() As String
        Dim ShipList() As String
        Dim Lot, Ship As String
        Set fs = CreateObject("Scripting.FileSystemobject")
        Set fl = fs.GetFolder(FilePath)
        Set f = fl.Files
        For Each X In f
            ext = Right(X.Name, 3)
            trans = Left(X.Name, 1)
            If ext = "txt" Then
                If trans = "l" Then
                    LotList.Add (X.Name)
                End If
                If trans = "s" Then
                    ShipList.Add (X.Name)
                End If
            End If
        For Each Lot In LotList
            ImportLot (FilePath & "\" & Lot)
        For Each Ship In ShipList
            ImportShip (FilePath & "\" & Ship)
    End Sub

    So without relying on any workarounds, is there a way to get a string array or some other data structure to behave in the way that I want?

    Friday, May 22, 2015 11:00 PM


  • Re:  store text in string arrays

    Code tested in Microsoft Excel ...

    Public Sub Import_R1(FilePath As String)
    'Jim Cone - Portland, Oregon USA - May 2015
        Dim fs As Object
        Dim fl As Object
        Dim f  As Object
        Dim x  As Object
        Dim M     As Long
        Dim N     As Long
        Dim trans As Long
        Dim ext   As String
        Dim Ship  As String
        Dim sName As String
        Dim LotList()  As String
        Dim ShipList() As String

        Set fs = CreateObject("Scripting.FileSystemObject")
        Set fl = fs.GetFolder(FilePath)
        Set f = fl.Files

       'Use value larger then expected count
        N = 200
        ReDim LotList(1 To N)
        ReDim ShipList(1 To N)
        M = 1
        N = 1

       'Overwritten later if files exist
        LotList(M) = "none"
        ShipList(N) = "none"

        For Each x In f
           sName = x.Name
           ext = VBA.Right$(sName, 3)
           If ext = "txt" Then
             trans = VBA.Asc(sName)
            'See 'Character Codes' in Office help for full list
             If trans = 108 Then     '108 is l, 76 is L, 82 is R
               LotList(M) = sName
               M = M + 1
             ElseIf trans = 115 Then '115 is s, 83 is S
               ShipList(N) = sName
               N = N + 1
             End If
            End If

       'You can only resize the last array dimension
        ReDim Preserve LotList(1 To M)
        ReDim Preserve ShipList(1 To N)

       'USED FOR TESTING - adds to the Excel active sheet
        Range(Cells(1, 1), Cells(M, 1)).Value = Application.Transpose(LotList)
        Range(Cells(1, 2), Cells(N, 2)).Value = Application.Transpose(ShipList)

       'Some other option - your choice, your method
        'For Each Lot In LotList
        '    ImportLot (FilePath & "\" & Lot)

        'For Each Ship In ShipList
        '    ImportShip (FilePath & "\" & Ship)

    Set x = Nothing
    Set f = Nothing
    Set fl = Nothing
    Set fs = Nothing
    End Sub

    Saturday, May 23, 2015 12:45 AM