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

In a simple VBA project for Word I have Class Module named Book which has just two properties - Title and Author - both strings.
I would like to be pull data from Sheet1 of a simple Excel Workbook to create a Collection of Books items ( named myBooks). Every book to be added to this Collection  (myBooks) will be created using a record from Sheet1 of the Excel Workbook, such that the value in Column 1 of each record will be the title of the Book and the value in Column 2 of each record will be the Author of the Book. I include a screen shot of the Sheet1 of my Excel Workbook.
Thank you for your help.

Screenshot of the Sheet1 of the Excel Workbook
Dim objExcel As New Excel.Application
Dim exWb As Excel.Workbook

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. 


exWb.Close

Set exWb = Nothing

Open in new window

LVL 1
FaheemAhmadGulAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

 
Neil FlemingConsultant and developerCommented:
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

Open in new window

0
 
Martin LissOlder than dirtCommented:
Why are you opening two workbooks in your code?  Why do you need line 15?
0
 
FaheemAhmadGulAuthor Commented:
@Martin Liss
Line 15 in my code has been added by mistake.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Neil FlemingConsultant and developerCommented:
@faheemAhmadGul .. yes I figured it was probably a relic of an earlier version.
0
 
Martin LissOlder than dirtCommented:
Sub GetBooks()

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

Dim bookTitle As String
Dim bookAutor As String

Dim myBooks As Collection
Set myBooks = New Collection

Dim lngLastRow As Long
Dim lngRow As Long

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.
With exWb
    lngLastRow = .Sheets("Sheet1").Range("A1048576").End(xlUp).Row
    For lngRow = 1 To lngLastRow
        myBooks.Add .Sheets("Sheet1").Cells(lngRow, "A") & "|" & .Sheets("Sheet1").Cells(lngRow, "B")
    Next
    .Close
End With

Set exWb = Nothing

'Test
Dim lngEntry As Long
For lngEntry = 1 To myBooks.Count
    Debug.Print Split(myBooks(lngEntry), "|")(0) & " by " & Split(myBooks(lngEntry), "|")(1)
Next
End Sub

Open in new window

0

Experts Exchange Solution brought to you by ConnectWise

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
 
FaheemAhmadGulAuthor Commented:
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

Open in new window


The above code gives me the following result.
Result From Code
0
 
Martin LissOlder than dirtCommented:
If you change the several occurrences of "Sheet1" in my code to "First", it should work for you.
0
 
FaheemAhmadGulAuthor Commented:
@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.
0
 
Martin LissOlder than dirtCommented:
In line 24 did you change "Sheet1" to "First" as I suggested?
0
 
FaheemAhmadGulAuthor Commented:
@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.
0
 
Martin LissOlder than dirtCommented:
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

Open in new window

0
 
FaheemAhmadGulAuthor Commented:
@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

Open in new window

0
 
Martin LissOlder than dirtCommented:
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)

Open in new window

0
 
FaheemAhmadGulAuthor Commented:
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.
0
 
Martin LissOlder than dirtCommented:
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.

Open 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) 

Open 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.
0
 
FaheemAhmadGulAuthor Commented:
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.
0
 
Martin LissOlder than dirtCommented:
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
0
 
Neil FlemingConsultant and developerCommented:
hmm. Just wondering what was wrong with the solution I posted as the first response to this question.
0
 
Martin LissOlder than dirtCommented:
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.
0
 
Martin LissOlder than dirtCommented:
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.
1
 
Neil FlemingConsultant and developerCommented:
@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."
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.