How to read excel cells into Array

[kharri5]

I figured it out! Huzzah!

In order to grab data from cells of an excel spreadsheet from an outside excel app into PPT WORD, or other office apps, jam that data into a custom class and jam that into an array of CustomClas type you do the following:

Public permList() As CustmClas

Function GetDataFromXL()
    
    Dim sFileName As String
    Dim srcWb As Object 'the source workbook file
    Dim curEnt As New CustmClas
    Dim weDone As Boolean
    
    'For parsiing the line into separate strings
    Dim lineCount As Integer
    Dim i As Integer
    lineCount = 1
    weDone = False
    
    ' edit this:
    sFileName = REPORT_DATA_FILE

    ' does the file exist?  simpleminded test:
    If Len(Dir$(sFileName)) = 0 Then
        MsgBox ("You didn't input a file at all. Please make sure the package was unzipped to the C: drive directly")
        Exit Function
    End If
    
    'Essentially opens the excel workbook to powerpoint's (or other office apps) eyes
    Set srcWb = ApExcel.Workbooks.Open(REPORT_DATA_FILE) 'add a new workbook to the application excel
    
    'while not at the end of the file
    Do While weDone = False
        'This is crucial to set it to nothing here. It overwrites to the last value
        'in every array position if you don't
        Set curEnt = Nothing
        'up the line counter to one (we use this for the dynamic array's size)
        're dimension preserve the array to up its size by 1 more in prep for a new item
        'as well as keep all the items its accumulated so far
        
        'easiest delimeter for the excels end
        If srcWb.WorkSheets.item("Sheet1").Cells(lineCount, 1).value = "$$$$$" Then
            weDone = True
        End If
        
        ReDim Preserve permList(lineCount - 1) As CustmClas
        
        'WE NEED SOMETHING LIKE THIS??

        curEnt.Response = srcWb.WorkSheets.item("Sheet1").Cells(lineCount, 1).value
        curEnt.Category = srcWb.WorkSheets.item("Sheet1").Cells(lineCount, 2).value
        curEnt.SubCat = srcWb.WorkSheets.item("Sheet1").Cells(lineCount, 3).value
        curEnt.Title = srcWb.WorkSheets.item("Sheet1").Cells(lineCount, 4).value
        curEnt.Id = srcWb.WorkSheets.item("Sheet1").Cells(lineCount, 5).value
        curEnt.Enttlmnt = srcWb.WorkSheets.item("Sheet1").Cells(lineCount, 6).value
        curEnt.EntType = srcWb.WorkSheets.item("Sheet1").Cells(lineCount, 7).value
        
        'important for object arrays
        Set permList(lineCount - 1) = curEnt
        lineCount = lineCount + 1
        
    Loop ' we've reached the end of the file now
    
    'do crap with array permList

     'don't forget to clean up your garbage. Nobody likes a hog
     CloseAll

End Function

Sub NAR(ByVal o As Object)
    Try
      System.Runtime.InteropServices.Marshal.ReleaseComObject (o)
    Catch
      o = Nothing
End Sub

Sub CloseAll()

    Erase permList
    ApExcel.Quit
    NAR (OjbWb)
    NAR (ApExcel)

End Sub

The reason primarily why I ask for help is to see if I can get a push in the right direction without having to scour the internet for solutions (which are surprisingly hard to find as most questions are so specific to each person's problem, this one being no exception) Anywho, I figure when I find the solution myself I should share it with the world, so here is my solution this problem in VBA coding of Macros for another office app.

kharri5,