VBA - Getting Data from an Excel Worksheet and Creating a Collection of Custom (Book) objects

Try something like this:

Dim objExcel As New Excel.Application
Dim exWb As Excel.Workbook
'define an excel range
Dim xlR As Excel.Range

Dim bookTitle As String
Dim bookAutor As String


Dim myBooks As Collection
Set myBooks = New Collection


Set exWb = objExcel.Workbooks.Open("G:\MyFolder\BookShelf.xlsx")


Set excelWorkBook = objExcel.Workbooks.Open("G:\Dr Ahmad\Handover\QuickWord.xlsx")

' I need code in this region which could run some kind of Loop on excelWorkBook  and give me a collection of book objects
' creating a book from all the records in Sheet1 of excelWorkBook such that the value in column 1 of each row will be the title of
'  the book and the value in column 2 of each row will be the author of the book.

'set range to first cell in some worksheet
Set xlR = exWb.Worksheets(1).Cells(1, 1)
'loop through rows
Do
'add first column as value, second as key
myBooks.Add xlR, xlR.Offset(0, 1)
'next row
Set xlR = xlR.Offset(1, 0)
Loop Until xlR = ""
exWb.Close

Set exWb = Nothing

Select allOpen in new window

Why are you opening two workbooks in your code?  Why do you need line 15?

@Martin Liss
Line 15 in my code has been added by mistake.

@faheemAhmadGul .. yes I figured it was probably a relic of an earlier version.

Thank you experts for your comments.
What I would like to do will something like this:
As you would note my code is creating two Book objects, getting data from my Excel Workbook. However, I my code I am kind of manually creating two Book Objects. I wondering if there is way to do this running a loop on exWb and creating as many books as there are records in the Sheet name First (in my code).

Private Sub BookTest()

Dim objExcel As New Excel.Application
Dim exWb As Excel.Workbook

Dim bookTitle As String
Dim bookAutor As String

Dim book2Title As String
Dim book2Autor As String


Dim myBooks As Collection
Set myBooks = New Collection
Dim numbers As Integer

Dim i As Integer
i = 1


Set exWb = objExcel.Workbooks.Open("G:\MyFolder\BookShelf.xlsx")

bookTitle = exWb.Sheets("First").Cells(i, 1)
bookAutor = exWb.Sheets("First").Cells(i, 2)

i = 2
book2Title = exWb.Sheets("First").Cells(i, 1)
book2Autor = exWb.Sheets("First").Cells(i, 2)
exWb.Close

Set exWb = Nothing

Dim b1 As New Book
b1.Title = bookTitle
b1.Author = bookAutor

Dim b2 As New Book
b2.Title = book2Title
b2.Author = "book2Title"

myBooks.Add b1, "a1"
myBooks.Add b2, "a2"


Debug.Print myBooks("a1").Title
End Sub

Select allOpen in new window

The above code gives me the following result.

If you change the several occurrences of "Sheet1" in my code to "First", it should work for you.

@Martin Liss
You code gives Subscript out of range error on Line 24.
Would it make coding this easier if I aleady know how many records there are in my Worksheet?  In my case I would know the number of records in my Worksheet that would need to be processed.

In line 24 did you change "Sheet1" to "First" as I suggested?

@Martin Liss
Yes, after changing "Sheet1" to "First" the code is working. Before I closed the question could you please show me, how I could I pull a book out of this collection. And if it is possible to use some kind of filter on this collection to pull out a kind of sub Collection from myBooks based on some criteria - such all Books from myBooks where the author is Jane Auston.

Change the Test code as shown.

'Test
Dim lngEntry As Long
Dim strParts() As String
For lngEntry = 1 To myBooks.Count
    'Debug.Print Split(myBooks(lngEntry), "|")(0) & " by " & Split(myBooks(lngEntry), "|")(1)
    strParts = Split(myBooks(lngEntry), "|")
    If strParts(1) = "Jane Austin" Then
        Debug.Print strParts(0) & " by " & strParts(1)
    End If
Next

Select allOpen in new window

@at Marin Liss
Thank you for your ongoing support. The code is working fine and I can see the collection is being created. However, being a beginner with VBA I am still unclear how I can pull a book out of the collection myBooks.  Just to clarify if I wanted to create a Book at the end of the test code, such that the Title of this book would be equal to the Title of the first book in myBooks, and Author of the this Book will be Author of the first Book in myBooks how could I achieve that.  I have put your test code above my code in the the code sample below just to illustrate where I want to creat a new book. I am creating this just to help me understand how to pull a book out of myBooks.

'Test
Dim lngEntry As Long
Dim strParts() As String
For lngEntry = 1 To myBooks.Count
    'Debug.Print Split(myBooks(lngEntry), "|")(0) & " by " & Split(myBooks(lngEntry), "|")(1)
    strParts = Split(myBooks(lngEntry), "|")
    If strParts(1) = "Jane Austin" Then
        Debug.Print strParts(0) & " by " & strParts(1)
    End If
Next


Dim firstBook As New Book
' I want to create a book hear using the data that is now in myBooks
firstBook .Tilte =     ' what would go here
firstBook .Author  =    ' what would go here

Select allOpen in new window

Dim firstBook As New Book
' Collection entries start at 1 and so MyBooks(1) refers to the first book in the collection.
' Do you understand what the Split code does here?
firstBook.Title = Split(myBooks(1), "|")(0)
firstBook.Author = Split(myBooks(1), "|")(1)

Select allOpen in new window

Thank you very much Martin. That is great. This line   (firstBook.Title = Split(myBooks(1), "|")(0)    does give me what I was looking for.
To be honest, I do not fully undersand Split code does here, I would be grateful if you could explain that as well.

In the code that I posted for you I actually used Split in two different ways. The "normal" way looks like this:

Dim strParts() As String ' Define an array of Strings
' This is the generic explanation of the split command.
' Split ( expression [,delimiter] [,limit] [,compare] )
 I'm not going to explain "limit" or "compare" but that information can be found on the web.

' We did this, where myBooks(lngEntry) is the "expression" and "|" is the "delimiter"
strParts = Split(myBooks(lngEntry), "|")
' That results in strParts(0) containing "Emma" and  strParts(1) containing "Jane Austin"
' Note that normal arrays like strParts() start at 0, unlike collections that start at 1.

Select allOpen in new window

That way is best if you want to refer to the results of the split more than once but otherwise you can use the shortcut method

Split(myBooks(1), "|")(0) 

Select allOpen in new window

where Dim-ing strParts() isn't necessary and you refer to the first parts of the data directly using (0), or the second part using (1) etcetera.

Many thanks for your detailed explanation. As the orginnal solution you posted was correct and the other comments were only to explain things, I am accepting the original as correct.
Thank you gain for all your help.

You're welcome and I'm glad I was able to help.

If you expand the “Full Biography” section of my profile you'll find links to some articles I've written that may interest you.

Marty - Microsoft MVP 2009 to 2017
              Experts Exchange MVE 2015
              Experts Exchange Top Expert Visual Basic Classic 2012 to 2017

hmm. Just wondering what was wrong with the solution I posted as the first response to this question.

Speaking for myself, I believe that while getting the title of the book out of the collection that your code creates is easy, getting the author is more difficult.

Oh and I just realized that if there's more than one book by the same author, it will fail because of a duplicate key.

@martinLiss ah, you're right about the duplicate key :) ... it WAS a bit of a casual response.

I've never been quite sure about the value of "Collections." We could abstract the books region of the worksheet into an Array in a single line of code, and the array wouldn't require any "Splitting."