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

How to use ADODB to list data from another workbook Table, in UserForm-ListView

inddon

Member
Hello There,

I have 2 workbook files (attached sample workbooks):
1. Master File 1.xlsx (1st file)
2. Entry File 2.xlsm (2nd file)

- The 1st file stores all the master data records across various worksheets (Customers, Vendors, Currency, etc) .
- In the 2nd File the User references (read only) the master data from the 1st file.

Eg. Currency Code and Names:
Userform
In the 2nd File, a Userform is created where it displays selected columns from the master file worksheet Table in a object ListView. The User selects a row and the required values are displayed in the worksheet cells. It also has a search field. As the User type, the records are filtered with the typed string in the ListView

W/o Userform
Other option, the User enters a Currency Code in cell E7. The program should retrieve it's corresponding 'Currency Name' from the 1st file and should display it in cell G7. If it does not exist it should display a message.

I have attached the sample files for your reference. From my little understanding I have included some code which is not completely working.

The User does not want to use the standard Data Validation and would like to have it work in the above manner for a broader functionality regarding this requirement.

Could you please have a look at the attached files and advice how the above can be achieved?

Many thanks for your help. Look forward to hearing from you.


Regards,
Don
 

Attachments

  • Master File 1.xlsx
    8.8 KB · Views: 7
  • Entry File 2.xlsm
    25.8 KB · Views: 9
Is this not enough?
Try to keep it simple:DD
Don't forget to change the path in the code!
See example


Thanks a lot Belleke. It is working, is nice :). The functionality needed is there.

In the real Master File scenario, there are master data across various worksheets. Each worksheet has a master table with many columns.

It is because of the below reasons, I tried to do using ListView:

1. In the Userform, it would display only selected columns from the worksheet Table. "SELECT column names....

2. You can give custom names to the ListView columns (as in the original post workbook attachment)

3. The UI. During runtime, the User can adjust the ListView column width and can have the grids displayed.

As you rightly said, I would keep it simple for future maintenance, but the User saw the above ListView UI and would like to have it in that way.

I would be happy to see if it is possible to do it with ListView using ADODB. It would be nice for me to know this option of working as well.

Many thanks and look forward to hearing from you.

Wish you a good weekend.


Regards,
Don
 
Using ADO, you won't be able to query table by it's name.

There are few ways to get data from table in another workbook, using ADO.

1. Use table range address to query.

2. Have static named range defined in source workbook. And query using named range. Note that dynamic named range can't be queried directly.

In both above cases, you'll need to set up range to encompass table range and beyond. Use query statement to exclude null.

3. If there is only one table on the sheet. Just query the entire sheet.

I personally never use ListView, as not all users have it installed (I for one, don't). I use ListBox or Combobox as needed.
 
Thanks Chihiro for your advice. I tired including the worksheet in the SELECT and it works good.


I further searched on the net regarding the usage of ListView. It seems it is not supported in 64bit excel. It is better to use Listbox.

Also, would like to thanks Belleke.


Regards,
Don
 
Is this not enough?
Try to keep it simple:DD
Don't forget to change the path in the code!
See example



Hi Belleke,


I tried to get it working with additional functionality but in vain. If you have some free time could you please assist in the following:

Attached new Master File workbook

1. To have selected columns in the ListBox (eg. Currency Code, Currency Sign, Currency Name)
2. To name these columns accordingly, as in the Table Header.
3. Search item: As the User types in, it should display only those records which match the characters entered

Appreciate your help. I suppose if this is done then the implementation of Listbox is completed.

Thanks & regards,
Don
 

Attachments

  • Master File.xlsx
    9.7 KB · Views: 7
Back
Top