Programming VBA: Reading excel into an Array

[kharri5]

Hello again,

I posted before about reading values from a delimited text file into a class with string properties, and then jamming that class into a dynamic array. It turns out that reading from the text file (which will be updated and contain about 500 lines to start) is a hideous complicated looking process for anyone updating my project for the future

if I had to stare at 500 lines like this

NA,Required,noSub,Policies and Procedures,94,$HasApplication - Policies and Procedures,P

I'd kill me too

My question is (and I know Im a total nub here) if I have this data in excel already

How do I

1) Open the worksheet
2) Read row by row and segment each column to the custom class's data member variables
3) Put that class into the dynamic array (which I think I know how to do, but maybe it's different when reading in excel I dunno)
4) know when to stop if the amount of rows increases

The custom class has these data members each representing a column of the excel sheet

Public Response As String
Public Category As String
Public SubCat As String
Public Title As String
Public Id As String
Public Enttlmnt As String
Public EntType As String

For question 4 I thought maybe I could start the 'A' cell of whatever number row it's supposed to stop to something like "$$$$" and then look for that to break a while loop, but again I don't even know how the while loop should look to start...I've looked online to no avail at all. All things reference reading from a text file into and array in VBA not reading from XLS into an array in VBA.

Please help me?

kharri5

[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,

[yarfangor]

Hi There, I am in a programming class and I'm having some trouble with how to go about my program.

Initially I have an Excel Spread sheet about 4 sheets, and they have information of Hockey Players (goals, points, games played etc...) and I want to put it into excel so I can use it as a database.

I wanted my program to have the user choose 3 players from each division from the list of players I had put. And then add up the total number of points they would get from those character selections. How would i go about doing this?

Thanks for the help.!