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
List worksheets using ADO approach
-
- PlatinumLounger
- Posts: 4911
- Joined: 31 Aug 2016, 09:02
List worksheets using ADO approach
You do not have the required permissions to view the files attached to this post.
-
- Administrator
- Posts: 78236
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: List worksheets using ADO approach
See the attached workbook. I changed the code of both the standard module and of the userform.
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans
Hans
-
- PlatinumLounger
- Posts: 4911
- Joined: 31 Aug 2016, 09:02
Re: List worksheets using ADO approach
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
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
-
- Administrator
- Posts: 78236
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: List worksheets using ADO approach
In the Test macro, the lines
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.
Code: Select all
sq = "SELECT [Reference] " & "FROM [" & sName & "$] ORDER BY [Reference]"
rs.Open Source:=sq, ActiveConnection:=cn, Options:=1
arIn1 = rs.GetRows
rs.Close
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
Hans
-
- PlatinumLounger
- Posts: 4911
- Joined: 31 Aug 2016, 09:02
Re: List worksheets using ADO approach
Thanks a lot.
Can you show how to add another column as criteria (just for learning)?
Can you show how to add another column as criteria (just for learning)?
-
- Administrator
- Posts: 78236
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: List worksheets using ADO approach
Code: Select all
sq = "SELECT [Reference], [OtherColumn] " & "FROM [" & sName & "$] ORDER BY [Reference], [OtherColumn]"
Best wishes,
Hans
Hans
-
- PlatinumLounger
- Posts: 4911
- Joined: 31 Aug 2016, 09:02
Re: List worksheets using ADO approach
Is OREDER like Sort ... And if Yes, why we sort the data?
-
- Administrator
- Posts: 78236
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: List worksheets using ADO approach
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.
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
Hans
-
- PlatinumLounger
- Posts: 4911
- Joined: 31 Aug 2016, 09:02
Re: List worksheets using ADO approach
Thanks a lot my tutor and sorry for the many questions.
Best Regards
Best Regards