Wise Owl Answers - How do I get the column names from an ADO recordset?

[@MyAudioBookCompilation]

It's Mark again. I'm speechless. Can't stop wondering how brilliant you are.

[@WiseOwlTutorials]

Ahh thanks Mark, but it's not brilliance, just experience 😀 Thank you for the comment though, I appreciate it!

[@sokcheaheng2594]

@@WiseOwlTutorials Hi Andrew, I believe there are a lot of programmers know this and posting their contents here and there. But most of them don't have your passion (brilliance) to teach us to understand so we can improve our skills.

[@WiseOwlTutorials]

@@sokcheaheng2594 Thank you for the support Sokchea, I really appreciate it!

[@krn14242]

Thanks Andrew. Learned about clicking CTRL + i today (thanks... did not know that). Great video as always.

[@WiseOwlTutorials]

Hi Kevin! Glad you enjoyed that one, thanks for the comments as always!

[@RohithKK-uh7pp]

Thank you for the very informative and useful video.

[@WiseOwlTutorials]

You're very welcome Rohith, as always!

[@KhalilYasser]

Thank you very much for these amazing and useful tutorials.

[@WiseOwlTutorials]

You're very welcome Yasser, as always!

[@tejamarneni]

Thank you so much for the amazing content. I tried to find funny videos on Twilight Movies. The Pitch Meeting Video is the best one I found so far. If I find any new ones I will send them your way.

[@WiseOwlTutorials]

😀 thanks Ravi, I thoroughly enjoyed the Pitch Meeting compilation

[@rajeshmajumdar4999]

Thank you so much 🙏

[@WiseOwlTutorials]

You're very welcome Rajesh, thank you for watching!

[@youssefsedkey7616]

Thanks a lot.

[@WiseOwlTutorials]

You're welcome Youssef, thank you for watching!

[@imranbhatti8580]

Thanks, Andrew For another nugget of knowledge. The interesting parts to me in this video were the hidden elements of the library and the Shcemecolumns. I am not sure if you have something like creating the Query string from the criteria cells on the sheet. If not can you please create a video on this i.e. SELECT clause to be taken from some range (dynamic named range maybe) and also OR or AND operators from sheet and WHERE clause from some criteria cells as well.? Hope my question makes sense. Best Regards Imran Bhatti

[@WiseOwlTutorials]

Hi Imran! I don't have a video on that specific topic for Excel workbooks but we do have an older video which shows how to something similar for Access and SQL Server databases using ADODB kzbin.info/www/bejne/fnacdHyYmtmBpKs The video shows how to use input boxes to capture user input which is then included in the SQL string. You can use the same principle to construct your query from the values of cells on a worksheet. I will create a video on this topic hopefully soon (I'm quite busy with teaching and consultancy at the moment) but I hope the older video can point you in the right direction for now! Thanks for your questions and support!

[@brencostigan]

Request for the WiseOwl treatment: Integrating addin (.xlam) to the ribbon menu for a more professional look and natural interface. I’ve got older addins that stitch themselves into the commandbar and whilst they are still useable via legacy support the results aren’t pretty.

[@WiseOwlTutorials]

Thanks Bren, I've got this one on the To Do list!

[@CuongNguyen-pf3el]

My result no header colum name. This is code, I don’t know what’s wrong here? StrQuery = "select wonum as [Work Order], description AS [WO Desc], location AS [WO Loc], assetnum AS [Asset], workorder.owner AS [Owner], status, schedstart from workorder where siteid='3003' AND location LIKE '2%' AND status='APPR' AND schedstart = '2021-11-05 06:00:00'"

[@daves4026]

nice video

[@WiseOwlTutorials]

Thanks Dave, glad you enjoyed it!

[@frikduplessis8849]

Thank you Andrew for a great tutorial it was however very short just when I started enjoying it 😀, this weekend I watched your whole VBA series, much better than Twilight 🤭, just something I haven't tried unsuccessful, is it possible to replace the Sheet$ with a variable of a specific Range on the Sheet, Thank you for great informative tutorials 👍

[@WiseOwlTutorials]

😀 I'm sorry to hear about your very boring weekend Frik! Hopefully the next one will be more exciting! You can reference a specific range of cells on a sheet using either cell references or a range name like so: "SELECT * FROM [Sheet1$B1:D15]" "SELECT * FROM [MyRangeName]" If you want to do this using a variable you need to make sure the variable contains a string representing either the address of the cells or the range name. You then need to concatenate the variable into the SQL string like so: "SELECT * FROM [" & MyVariable & "]" I hope that helps!

[@frikduplessis8849]

@@WiseOwlTutorials thank you I can't wait to try it out, it will help with this real world frustrations

[@daves4026]

Hi Frik Hopefully I have understood your problem correctly. I use this Dim DBQsheet as string DBQsheet = "your sheet name" " SELECT * from [" & DBQsheet & "$B13:K5000]" the part after the $ is only if the data is in a range on a sheet. Very bad design of source data but often happens with excel

[@thwbn3993]

Dear Andrew, even if it is a bit cheeky: would it be possible to add another video to your fantastic ADO series? For me it would be a great help to understand the best practice to get several informations from closed workbook. F. I.: what if you grab one time all james bond movies and in another second (maybe from an input box or a user form) all twilight monies. And so on. Open a new connection for each genre? Or one connection and several recordsets? Or even several sub recordeds from one main 'select * from... ' recordset? This would be huge help for deeper understanding! Tell me afterwards how many cups of tea do you drunk during the editing. Thanks a ton from Bonn

[@WiseOwlTutorials]

Hi there! It's certainly not cheeky to ask the question! I will add this to my To Do list. Just as a hint I think that the best approach to this sort of situation is to use a disconnected recordset - the idea is to open the connection, load everything you need into the recordset (preparing a recordset is an expensive operation) and then close the connection. You can then apply filters to the recordset to return the different sets of data that you need and discard it when you have finished. Of course, you should never try to select the Twilight movies, everybody knows this!

[@thwbn3993]

@@WiseOwlTutorials a video in combination what wayne asked for (write to an closed workbook file) would be marvellous! Thorsten

[@WiseOwlTutorials]

@@thwbn3993 Thanks Thorsten, it's on the To Do list!

[@luvlycan]

Hii Andrew !! Is it possible to loop through a Range of values using VBA for Find function Example : Cells.Find What:="Lucky" If found select the cell If not found then Cells.Find What:="Andrew" If found, select the cell Something like this. P.S. I can provide a column reference to VBA to loop through

[@WiseOwlTutorials]

Hi Lucky! We have an older video which shows how to use the Find and FindNext methods which you might find useful as a reference and you can see it here kzbin.info/www/bejne/lYu5hIlvjpyHfdk Your example is a little different to the one shown in the video, the basic code would look something like this: Sub FindFirstMatch() Dim NamesToFind As Range Dim ListToLookIn As Range Dim r As Range Dim ResultCell As Range Set NamesToFind = Sheet2.Range("A1:A5") Set ListToLookIn = Sheet1.Range("B2", Sheet1.Range("B2").End(xlDown)) Sheet1.Select For Each r In NamesToFind Set ResultCell = ListToLookIn.Find(What:=r.Value) If Not ResultCell Is Nothing Then ResultCell.Select Exit For End If Next r End Sub I hope it helps!

[@luvlycan]

@@WiseOwlTutorials Andrew you are on another level of the word Genius... That's exactly what I was looking for. Thank you soooooo much :)

[@WiseOwlTutorials]

@@luvlycan That's great, happy to hear that it worked!

[@sokcheaheng2594]

Hi Andrew, I have done so many dry-eyes google searches to add headers to the listbox. But all they said it not possible with ADO. So I ended up creating another listbox to just hold the headers. Do we have way to do this with listbox?

[@WiseOwlTutorials]

Hi Sokchea! Like you I have spent a long time searching for an easy way to do this but I'm reasonably certain that the column headers property of the list box only works when you reference a range of cells. I did find a beautifully elaborate solution which involved programmatically adding labels above the list box in the correct position but I don't seem to be able to find that link at the moment.

[@gerettkindred6949]

Question: is it possible to use these SQL statements to access data in an Excel file stored on SharePoint? I have used the below listed "Connection.String" which opens and closes ok but I cannot open the record set without getting an error. Provider=Microsoft.ACE.OLEDB.12.0;WSS;IMEX=1;RetrieveIds=Yes;DATABASE=" & myFilePath & ";LIST={" & myListData & "};"

[@WiseOwlTutorials]

Hi Gerett! Unfortunately we don't use SharePoint at Wise Owl so I can't offer much in the way of useful advice here. It certainly appears as though normal select statements should work scottlyerly.wordpress.com/2014/05/14/excel-geeking-using-vba-and-ado-to-pull-data-from-sharepoint-lists/ Sorry I don't know any more than that.

[@wayneking6667]

Hey Andrew - I am not sure if you did a video where you write to a closed work book.if you did , can you send me the link..Also, can you connect to a website that requires a username and password such as google sheets

[@WiseOwlTutorials]

Hi Wayne! I haven't created the video which shows how to do this with Excel workbooks yet, but it's on the To Do list! We do have a much older video which shows how to do this with ADODB using Access and SQL Server databases kzbin.info/www/bejne/Y5SVgqmvhdWed68 The principle is the same for Excel workbooks although the ACEOLEDB provider doesn't support Delete statements. You can still execute Insert and Update statements. I hope that helps while you're waiting!

[@hajajahirhussain6814]

How to get unique column name from multiple workbooks

[@WiseOwlTutorials]

Hi! Can you be more specific about what you're trying to do and what you've already done so far?

[@hajajahirhussain6814]

@@WiseOwlTutorials yeah I have multiple workbooks, but some of the column names are different in each workbook. Like first 3 columns are same in each workbook but not in the same position, and 4 to 6 th columns are different months column like 1st file has jan to mar, 2nd file has apr to june... So i have consolidate all of them into single workbook. Like first 3 columns and jan,feb,mar,apr,may,june... Is it possible in ADO vba?

[@WiseOwlTutorials]

@@hajajahirhussain6814 In this playlist kzbin.info/aero/PLNIs-AWhQzckUd5i0E1xeSFeBAonYIurK look for the video on Union Queries from Multiple Files. To handle different columns in different files, make sure your first Select statement has a placeholder column for every other column from the other files: SELECT [Col1], [Col2], [Jan], [Feb], [Mar], Null AS [Apr], Null AS [May], Null AS [Jun] FROM [Sheet$] UNION SELECT [Col1], [Col2], Null, Null, Null, [Apr], [May], [Jun] FROM [Sheet$] IN ... Etc.

[@hajajahirhussain6814]

@@WiseOwlTutorials thanks buddy i will try it. 👍👍

[@WiseOwlTutorials]

@@hajajahirhussain6814 👍