Avatar of FaheemAhmadGul
FaheemAhmadGul
Flag for United Kingdom of Great Britain and Northern Ireland asked on

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

Microsoft Office.NET ProgrammingVBAMicrosoft ExcelMicrosoft Word

Avatar of undefined
Last Comment
Neil Fleming

8/22/2022 - Mon
Neil Fleming

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

Martin Liss

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

ASKER
@Martin Liss
Line 15 in my code has been added by mistake.
Your help has saved me hundreds of hours of internet surfing.
fblack61
Neil Fleming

@faheemAhmadGul .. yes I figured it was probably a relic of an earlier version.
ASKER CERTIFIED SOLUTION
Martin Liss

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
FaheemAhmadGul

ASKER
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
Martin Liss

If you change the several occurrences of "Sheet1" in my code to "First", it should work for you.
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
FaheemAhmadGul

ASKER
@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.
Martin Liss

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

ASKER
@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.
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
Martin Liss

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

FaheemAhmadGul

ASKER
@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

Martin Liss

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

⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
FaheemAhmadGul

ASKER
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.
Martin Liss

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.
FaheemAhmadGul

ASKER
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.
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
Martin Liss

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
Neil Fleming

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

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.
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Martin Liss

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.
Neil Fleming

@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."