Summary to be generated from excel files in vba.

[pmyk]

Summaryof Friends Free Circulation Library to be generated from Excel FIles based on common ID in vba.
D:\Member.xls
Col A= MemberID ColB= Member'sName
1234 Mr. D.
1235 Mr. A.
1236 Mr. C.

D:\Books.xls
Col A= BookID ColB= Titles of books
101 Adventures
102 Health Tips
103 Short Stories

D:\Transactions.xls
Col A= MemberID ColB= date ColC= BookID ColD= TR (Book taken or Returned)
1235 10 Jan 2013 103 T
1234 12 Jan 2013 101 T
1236 14 Jan 2013 102 T
1234 16 Jan 2013 102 T
1235 18 Jan 2013 103 R
1236 20 Jan 2013 102 R
1236 20 Jan 2013 103 T

Summary Expected Member-wise on a Sheet with the name “Report” in the Transactions.xls:
A=MemberID B=MemberName C=date D=BookTitle E=TR (Book taken or Returned)
1234 Mr. D. 12 Jan 2013 Adventures Taken
1234 Mr. D. 16 Jan 2013 Health Tips Taken
1235 Mr. A. 10 Jan 2013 Short Stories Taken
1235 Mr. A. 18 Jan 2013 Short Stories Returned
1236 Mr. C. 14 Jan 2013 Health Tips Taken
1236 Mr. C. 20 Jan 2013 Health Tips Returned
1236 Mr. C. 20 Jan 2013 Short Stories Taken

I can populate Listview Control by pulling data from this Report Sheet using Selection.Offset. (Any other good suggestion is appreciated).
I don’t know much about VBA. Please help. Thanks in advance.

[Doug Robbins]

If you have Access, it is very simple to link to the Excel files and then use a Select query in Access where you create a link from the IDs in the transaction table to the respective IDs in the Member and the Books tables and then select the required fields from each of the tables as the output of the query.

[pmyk]

Thanks for your reply. I am still a learner.
I searched the Net and picked up vlookup and it is doing the job.
Thanks once again.

[SamT]

If appropriate, will you use the Thread Tools At the top of this page to mark the Thread "Solved?"

Also, for the benefit of other guests, will you post the code that worked for you? You can use the # button of the Reply Editor to format the Code itself. Just click the button and paste between the new Code tags. Or, paste the code, then select it, then press the symbol,

[pmyk]

A piece of my code, which I got from Net.

Private Sub cmd2Members_VLOOKUP_Click()
    Dim lookFor As Range
    Dim srchNameRange As Range
    Dim FoundOut As String
    Dim book1 As Workbook
    Dim book2 As Workbook
    Dim book2Name As String
    book2Name = "Members.xls
    Dim book2NamePath As String
    book2NamePath = "D:\" & book2Name
    Set book1 = Transactions.xls")
    MyFyl = Workbooks.Open (book2NamePath)
    Set book2 = Workbooks(book2Name)
    Set lookFor = book1.Sheets(1).Cells(2, 3)   'value to find in Row 2 and Col 3
    Set srchNameRange = book2.Sheets(1).Range("B:C")
    FoundOut = Application.VLookup(lookFor, srchNameRange, 2, False)
msgbox "FoundOut=" & FoundOut
End Sub

[SamT]

Thank you.