Great video, mate! I never considered using ado for populating listbox on form. It’s a huge advantage using ado for that. I’ll start doing it this way without any doubt. Thanks ever so much for this video! You’re a legend!
I can not agree anymore that you are the best one explained that topic till now Amazing work and smart methodology that you are following while writing your code thanks for your efforts bro
@@WiseOwlTutorials I've tried to make the code below but once I add any column except the date column the compilor gives me type mismatch error I"d be so thankful of you tell me why that's happening ! ...... thanks, Private Sub UserForm_Initialize() Dim cn As ADODB.Connection Dim rs As ADODB.Recordset Set cn = New ADODB.Connection cn.ConnectionString = _ "Provider=Microsoft.ACE.OLEDB.12.0;" & _ "Data Source=F:\Book1.xlsx;" & _ "Extended Properties='Excel 12.0 Xml;HDR=YES';"
cn.Open Set rs = New ADODB.Recordset
rs.ActiveConnection = cn rs.Source = "select [date],[factory],[hour],[inspection stage],[model] from [sheet1$]"
rs.Open With Me.ListBox1 .ColumnCount = rs.Fields.Count .List = Application.WorksheetFunction.Transpose(rs.GetRows)
@@ahmedaiad3372 Hi Ahmed, are there any blank cells in the other columns? You might like to look at the video about Null values in this playlist ua-cam.com/play/PLNIs-AWhQzckUd5i0E1xeSFeBAonYIurK.html I hope it helps!
Thanks Andrew. Just learned how to add variable to my SQL statement. Adds the '% %' to anything the user types in the inputbox. Very cool. strTitle = InputBox("Enter a movie title or any part of one", "Search for Movie Title") sqlString = "SELECT [Title], [Release Date], [Run Time] ,[Genre] FROM [Sheet1$] " & _ "WHERE [Title] LIKE " & Chr$(39) & Chr$(37) & strTitle & Chr$(37) & Chr$(39) & _ "ORDER BY [Title] ASC"
Very nice Kevin, good stuff and thank you for sharing! This isn't a criticism at all but just for ease of reading, you could consider typing the ' and % characters as literal text within your SQL string, like this: sqlString = "SELECT [Title], [Release Date], [Run Time] ,[Genre] FROM [Sheet1$] " & _ "WHERE [Title] LIKE '%" & strTitle & "%' ORDER BY [Title] ASC" But the results are identical so it doesn't really matter!
@@WiseOwlTutorials Cool... I initially tried something like that and got syntax error, I see where I made my error. Thanks, this does make it much more readable. After playing around some more I learned how to fill a label with the record count of my list. Very cool. Thanks again!!
Thanks, Andrew! Nowadays I am working on some projects like that (Using ADO) and your playlist is a great resource for that. By the way thanks for highlighting my question in this video.
Hi, Great! Nice to see that! I am using such approach with MySQL database for almost 7 years in small corporate app. The only inconvenience is that one have to create another listbox for field names. Because the listbox' inbuilt fields work only with ranges. If the data in database tables is more or less unchangeable - it could make sence to read it once fron the DB, add it to collection (of data classes), add the collection to superior GlobalSuperCollection, and fill the listbox from the latter (retrieving the required sub collection by name) when needed. So another idea for your video could be filling list from collection. ..And perhaps you can find better solution for fields naming. Thank you and please continue with listboxes.
By the way, a listview control seems very promising as contrasted to a listbox control since the workaround for column headers is no more needed: column headers of a listview are available - not only from range (as for listbox). It would be really nice to have your tutorial on populating the listview!
Thanks Claudio! There isn't a simple, built-in way to add column headers to the list box unfortunately. There are several workarounds and you can find a couple of suggestions here stackoverflow.com/questions/657498/how-to-add-headers-to-a-multicolumn-listbox-in-an-excel-userform-using-vba/22209020 I hope that helps!
Hi. Many thanks to you, you are such a good "teacher". I'm facing an issue ... It works once i have got more than two rows as a result of my query. But my query returns only one row, transpose does not work anymore. Any idea of how I can fix this ?
Many thanks, great effort. Thanks you vary much. However I have an issue. When I try to transpose single record recordset it doesn't work. Do you have any advice?
Hi Priyanka! There isn't a simple, built-in way to do this. There are several workarounds and you can find a couple of suggestions here stackoverflow.com/questions/657498/how-to-add-headers-to-a-multicolumn-listbox-in-an-excel-userform-using-vba/22209020 I hope it helps!
Hi Andrew, just catching up on these ADO for Excel files videos, and I have a question - assuming your target closed file has data that (a) doesn't have a header row and (b) isn't structured in a contiguous region of cells, I was thinking about how you could go about extracting values from a cell by its address... would it be feasible to use a combination of [F1], [F4] column numbers in the SELECT statement, and calculation of appropriate parameters for the GetRows method, to achieve this? I was thinking along the lines of a custom function that would accept two string parameters - the path to the closed file, and a cell address - and returns a variant containing the value at that address.
Ah, ignore me, I've just watched this ua-cam.com/video/FNjTCwV7VhM/v-deo.html and you've already answered how to do this! SELECT [F4] FROM [SheetName$H3:K7] for example, would give you everything in column K (not column D, the fourth column on the sheet)?
@@VincentHardwick Hey Vince! I was just about to reply to say that's a cool idea - you could just load everything into an array using GetRows() and then extract information from the array by specifying the index of the row and column elements (bearing in mind that GetRows() transposes the worksheet rows and columns). But if you got the answer from the other video then cool!
@@WiseOwlTutorials I think the other video has answered my question, but I'll probably prove it out in code to make sure it works in the way I think it will in practice. I also thought about how you might extract values from a non-contiguous series of cells/ranges in one query, by a multi-range reference like you might use in a formula - A5:B7,D10,G52:G60 for example. I will have a play and see what I can do! If I come up with a potentially useful generic function for doing this I'll let you know.
Hi Andrew, I tried to post a comment on this thread with a link to a zip file on my Google Drive with the solution I came up with, but it's disappeared... I think UA-cam must have flagged it as spam and automatically deleted it. It does exactly what I described previously, if you're still interested to take a look, let me know how I can get it to you.
Hello, great video! Do you know how to populate a ListView from a closed workbook with ADODB? I did look into this, but ran into errors which i could not solve. If you know how to do this, could you make a short video about it?
How do I populate a listbox using another excel and use it to input the content in website? Scenario: I'm a sys admin, I have list of people(50) to be terminated from some systems. I want to do it using listbox. Any help would be appreciated.
Great video that helped me get results. I am new to programming and I have a question. Is it possible to add a TextBox on the Form to place a filter? If so, how can I do this?
Hi Chris! Yes, you can use a text box to filter your query results. To do this you'll need to know how to add a WHERE clause to the query to add criteria. We have a playlist which covers writing SQL queries for Excel VBA ua-cam.com/play/PLNIs-AWhQzckUd5i0E1xeSFeBAonYIurK.html And another which covers working with user forms ua-cam.com/play/PLNIs-AWhQzckOzn3l2_VWUicXZvywNSI4.html I hope it helps!
Hi Andrew, many many thanks to you for this knowledgeable video tutorial, I have one request to you, can you please make some tutorial about Excel RibonX/VBA RibonX, how can we program or create custom ribbon in excel, and also how can you control or manipulate custom Ribbon through Excel VBA. God bless you. Thanks Kashif
Thank you for the suggestion Kashif! I do have that topic on my To Do list already and I will hopefully get around to making a video on this topic one day!
Great Video, Please the error "'Either BOF or EOF is True, or the current record has been deleted. Requested operation requires a current record'... is being reported in this command at the moment the function ".List = WorksheetFunction.Transpose( rs.GetRows)" is activated...can you tell me what this could be
Hi Diego! It sounds like your recordset is empty. Check the statement you've used to set the Source property of the recordset to make sure that you're returning at least one row. I hope it helps!
Hi Andrew, This is amazing video. My question is that I am unable to see the heading on top of list box when showing data while using ADO. I have done lot of research but cannot find anything. Can you please let us know how can we add columns heading in listbox. Thanks!
Hi Muhammad! There isn't a simple, built-in way to do this. There are several workarounds and you can find a couple of suggestions here stackoverflow.com/questions/657498/how-to-add-headers-to-a-multicolumn-listbox-in-an-excel-userform-using-vba/22209020 I hope it helps!
I have been trying to use this method of populating a Userform from MS Sequel DB. It works until I add the WorksheetFunction.Transpose and then it does not populate anything. I can get the results to populate the listbox vertically without the WorksheetFunction.Transpose but that is all. Any suggestions?
Andrew, for some reason whenever I use the WorksheetFunction.Transpose (rs.GetRows), it comes back a Run Time Error 13, Type Mismatch. Any workarounds?
Hi Joseph! I know that nulls in your source data can cause this particular error. You may find it useful to replace nulls with a value in the SQL query which populates your recordset. We have a video on that topic which you can see here ua-cam.com/video/B2YnIokR_c0/v-deo.html I hope it helps!
Hi Brent! I think that you'll find videos 56.5 and 56.6 in this playlist useful ua-cam.com/play/PLNIs-AWhQzclbRVLCZlsFvpz6fz2nPGbt.html I hope it helps!
When you reference the "Microsoft activex data object" and select the lastest version does that affect someone who has an early version if you should share the sheet.. like the early binding / late binding example you showed with Microsoft word??
Hi Wayne! Yes, that's correct. You can use the same late-binding technique here with the ADO library too. So the late-binding version would look something like this: Dim cn As Object Dim rs As Object
@@WiseOwlTutorials Perfect, this helps..love your videos..I am a subscriber since 2016 when I first started to learn VBA and I literally learned from your videos like taking classes, if there is any way I can donate let me know. Thank you
@@wayneking6667 Thanks Wayne, I really appreciate that! We do have a donation link in the video description and we're looking into UA-cam channel memberships but we're not sure when or if we'll add those at the moment. Thanks for your support!
Hi Andrew Just been trying out the populate a ListBox1 using an ADO record set in VBA just what I was looking for, and I was wondering if its posable to use a TextBox1 to reflect what's in the ListBox1 using a criteria method of some kind ie… %textbox1text% so only the matching entries remain in the ListBox1 it would be a great search engine for what I working on. I have tryed but without success.
Hi Phil, yes you can! One way is to concatenate the text or value of the textbox into the sql string. Something like this would work: rs.Source = _ "SELECT [Title], [Release Date], [Run Time] FROM [Sheet1$] " & _ "WHERE [Title] LIKE '%" & TextBox1.Text & "%' ORDER BY [Title]" I hope it helps!
Thanks for that, work just fine, I was struggling for awhile and I nearly crack it but I am still trying to understand the SQL syntax and get bog down from time to time, but your code work first time and it work great Thanks again for you time and help .@@WiseOwlTutorials
Hey Andrew, I know this is a year after the upload date, but I was wondering how you might go about updating & inserting a set of values for a multi-valued record set in VBA. I understand that you're supposed to treat the multivalued field as it's own "child" recordset, but I'm at a loss for what to do next
Hi! I'm guessing that you're talking about multi-value fields in Microsoft Access? I don't have any experience with that I'm afraid - I've avoided multi-value fields because (as far as I know) they're unique to Access and the DAO library. There's some VBA documentation here which you may well have seen already docs.microsoft.com/en-us/office/vba/access/concepts/data-access-objects/manipulate-multivalued-fields-with-dao Sorry I don't know any more than that!
@@WiseOwlTutorials Correct, that is what I meant, sorry 😅 I've circumvented the problem by using another table completely. Tysm for all the helpful videos!
great video! i know this tutorial is already a year old but can you create a tutorial on populating listbox with the data was saved on sql server? thanks
Hi! You can check out this playlist starting at part 56.1 to learn how to connect to SQL Server ua-cam.com/play/PLNIs-AWhQzclbRVLCZlsFvpz6fz2nPGbt.html I hope it helps!
Hi Andrew, great tutorial! Me and some others on the internet have a similar problem: When the recordcount is only 1 the listbox gets no value at all. Right now I'm using an ugly if statement to handle the recordcount = 1. Have you gone through this problem? How did you solve it? Thanks!
Update. A believe the problem is with the worksheetfunction.transpose. Its not working when the recordcount is one, it even override the listbox.columncount setting it to one. I was suppose to get one line with 3 columns but the result is 3 lines and 1 column. with or without the transpose the result is the same. Any help would be appreciated.
Hi Ricardo! Yeah an IF statement is how I would solve the problem, something like this perhaps: If rs.RecordCount > 1 Then .List = WorksheetFunction.Transpose(rs.GetRows) Else .AddItem .List(0, 0) = rs.Fields(0).Value .List(0, 1) = rs.Fields(1).Value .List(0, 2) = rs.Fields(2).Value End If I hope it helps!
@@WiseOwlTutorials Thx for the reply. It does solve the issue, but I came across another one. When the value on the recordset is null it cant be added to the listbox and I cant overcome this with the rs.getrows but can check for null in the rs.Fields().value. Unfortunately the .additem method is much slower with big rs, but its the only one I got working flawless. Anyway, thx again, really appreciate the your tutorials!
@@ricardobarrosavancine4359 Indeed, nulls are a problem! You could replace the nulls in your SQL query rather than looping through the recordset afterwards. This video explains how you can do that ua-cam.com/video/B2YnIokR_c0/v-deo.html I hope it helps!
I encountered a bug in the attached files, if the where clause is matched by only one record, it will not appear correctly in the listbox. e.g. Stargate 28.10.1944 128 vs Stargate 28.10.1994 128 Would anyone know how to get out of this? Translated by DeepL
Not very elegant but something like this would do: rs.CursorType = adOpenStatic rs.Source = _ "SELECT [Title], [Release Date], [Run Time] FROM [Sheet1$] " & _ "WHERE [Title] LIKE '%stargate%' ORDER BY [Title]"
rs.Open
With ListBox1 .ColumnCount = rs.Fields.Count .ColumnWidths = "300;50;50"
If rs.RecordCount > 1 Then .List = WorksheetFunction.Transpose(rs.GetRows) Else .AddItem .List(0, 0) = rs.Fields(0).Value .List(0, 1) = rs.Fields(1).Value .List(0, 2) = rs.Fields(2).Value End If
Hi Andrew, Great Tut series! Tried to reproduce and mix this video with the one bevore populating an array from a recordset rs . Now I wonder why the WorksheetFunction.Transpose(rs.getRows) do work in a listbox.List and not to switch the rows and columns like Sub changingTheRows() dim a as variant a = WorksheetFunction.Transpose(rs.getRows) end sub Is there any secret trick (only) the wise owl knows? 🤷♂️ This really makes me crazy! 😢 Thanks - Thorsten
Hi Thorsten, when you say it doesn't work, what do you mean exactly? If you receive a Type Mismatch runtime error it could be due to Nulls in your source data. So, for example, if I do this: rs.Source = "SELECT [Title], [Release Date], [Budget] FROM [Film$]" rs.Open
Dim a As Variant
a = WorksheetFunction.Transpose(rs.GetRows) I receive an error. But, if I do this: rs.Source = "SELECT [Title], [Release Date], IIf(IsNull([Budget]), 0, [Budget]) AS [Budget] FROM [Film$]" rs.Open
Dim a As Variant
a = WorksheetFunction.Transpose(rs.GetRows) It works. I hope it helps!
@@WiseOwlTutorials Dear Andrew, thank you so much for this kind response! I am afraid this is the point. Based on the fact that I look for Null's in during creating class-objects from the SQL query* (based on the rs Array) I did not take care of Null's right in the Query itself. After all I am too lazy to fix the Null problem during several querys and and hundrets of empty fields. So at least: Thank you so much for this big help in understandig the problem! *) thanks to the wise owl for the hint: how to create typed collections 👍 kind regards Thorsten
@@thwbn3993 Hi Thorsten, I'm happy that we found the reason but it's a shame that it doesn't solve the problem for you. Chip Pearson has a nice custom TransposeArray function which you can find on this page (along with many other useful functions). I hope that will help!
I wonder could someone help me out, I have created an Animated Race Bar Chart and I'm using VBA to update it, but all but one of the y axis labels Disappear while the loop is running and they appear again at the end. I have a bar chart and I expanding the range of source data one row at a time. Anyone got and Ideas, thanks Liam... Dim i As Integer For i = 2 To sh.Range("I2").Value VBA.DoEvents sh.Range("J2").Value = i ActiveSheet.ChartObjects("Chart 6").Activate ActiveSheet.ChartObjects("Chart 6").Chart.Refresh Application.Wait Now + TimeSerial(0, 0, 0.4) Next i
Hi Andrew I have been trying to Put Headers into my Listbox control using the ADO method i.e.… [frmADOVBA.List = WorksheetFunction.Transpose(ShopData.GetRows)] this work alright but no header. I have spent some time on the syntax but without success. Is it posable without looping over the whole of the record set? Regards Phil Millington
Hi Phil! No sorry, a listbox doesn't provide this feature. There are several workarounds and you can find a couple of suggestions here stackoverflow.com/questions/657498/how-to-add-headers-to-a-multicolumn-listbox-in-an-excel-userform-using-vba/22209020 I hope it helps!
@@WiseOwlTutorialsHi Andrew I have decided to Copy the ADO Record Set to a worksheet and use the Range Address to fill the ListBox I also enabled the Headers option and this works alright with the headers displayed at the top of the Listbox using the top row of data. I had a look at the web site and notice there have been lots of people looking for a solution, the ones that are shown are a bit too complicated for me. Thanks again for your help and advice and for taking the time to reply. Kind Regards Phil
Another life saving tutorial from the WiseOwl team. Made my day. Thank you.
Happy to hear that it helped, thanks for watching!
Great video, mate! I never considered using ado for populating listbox on form. It’s a huge advantage using ado for that. I’ll start doing it this way without any doubt. Thanks ever so much for this video! You’re a legend!
Cheers Sebastian!
Hi Andrew, thank you so so so much. I got my project done because of your tutorials.
Happy to hear that Sokchea!
I can not agree anymore that you are the best one explained that topic till now
Amazing work and smart methodology that you are following while writing your code
thanks for your efforts bro
Thank you for the kind words and for watching Ahmed!
@@WiseOwlTutorials
I've tried to make the code below but once I add any column except the date column the compilor gives me
type mismatch error
I"d be so thankful of you tell me why that's happening ! ...... thanks,
Private Sub UserForm_Initialize()
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Set cn = New ADODB.Connection
cn.ConnectionString = _
"Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=F:\Book1.xlsx;" & _
"Extended Properties='Excel 12.0 Xml;HDR=YES';"
cn.Open
Set rs = New ADODB.Recordset
rs.ActiveConnection = cn
rs.Source = "select [date],[factory],[hour],[inspection stage],[model] from [sheet1$]"
rs.Open
With Me.ListBox1
.ColumnCount = rs.Fields.Count
.List = Application.WorksheetFunction.Transpose(rs.GetRows)
End With
rs.Close
cn.Close
@@ahmedaiad3372 Hi Ahmed, are there any blank cells in the other columns? You might like to look at the video about Null values in this playlist ua-cam.com/play/PLNIs-AWhQzckUd5i0E1xeSFeBAonYIurK.html
I hope it helps!
@@WiseOwlTutorials Amazing, It worked well with me but I still can't get the table headers in my selection
can that be solved in somehow!
Anyone here know the solution for that people .
????
Thanks Andrew. Just learned how to add variable to my SQL statement. Adds the '% %' to anything the user types in the inputbox. Very cool.
strTitle = InputBox("Enter a movie title or any part of one", "Search for Movie Title")
sqlString = "SELECT [Title], [Release Date], [Run Time] ,[Genre] FROM [Sheet1$] " & _
"WHERE [Title] LIKE " & Chr$(39) & Chr$(37) & strTitle & Chr$(37) & Chr$(39) & _
"ORDER BY [Title] ASC"
Very nice Kevin, good stuff and thank you for sharing!
This isn't a criticism at all but just for ease of reading, you could consider typing the ' and % characters as literal text within your SQL string, like this:
sqlString = "SELECT [Title], [Release Date], [Run Time] ,[Genre] FROM [Sheet1$] " & _
"WHERE [Title] LIKE '%" & strTitle & "%' ORDER BY [Title] ASC"
But the results are identical so it doesn't really matter!
@@WiseOwlTutorials Cool... I initially tried something like that and got syntax error, I see where I made my error. Thanks, this does make it much more readable. After playing around some more I learned how to fill a label with the record count of my list. Very cool. Thanks again!!
@@krn14242 Great stuff, I'm confident that your form looks infinitely better than mine already!
Thanks, Andrew! Nowadays I am working on some projects like that (Using ADO) and your playlist is a great resource for that. By the way thanks for highlighting my question in this video.
Thanks Imran, happy to hear that you found these videos useful!
Love it! Thanks for your time producing this very informative instructional video!
My pleasure Erick, glad you enjoyed it and thanks for the comment!
Thank you very much for the clear and understandable information.
You're very welcome, thanks for watching!
Hi guy, thank you so much. Great video!!
You're very welcome, thank you so much for taking the time to leave a comment!
👏👏👏👏 thank you Andrew this is a really cool and helpful tutorial,
Thanks Frik, glad you enjoyed it!
Hi, Great! Nice to see that! I am using such approach with MySQL database for almost 7 years in small corporate app.
The only inconvenience is that one have to create another listbox for field names. Because the listbox' inbuilt fields work only with ranges.
If the data in database tables is more or less unchangeable - it could make sence to read it once fron the DB, add it to collection (of data classes), add the collection to superior GlobalSuperCollection, and fill the listbox from the latter (retrieving the required sub collection by name) when needed.
So another idea for your video could be filling list from collection.
..And perhaps you can find better solution for fields naming.
Thank you and please continue with listboxes.
Thanks for the suggestion N D! I'll add it to the To Do list.
@@WiseOwlTutorials You are welcome )
By the way, a listview control seems very promising as contrasted to a listbox control since the workaround for column headers is no more needed: column headers of a listview are available - not only from range (as for listbox). It would be really nice to have your tutorial on populating the listview!
@@aNDy-qh1em Indeed, that topic is on my list (no pun intended!)
Uf, great tutorial, thank you Andrew, Cheers
You're very welcome Janez, thanks for your support!
Amazing and awesome my tutor.
Thank you Yasser!
That's fantastic. How to display column headers in the listbox following that procedure?
Thanks Claudio! There isn't a simple, built-in way to add column headers to the list box unfortunately. There are several workarounds and you can find a couple of suggestions here stackoverflow.com/questions/657498/how-to-add-headers-to-a-multicolumn-listbox-in-an-excel-userform-using-vba/22209020
I hope that helps!
Hi. Many thanks to you, you are such a good "teacher". I'm facing an issue ... It works once i have got more than two rows as a result of my query. But my query returns only one row, transpose does not work anymore. Any idea of how I can fix this ?
Hi! Can you search for my answer to @ricardobarrosavancine4359 on this page - it's a common issue!
@@WiseOwlTutorials Thanks very much
Many thanks, great effort. Thanks you vary much. However I have an issue. When I try to transpose single record recordset it doesn't work. Do you have any advice?
Thanks Miroslav, glad you enjoyed it! Can you search for my answer to "Ricardo Barros Avancine" on this page for the solution to this problem.
Very nice !
Thanks!
Thanks very much.
You're welcome Rohith, thank you for watching!
Thanks for sharing.....Could you please how to populate Listbox header along with data....
Hi Priyanka! There isn't a simple, built-in way to do this. There are several workarounds and you can find a couple of suggestions here stackoverflow.com/questions/657498/how-to-add-headers-to-a-multicolumn-listbox-in-an-excel-userform-using-vba/22209020
I hope it helps!
Hi Andrew, just catching up on these ADO for Excel files videos, and I have a question - assuming your target closed file has data that (a) doesn't have a header row and (b) isn't structured in a contiguous region of cells, I was thinking about how you could go about extracting values from a cell by its address... would it be feasible to use a combination of [F1], [F4] column numbers in the SELECT statement, and calculation of appropriate parameters for the GetRows method, to achieve this? I was thinking along the lines of a custom function that would accept two string parameters - the path to the closed file, and a cell address - and returns a variant containing the value at that address.
Ah, ignore me, I've just watched this ua-cam.com/video/FNjTCwV7VhM/v-deo.html and you've already answered how to do this! SELECT [F4] FROM [SheetName$H3:K7] for example, would give you everything in column K (not column D, the fourth column on the sheet)?
@@VincentHardwick Hey Vince! I was just about to reply to say that's a cool idea - you could just load everything into an array using GetRows() and then extract information from the array by specifying the index of the row and column elements (bearing in mind that GetRows() transposes the worksheet rows and columns).
But if you got the answer from the other video then cool!
@@WiseOwlTutorials I think the other video has answered my question, but I'll probably prove it out in code to make sure it works in the way I think it will in practice. I also thought about how you might extract values from a non-contiguous series of cells/ranges in one query, by a multi-range reference like you might use in a formula - A5:B7,D10,G52:G60 for example.
I will have a play and see what I can do! If I come up with a potentially useful generic function for doing this I'll let you know.
@@VincentHardwick Cool, I look forward to seeing what you come up with!
Hi Andrew, I tried to post a comment on this thread with a link to a zip file on my Google Drive with the solution I came up with, but it's disappeared... I think UA-cam must have flagged it as spam and automatically deleted it. It does exactly what I described previously, if you're still interested to take a look, let me know how I can get it to you.
Great video, it's a shame it's not shown it in the listbox
header of individual columns.
Yes, agreed, getting the column headers in the list box is a pain and I didn't have time to cover it in the short answer video. It's on my to do list!
Hello, great video!
Do you know how to populate a ListView from a closed workbook with ADODB?
I did look into this, but ran into errors which i could not solve.
If you know how to do this, could you make a short video about it?
Thanks Manuel! I do have this topic on my To Do list, thanks for the question and for watching!
@@WiseOwlTutorials Nice, i am looking forward to it, thanks for the effort.
How do I populate a listbox using another excel and use it to input the content in website?
Scenario: I'm a sys admin, I have list of people(50) to be terminated from some systems.
I want to do it using listbox.
Any help would be appreciated.
Great video that helped me get results.
I am new to programming and I have a question.
Is it possible to add a TextBox on the Form to place a filter? If so, how can I do this?
Hi Chris! Yes, you can use a text box to filter your query results. To do this you'll need to know how to add a WHERE clause to the query to add criteria. We have a playlist which covers writing SQL queries for Excel VBA ua-cam.com/play/PLNIs-AWhQzckUd5i0E1xeSFeBAonYIurK.html
And another which covers working with user forms ua-cam.com/play/PLNIs-AWhQzckOzn3l2_VWUicXZvywNSI4.html
I hope it helps!
Hi Andrew, many many thanks to you for this knowledgeable video tutorial, I have one request to you, can you please make some tutorial about Excel RibonX/VBA RibonX, how can we program or create custom ribbon in excel, and also how can you control or manipulate custom Ribbon through Excel VBA.
God bless you.
Thanks
Kashif
Thank you for the suggestion Kashif! I do have that topic on my To Do list already and I will hopefully get around to making a video on this topic one day!
Great Video, Please the error "'Either BOF or EOF is True, or the current record has been deleted. Requested operation requires a current record'... is being reported in this command at the moment the function ".List = WorksheetFunction.Transpose( rs.GetRows)" is activated...can you tell me what this could be
Hi Diego! It sounds like your recordset is empty. Check the statement you've used to set the Source property of the recordset to make sure that you're returning at least one row. I hope it helps!
Hi Andrew, This is amazing video. My question is that I am unable to see the heading on top of list box when showing data while using ADO. I have done lot of research but cannot find anything. Can you please let us know how can we add columns heading in listbox. Thanks!
Hi Muhammad! There isn't a simple, built-in way to do this. There are several workarounds and you can find a couple of suggestions here stackoverflow.com/questions/657498/how-to-add-headers-to-a-multicolumn-listbox-in-an-excel-userform-using-vba/22209020
I hope it helps!
I have been trying to use this method of populating a Userform from MS Sequel DB. It works until I add the WorksheetFunction.Transpose and then it does not populate anything.
I can get the results to populate the listbox vertically without the WorksheetFunction.Transpose but that is all.
Any suggestions?
Hi Brent! One reason may be nulls in your dataset. Check out the answers to comments by ThW Bn and Joseph Dube for a workaround. I hope it helps!
Andrew, for some reason whenever I use the WorksheetFunction.Transpose (rs.GetRows), it comes back a Run Time Error 13, Type Mismatch. Any workarounds?
Hi Joseph! I know that nulls in your source data can cause this particular error. You may find it useful to replace nulls with a value in the SQL query which populates your recordset. We have a video on that topic which you can see here ua-cam.com/video/B2YnIokR_c0/v-deo.html
I hope it helps!
Hello, this was a great tutorial. Is there any way to do this and have the combo box searchable? I have a really large record set I am working with
Hi Caleb, do you mean like this sort of thing? stackoverflow.com/questions/44076069/searchable-combo-box-with-a-list-of-sugggestion-on-a-userform
@@WiseOwlTutorials I THINK SO! Thanks! I’ll give it a try!
great video as usual... thanks.. i have a question please.. how can we handle the situation when the query result is empty? Thanks again
Hi Mohammed! You might find this video useful ua-cam.com/video/aEnFo2OU8Os/v-deo.html
I hope it helps!
@@WiseOwlTutorials
Thank you.. this exactly answers my question.. really appreciated ❤❤❤
@@not1AM My pleasure Mohammed, I'm happy it helped!
Can you offer some assistance on how to Insert records from a 7 column listbox on an excel userform, into a table in sql server db?
Hi Brent! I think that you'll find videos 56.5 and 56.6 in this playlist useful ua-cam.com/play/PLNIs-AWhQzclbRVLCZlsFvpz6fz2nPGbt.html
I hope it helps!
When you reference the "Microsoft activex data object" and select the lastest version does that affect someone who has an early version if you should share the sheet.. like the early binding / late binding example you showed with Microsoft word??
Hi Wayne! Yes, that's correct. You can use the same late-binding technique here with the ADO library too. So the late-binding version would look something like this:
Dim cn As Object
Dim rs As Object
Set cn = CreateObject("ADODB.Connection")
cn.ConnectionString = _
"Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=" & ThisWorkbook.Path & "\Movies.xlsx;" & _
"Extended Properties='Excel 12.0 Xml;HDR=YES';"
cn.Open
Set rs = CreateObject("ADODB.Recordset")
rs.ActiveConnection = cn
rs.Source = _
"SELECT [Title], [Release Date], [Run Time] FROM [Sheet1$] " & _
"WHERE [Title] LIKE '%star%' ORDER BY [Title]"
rs.Open
With ListBox1
.ColumnCount = rs.Fields.Count
.ColumnWidths = "300;50;50"
.List = WorksheetFunction.Transpose(rs.GetRows)
End With
rs.Close
cn.Close
I hope that helps!
@@WiseOwlTutorials Perfect, this helps..love your videos..I am a subscriber since 2016 when I first started to learn VBA and I literally learned from your videos like taking classes, if there is any way I can donate let me know. Thank you
@@wayneking6667 Thanks Wayne, I really appreciate that! We do have a donation link in the video description and we're looking into UA-cam channel memberships but we're not sure when or if we'll add those at the moment.
Thanks for your support!
Thanks very very much....
¿How I do populate a *Listview from a Recordset without a bucle?
Hi Edison, I've only ever done this using a loop. I'm not sure if there's another way.
@@WiseOwlTutorials Thanks for talk to me.....I'm from Bogotá-Colombia,,, God watch over you always
@@Edison_mm Gracias Edison!
Hi Andrew Just been trying out the populate a ListBox1 using an ADO record set in VBA just what I was looking for, and I was wondering if its posable to use a TextBox1 to reflect what's in the ListBox1 using a criteria method of some kind ie… %textbox1text% so only the matching entries remain in the ListBox1 it would be a great search engine for what I working on. I have tryed but without success.
Hi Phil, yes you can! One way is to concatenate the text or value of the textbox into the sql string. Something like this would work:
rs.Source = _
"SELECT [Title], [Release Date], [Run Time] FROM [Sheet1$] " & _
"WHERE [Title] LIKE '%" & TextBox1.Text & "%' ORDER BY [Title]"
I hope it helps!
Thanks for that, work just fine, I was struggling for awhile and I nearly crack it but I am still trying to understand the SQL syntax and get bog down from time to time, but your code work first time and it work great Thanks again for you time and help .@@WiseOwlTutorials
@@philmillington5025 No worries Phil! You'll get there with the SQL side, it's just practice!
Hey Andrew, I know this is a year after the upload date, but I was wondering how you might go about updating & inserting a set of values for a multi-valued record set in VBA. I understand that you're supposed to treat the multivalued field as it's own "child" recordset, but I'm at a loss for what to do next
Hi! I'm guessing that you're talking about multi-value fields in Microsoft Access? I don't have any experience with that I'm afraid - I've avoided multi-value fields because (as far as I know) they're unique to Access and the DAO library. There's some VBA documentation here which you may well have seen already docs.microsoft.com/en-us/office/vba/access/concepts/data-access-objects/manipulate-multivalued-fields-with-dao
Sorry I don't know any more than that!
@@WiseOwlTutorials Correct, that is what I meant, sorry 😅 I've circumvented the problem by using another table completely. Tysm for all the helpful videos!
@@acequantum2850 No problem, happy to hear you found an alternative solution!
great video! i know this tutorial is already a year old but can you create a tutorial on populating listbox with the data was saved on sql server? thanks
Hi! You can check out this playlist starting at part 56.1 to learn how to connect to SQL Server ua-cam.com/play/PLNIs-AWhQzclbRVLCZlsFvpz6fz2nPGbt.html
I hope it helps!
@@WiseOwlTutorials hey already did it reading your tutorial somewhere in the web! Thanks a lot for noticing me here! 💚
@@mrnimbus2556 Happy to hear that you found a solution and thanks for watching!
Hi Andrew, great tutorial!
Me and some others on the internet have a similar problem: When the recordcount is only 1 the listbox gets no value at all. Right now I'm using an ugly if statement to handle the recordcount = 1. Have you gone through this problem? How did you solve it? Thanks!
Update. A believe the problem is with the worksheetfunction.transpose. Its not working when the recordcount is one, it even override the listbox.columncount setting it to one. I was suppose to get one line with 3 columns but the result is 3 lines and 1 column. with or without the transpose the result is the same. Any help would be appreciated.
Hi Ricardo! Yeah an IF statement is how I would solve the problem, something like this perhaps:
If rs.RecordCount > 1 Then
.List = WorksheetFunction.Transpose(rs.GetRows)
Else
.AddItem
.List(0, 0) = rs.Fields(0).Value
.List(0, 1) = rs.Fields(1).Value
.List(0, 2) = rs.Fields(2).Value
End If
I hope it helps!
@@WiseOwlTutorials Thx for the reply. It does solve the issue, but I came across another one. When the value on the recordset is null it cant be added to the listbox and I cant overcome this with the rs.getrows but can check for null in the rs.Fields().value. Unfortunately the .additem method is much slower with big rs, but its the only one I got working flawless. Anyway, thx again, really appreciate the your tutorials!
@@ricardobarrosavancine4359 Indeed, nulls are a problem! You could replace the nulls in your SQL query rather than looping through the recordset afterwards. This video explains how you can do that ua-cam.com/video/B2YnIokR_c0/v-deo.html
I hope it helps!
@@WiseOwlTutorials Thanks!
I encountered a bug in the attached files, if the where clause is matched by only one record, it will not appear correctly in the listbox.
e.g.
Stargate
28.10.1944
128
vs
Stargate 28.10.1994 128
Would anyone know how to get out of this?
Translated by DeepL
Not very elegant but something like this would do:
rs.CursorType = adOpenStatic
rs.Source = _
"SELECT [Title], [Release Date], [Run Time] FROM [Sheet1$] " & _
"WHERE [Title] LIKE '%stargate%' ORDER BY [Title]"
rs.Open
With ListBox1
.ColumnCount = rs.Fields.Count
.ColumnWidths = "300;50;50"
If rs.RecordCount > 1 Then
.List = WorksheetFunction.Transpose(rs.GetRows)
Else
.AddItem
.List(0, 0) = rs.Fields(0).Value
.List(0, 1) = rs.Fields(1).Value
.List(0, 2) = rs.Fields(2).Value
End If
End With
@@WiseOwlTutorials
Thanks for the quick reply. Works absolutely great!
@@grimm5214 Nice one!
Hi Andrew, Great Tut series!
Tried to reproduce and mix this video with the one bevore populating an array from a recordset rs .
Now I wonder why the WorksheetFunction.Transpose(rs.getRows) do work in a listbox.List and not to switch the rows and columns like
Sub changingTheRows()
dim a as variant
a = WorksheetFunction.Transpose(rs.getRows)
end sub
Is there any secret trick (only) the wise owl knows? 🤷♂️ This really makes me crazy! 😢
Thanks - Thorsten
Hi Thorsten, when you say it doesn't work, what do you mean exactly? If you receive a Type Mismatch runtime error it could be due to Nulls in your source data. So, for example, if I do this:
rs.Source = "SELECT [Title], [Release Date], [Budget] FROM [Film$]"
rs.Open
Dim a As Variant
a = WorksheetFunction.Transpose(rs.GetRows)
I receive an error. But, if I do this:
rs.Source = "SELECT [Title], [Release Date], IIf(IsNull([Budget]), 0, [Budget]) AS [Budget] FROM [Film$]"
rs.Open
Dim a As Variant
a = WorksheetFunction.Transpose(rs.GetRows)
It works. I hope it helps!
@@WiseOwlTutorials Dear Andrew, thank you so much for this kind response! I am afraid this is the point. Based on the fact that I look for Null's in during creating class-objects from the SQL query* (based on the rs Array) I did not take care of Null's right in the Query itself.
After all I am too lazy to fix the Null problem during several querys and and hundrets of empty fields.
So at least: Thank you so much for this big help in understandig the problem!
*) thanks to the wise owl for the hint: how to create typed collections 👍
kind regards
Thorsten
@@thwbn3993 Hi Thorsten, I'm happy that we found the reason but it's a shame that it doesn't solve the problem for you. Chip Pearson has a nice custom TransposeArray function which you can find on this page (along with many other useful functions). I hope that will help!
@@WiseOwlTutorials the positive side is:. I never new something about chip Pearson. I guess I have to read a view pages there 😅
Thanks a ton from Bonn
@@thwbn3993 I have spent many happy hours on Chip's website, I'm sure that you will too!
I wonder could someone help me out, I have created an Animated Race Bar Chart and I'm using VBA to update it, but all but one of the y axis labels Disappear while the loop is running and they appear again at the end. I have a bar chart and I expanding the range of source data one row at a time. Anyone got and Ideas, thanks Liam...
Dim i As Integer
For i = 2 To sh.Range("I2").Value
VBA.DoEvents
sh.Range("J2").Value = i
ActiveSheet.ChartObjects("Chart 6").Activate
ActiveSheet.ChartObjects("Chart 6").Chart.Refresh
Application.Wait Now + TimeSerial(0, 0, 0.4)
Next i
Hi Liam, I'm not sure about that one off the top of my head but I'll stick it on the list to have a look at!
@@WiseOwlTutorials I just turned off the axis in the chart elements and turned them back on and they are working now. I have no clue why that worked:)
@@lcorcoran56 Brilliant! I would have spent hours looking for a VBA solution for that, so thanks for letting us know you fixed it!
Super Tutorial! But the WorksheetFunction.Transpose generates a Error 13. Any suggestions? Without this, it works!
Hi Andrew I have been trying to Put Headers into my Listbox control using the ADO method i.e.… [frmADOVBA.List = WorksheetFunction.Transpose(ShopData.GetRows)] this work alright but no header. I have spent some time on the syntax but without success. Is it posable without looping over the whole of the record set? Regards Phil Millington
Hi Phil! No sorry, a listbox doesn't provide this feature. There are several workarounds and you can find a couple of suggestions here stackoverflow.com/questions/657498/how-to-add-headers-to-a-multicolumn-listbox-in-an-excel-userform-using-vba/22209020
I hope it helps!
@@WiseOwlTutorialsHi Andrew I have decided to Copy the ADO Record Set to a worksheet and use the Range Address to fill the ListBox I also enabled the Headers option and this works alright with the headers displayed at the top of the Listbox using the top row of data. I had a look at the web site and notice there have been lots of people looking for a solution, the ones that are shown are a bit too complicated for me. Thanks again for your help and advice and for taking the time to reply. Kind Regards Phil
@@philmillington5025 Hi Phil, indeed it's a common request. Happy to hear that you found a workaround!