Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!
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.
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.
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
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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
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
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).
The above code gives me the following result.
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
The above code gives me the following result.
@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.
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
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.
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
@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.
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
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)
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.
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:
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
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.
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)
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.
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
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.
Premium Content
You need an Expert Office subscription to comment.Start Free Trial