List worksheets using ADO approach

YasserKhalil
PlatinumLounger
Posts: 4911
Joined: 31 Aug 2016, 09:02

List worksheets using ADO approach

Post by YasserKhalil »

Hello everyone
In previous thread Mr. Hans helped me with the following code ( the code is in standard module and userform1 module)
What I am trying to do now is : I have created a combobox that I need to list the worksheets in the workbook that will be selected. Then populating the headers of that sheet in listbox1 ..

Thanks advanced for help
You do not have the required permissions to view the files attached to this post.

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: List worksheets using ADO approach

Post by HansV »

See the attached workbook. I changed the code of both the standard module and of the userform.

Source With Code.xlsm
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans

YasserKhalil
PlatinumLounger
Posts: 4911
Joined: 31 Aug 2016, 09:02

Re: List worksheets using ADO approach

Post by YasserKhalil »

Amazing my tutor. I will try to do my best to learn such perfect skills.
Last question: Is it easy to change the column of search ( the criteria columns I mean as "Reference")?
And what if I need to add another column as criteria .. what part of the code should I change? (Just refer me to the part responsible for that so as to lean)

Best Regards

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: List worksheets using ADO approach

Post by HansV »

In the Test macro, the lines

Code: Select all

        sq = "SELECT [Reference] " & "FROM [" & sName & "$] ORDER BY [Reference]"
        rs.Open Source:=sq, ActiveConnection:=cn, Options:=1
        arIn1 = rs.GetRows
        rs.Close
populate an array ar1 with the values of the Reference column. You could change this to use another field, or a combination of two or more fields.

Keep in mind that the ar2 array that is populated in the lines below that, MUST be sorted the same way as ar1.
Best wishes,
Hans

YasserKhalil
PlatinumLounger
Posts: 4911
Joined: 31 Aug 2016, 09:02

Re: List worksheets using ADO approach

Post by YasserKhalil »

Thanks a lot.
Can you show how to add another column as criteria (just for learning)?

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: List worksheets using ADO approach

Post by HansV »

Code: Select all

        sq = "SELECT [Reference], [OtherColumn] " & "FROM [" & sName & "$] ORDER BY [Reference], [OtherColumn]"
Best wishes,
Hans

YasserKhalil
PlatinumLounger
Posts: 4911
Joined: 31 Aug 2016, 09:02

Re: List worksheets using ADO approach

Post by YasserKhalil »

Is OREDER like Sort ... And if Yes, why we sort the data?

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: List worksheets using ADO approach

Post by HansV »

Yes, ORDER BY specifies the sort order of the recordset.
We want the two arrays ar1 and ar2 to be sorted the same way, so that when we find the value(s) we search for in ar1, we can retrieve the values with the same index from ar2.
Best wishes,
Hans

YasserKhalil
PlatinumLounger
Posts: 4911
Joined: 31 Aug 2016, 09:02

Re: List worksheets using ADO approach

Post by YasserKhalil »

Thanks a lot my tutor and sorry for the many questions.
Best Regards