Webinar Number: W0058
This webinar includes:
- Why querying data using standard VBA is problematic.
- What is ADO and how can I use it.
- How to easily set up ADO using VBA code.
- How to use SQL to perform a simple query.
- How to write the query results to a worksheet.
- Example query – filtering data
- Example query – sorting data
- Example query – grouping data by date.
- Example query – joining data
- Live coding so you can see exactly what to do
- A Question and Answer session at the end
Download the Source Code
You can download the source code from here
The webinar was powerfull.
I already bought your 399€-book and saved the links to different of your VBA subjects (articles).
An article about the approach with SQL is still missing in this collection.
Do you have an article of this course, even in PDF should be wonderfull to me?
Hi Peter,
I’m not clear on your question. Do you need to access the course?
Paul
Fabulous. Paul.
Thanks Sandeep
Why are the Subs like Select and Use class Private and not Public?
Hello Paul
Thanks for the great seminar
many greetings
Karl-Heinz
Thanks Karl-Heinz
Hi Paul,
nice Webinar.
Where can I Download the Source Code ?
Greetings Tobi
Hi,
I will be available in 48 hours when I move the replay to the members section. You can sign up here
PowerQuery CAN do the things you’ve shown in your presentation.
Working w/ different data source types,
select, filter, group, join, insert, delete, calculations, etc….
No code necessary!
This does not mean, VBA is out, because it has ist own advantages.
VBA can do everything, PQ can do everything with data handling.
PQ is definitely a great tool.
But personally I prefer having the SQL code when doing retrieval queries. You can see the query in one place and it is quicker and easier to make changes.
As always. Very clear explanation. Thnx
You’re welcome René
I have already had the opportunity to watch the webinar film.
Once again it was a very useful and clarifying webinar.
Well done Paul.
Thanks João.
That was a wonderful webinar Paul. Those who were familiar with RDBMS & SQL but didn’t know SQL is available for Excel too (through ADODB), will surely change their approach in developing Excel-VBA applications. SQL is so concise & versatile that, it will reduce the code volume drastically, eliminate the need of writing complex codes and would contribute in readability and maintainability of code too. Hope you will make more tutorial videos for addressing efficiently the issues/limitations related to Excel/SQL (e.g. output, formatting, performance, class/code library). Thanks again for your contributions for Excel VBA programmers!
Thanks Munim, Glad you enjoyed the presentation so much.
Paul great job as always 😉 Unfortunately because of ActiveX library I am not able to run this solution on macOS. I have to use only solutions that works fine within both operating system Mac & Windows. 🙁 Beside that I only wonder if it is possible to run other queries like SELECT or DROP with close filed like you have been presented with INSERT INTO example…
Once again, thank you very much Paul, it was a pleasure to be a part of your yesterdays webinar group…
Thanks Piotr. Yes, all the Select examples I used can be used with a closed file. I mentioned in the webinar that I was using the current workbook for examples for convenience. Otherwise I would have to keep opening the file. The selects will work on open or closed.
Thank you very much Paul for the answer Paul!!!
Greetings Paul! I’m watching the video on https://excelmacromastery.com/ado-powerful-queries/. How or where can I also download the source code for this video?
Cheers, -Hahns
Hi Hahns,
The code will be available for members after the deadline ends. See here for details about becoming a member. Note that with the current offer you get 2 months free.
Paul, I answered my own question by using the worksheets code-name rather than the display-name!
Mea Cupla!
THANKS!
Richard Lyon
No problem Richard.
Paul,
Great webinar. The webinar really got me up to speed. SQL saves a ton of development time by being able to describe the desired result set in a SQL statement as opposed to writing many lines of complex VBA code.
Thank you!
Richard Lyon
You’re welcome Richard.
Why are the Subs like Select and Useclass Private and not Public?
is it because you have used ADO?
It seems from your webinar and comments above that we can use SQL in VBA by adding ADO from Reference library. We don’t need to have Access or any other software, except for Excel in windows.right?
Paul, thanks a lot for your great webinar!
It is truly amazing to unleash all the SQL might on my tables ))
May I add though that writing headers one by one is rather slow on wide tables.
I’d suggest to replace the “Write out headers” code section with this:
Dim arrHdr() As String
ReDim arrHdr(1 To 1, 1 To rs.Fields.Count)
Dim i As Long
For i = 1 To rs.Fields.Count
arrHdr(1, i) = rs.Fields(i – 1).Name
Next i
shResults.Range(“A1”).Resize(ColumnSize:=rs.Fields.Count).Value = arrHdr
Thanks Mika.
For tables with lots of fields, arrays are definitely better!
Hi Paul – finally got around to watching this. WOW!
So I already use ADO to connect to SQL Server and read data from (and write data back to) the SQL Server tables etc.
Firstly I never knew you could do the same in Excel Files and I can see straightaway how useful that can be in terms of copying subsets of data or even manipulating it via SQL first – BRILLIANT!
The other thing I learnt is how to bring in the Field Names – in my SQL Server code I was always hard coding that. Time to update my code.
Thank you so much mate.
Thanks Simon. If you already have SQL skills then it’s very easy to use.
Hi- the class is great but i got stuck when replicating your examples at minute 17:40. The previous examples worked perfectly. I did created the class module and all the methods you described. However whenever i push the run button over he UseClass() module i got this error “user-defind type not defined”.