Excel VBA Add strings to a list, and sort alphabetically and numerically

Question

Here are the goals for my latest project I'm working on. In a text file, there is information for different tools.

1. Parse a text file and extract a number and a string. DONE. Although, in the final build, I need to to parse through the clipboard.

2. Put these two items into a list of some sort. I have tried attempting to make a dynamic multi-dimensional array so the left would be tool number and the right would be the toolpath name. I couldn't get arrays to work, so I then tried collections. The problem with collections is that there is no sort function, which is what I need. I also learned about dictionaries, which sparked the idea that I may be missing something I could use. Are those three the only things available?

3. Use a list that will determine which item from #2 should be used and determine what position in the list it is. Then use the information to do some tasks.

4. Use the item from #3 and remove it from the #2 list.

5. Repeat goal 2-4 until all items have been removed.

So this is for CNC machining, when saving toolpaths. Normally, the user would organize the order of what tools should go first, second, etc., but I would like to do that all automatically. I know how to navigate the menu using VBA, I just need to figure out how to organize the information so Excel can do it all.

My theory for step 3 is to use a collection in the main list, and search the collection from a term in the CorrectOrder collection. So if it finds it in the main list, I would take action. If not, it would continue down the CorrectOrder collection.

So basically, I need ideas on what I should use for this: collections, arrays, dictionaries, or something else.

Answer 1

Thanks to Byron, I decided to use Excel for the list method and do the sorting. The reason I was avoiding using Excel sheets is because I wanted to reduce the amount of information on my sheet and just use a temporary list. But it is a lot easier and more friendly to use Excel for what it was made to do. Here is the code that reads a text file, extracts what I need, and sorts it. The other Sub uses a list from another text file to define which item in the list goes first, second, and so on.

Sub AUTOTOOLPATHretrieve()
Dim textline As String, ToolNumber As String, Comments As String, i As Long, GreenCount As Long, RoBCount As Long, GreenTools As String, RoBTools As String, GreenTool As Boolean, RoBTool As Boolean

GreenCount = 0
RoBCount = 0
GreenTools = ""
RoBTools = ""
GreenTool = False
RoBTool = False

For X = 1 To 8
Range("I" & X) = " "
Next
Range("J31:J33").ClearContents
Range("Z1:AA50").ClearContents
Open "V:\CNC DEPT\ART CAM\WILL B\WILL PROGRAMS\AUTOTOOLPATH.txt" For Input As #1
On Error Resume Next
i = 0
SAVEDVAR = 0
Do Until EOF(1)
Line Input #1, textline
Debug.Print (textline)

'CHECK LINE FOR ":" AND EXTRACT THAT LINE
Select Case Mid(textline, 1, (InStr(1, textline, ":")) - 1)
    Case "Tool"
        Debug.Print ("CONTAINS TOOL")
        ToolNumber = Mid(textline, 22, 2)
        Select Case True
            Case Mid(ToolNumber, 2, 1) <> "]"
                ToolNumber = Mid(ToolNumber, 2)
                Range("B2").Value = "GREEN"
                GreenCount = GreenCount + 1
                GreenTool = True
                Call GREENSTYLE
            Case Mid(ToolNumber, 2, 1) = "]"
                ToolNumber = Replace(ToolNumber, "]", "")
                Range("B2").Value = "RED or BLUE"
                RoBCount = RoBCount + 1
                RoBTool = True
                Call RoBSTYLE
        End Select
        Select Case ToolNumber
            Case 5
                Range("J31") = Mid(textline, 25, 6)
            Case 6
                Range("J32") = Mid(textline, 25, 6)
            Case 7
                Range("J33") = Mid(textline, 25, 6)
        End Select

        Range("I" & ToolNumber) = ToolNumber

    Case "Comments"
    i = i + 1
        Debug.Print ("CONTAINS COMMENTS")
        Comments = Mid(textline, 21)
        Range("AA" & i).Value = ToolNumber & " " & Comments

        Select Case True
        Case GreenTool = True
                GreenTools = GreenTools & vbNewLine & ToolNumber & " " & Comments
                GreenTool = False
        Case RoBTool = True
                RoBTools = RoBTools & vbNewLine & ToolNumber & " " & Comments
                RoBTool = False
        End Select

    Case "Machining Time"
    Select Case i
        Case Is = 0
    Debug.Print ("CONTAINS MACHINING TIME")
        cb = Mid(textline, 21)
          Select Case cb
          Case Left(cb, 2) = 0
Range("E37") = Mid(cb, 4, 2)
Range("F37") = Mid(cb, 7)
          Case Else
Range("E37") = Mid(cb, 4, 2) + (Left(cb, 2) * 60)
Range("F37") = Mid(cb, 7)
          End Select
          End Select
End Select

Select Case True
Case i = 0 And Left(textline, 3) = "V:\"
    Debug.Print (Mid(textline, 21, 27))
    Debug.Print (Mid(textline, 18, 11))
    Debug.Print (Mid(textline, 35, 20))
    Debug.Print (Mid(textline, 21, 13))
    Select Case True
        Case Mid(textline, 21, 27) = "PRODUCTION PROGRAMS 9-05-07"
            Range("B33") = "PRODUCTION PROGRAMS"
        Case Mid(textline, 18, 11) = "2_Customers"
            Range("B33") = "CUSTOMER FILE"
        Case Mid(textline, 35, 20) = "REPLACEMENT PROGRAMS"
            Range("B33") = "REPLACEMENT PROGRAMS"
        Case Mid(textline, 21, 13) = "CUSTOM SHEETS"
            Range("B33") = "CUSTOM SHEETS"
    End Select
    SAVEDVAR = 1
End Select

Select Case True
Case i > 0 And SAVEDVAR = 0
            MsgBox "NOTICE: File has not been saved. Please save the current ArtCam file to continue.", vbOKOnly
            Close #1
            Exit Sub
End Select
Loop
Close #1

    Select Case True
    Case GreenCount <> 0 And RoBCount <> 0
        Select Case True
            Case GreenCount > RoBCount
                MsgBox "Error: Red and Blue machine tools are used in this program! The tools are: " & RoBTools & vbNewLine & "Fix and try again.", vbExclamation
                Exit Sub
            Case GreenCount < RoBCount
                MsgBox "Error: Green machine tools are used in this program! The tools are: " & GreenTools & vbNewLine & "Fix and try again.", vbExclamation
                Exit Sub
        End Select
End Select

Sleep (100)
Range("AA1:AA50").SORT _
Key1:=Range("AA1"), Order1:=xlAscending
Call AUTOTOOLPATHcompare
End Sub
Sub AUTOTOOLPATHcompare()
Dim textline As String, n As Long
n = 1
Range("Z1:Z50").ClearContents
Open "V:\CNC DEPT\ART CAM\WILL B\WILL PROGRAMS\AUTOTOOLPATHorder.txt" For Input As #1
Do Until EOF(1)
Line Input #1, textline
Debug.Print (textline)
For X = 1 To 50
Select Case Range("AA" & X)
    Case Is <> ""
Select Case textline
Case Is = Range("AA" & X)
Range("Z" & X) = n
Debug.Print (n)
n = n + 1
End Select
End Select
Next
Loop
Close #1
End Sub