• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Get Data from Excel Files on Common Field using VBA

P

pmyk

New Member
Please give me the code to prepare a consolidated report with the Titles of the books of all members as well as a report on individual Member with the Titles of the books.
The Member ID & Member's Name are in one excel file.
Book ID and Titles of books are in another Excel file.
Member ID, Date, Book ID of books either Given by the Friends Circulation Library or returned to the Library and in the last column G for Given and R for Returned are stored in another Excel file.
Thanks in advance.
 
Thanks. I checked. But, I could not find an answer to my query. If the command button to generate one member summary is clicked, I want to display it in a ListView control in an UserForm through VBA.
If the command button to generate summary of All Members is clicked, I want to display it in a ListView control in an UserForm through VBA.
In both the displays, the Book Code should not be displayed, but the Title of the Book should be displayed by pulling the corresponding Title from the Books.xls based on the Book ID in the Transactions.xls in which Given Away or Returned or entered.
I can generate summary just by displaying the Book ID. But I don’t know how to translate the Book ID by getting the corresponding Book Title simultaneously while drawing data from Member.xls as well as Transactions.xls. If I know how to do this, then I can even display the name of the Member in the Summary.
If the summary is pasted on a Sheet with the name “Report” in the Transactions.xls, then I can populate listview control by pulling data using Selection.Offset. I don’t know much about VBA. So, this method of mine may be right or wrong. Sorry. I need a better suggestion.
Please help.
 
I'm afraid I just became more confused. :confused:
Another idea might be to try and build an SQL query, the same way that queries are done in MS Access. It sounds like you have 3 or 4 tables, and you need to combine them based on certain key fields?
 
Yes. Let me explain my query.
Friends Circulation Library Summary needed.
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= GR (G=Book Given by the Library. R=Book Returned to the Library)
1235 10 Jan 2013 103 G
1234 12 Jan 2013 101 G
1236 14 Jan 2013 102 G
1234 16 Jan 2013 102 G
1235 18 Jan 2013 103 R
1236 20 Jan 2013 102 R
1236 20 Jan 2013 103 G
If the command button to generate one member summary is clicked, I want to display it in a ListView control in an UserForm through VBA.
If the command button to generate summary of All Members is clicked, I want to display it in a ListView control in an UserForm through VBA.
In both the displays, in addition to the Member ID, the Name of the Member and instead of the Book ID, the Title of the Book should be displayed.
If the summary is pasted on a Sheet with the name “Report” in the Transactions.xls, then I can populate Listview control by pulling data using Selection.Offset. I don’t know much about VBA. So, this method of mine may be right or wrong. Sorry. I need a better suggestion.
Please help. Thanks in advance.
 
You must log in or register to reply here.
Back
Top