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
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
'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
'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)
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.
Split(myBooks(1), "|")(0)
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.
Open in new window