Thank you very much. I learned the knowledge about rs.field.count-1. I could use loop to get all the rs data put them any place in excel sheet's cells. Thank you again.
Thank you for this amazing series. I am at a binge-watching spree. At my new job, I am required to perform a lot of VBA and SQL-related stuff in which I had no experience. But your videos really helps! I had a question if it is possible to use multiple JOIN, INNER JOIN fetching data from multiple tables. At my work, this is done via creating a temporary table. Is something like this possible directly via VBA? Have you explained it in future videos? If you could just let me know if it's possible, I will research and find out myself somehow.
Hi Nelson! Yes indeed you can write joins in your VBA SQL queries. We have a series which covers writing SQL in VBA which you can see here th-cam.com/play/PLNIs-AWhQzckUd5i0E1xeSFeBAonYIurK.html The examples assume you're connecting to another Excel workbook as a data source but the principles apply to SQL Server as well. I hope it helps!
Hi Mallesh! We have a video on getting data from stored procedures which you can see here th-cam.com/video/hYq0ElVLZhg/w-d-xo.html You might find the link to the playlist useful as well th-cam.com/play/PLNIs-AWhQzclbRVLCZlsFvpz6fz2nPGbt.html I hope it helps!
Great 👍🏼 is it possible to to use late binding here. I may give excel sheet to an employee that sql data connection driver is not installed on his computer. Thank you.
Hi Abdullah, you can certainly use late binding but that wouldn't help if the database driver isn't installed on the client machine. You can still use the earlier OLEDB drivers for SQL Server if you need to, as described in this video th-cam.com/video/OWKae1pTTnE/w-d-xo.html So, instead of setting the provider to MSOLEDBSQL you could set it to SQLNCLI11 (assuming that the SQL Server Native Client dll has been installed on the client machine) or SQLOLEDB if you wanted to go old-school. Bear in mind that these older providers have been deprecated by Microsoft and aren't recommended for new development work (as described in the video I linked to above).
HI.. thank you.. i was just working in a project using this.. i have a question though.... i have a UDF in access using VBA.. and i use it to populate a particular query in access... then im trying to get this data into excel but i get the error undefined function "name of the function" in expression... what can i do?
Hi José, I'm not sure that it's possible to do this (I'll admit that I've never tried until you asked this question!) - there's a nice explanation of why it doesn't work in the answer to the question asked here stackoverflow.com/questions/25226635/vba-function-in-excel-adodb-query I hope that gives you some more information!
Hello, I appreciate your advice. I have an SQL (which works well in SQL Query) where there Left outer join used on 2 databases. In this question how would I construct the data source in VBA Language (if there are 2 databases), Connection String and how will SQL statement will be placed in VBA? I also need to use project numbers as variables or inputs. So I would like if SQL variables or inputs can refer to certain ranges in worksheet rather than me changing each time those projects in SQL itself. Thank you so much!
Hello I’ve got a bit of a complex question. I am pulling a stored procedure as part of my recordset, then after the data has been imported into excel, running a conditional statement to evaluate the data. It does not appear that the code is correctly identifying blank cells for the statement if (value) “” then else end if . However, when copy and pasting the data from the results grid in sql server, and replacing the NULL values with BLANKS in excel the code executes perfectly. Note: when querying the database using vba, it is set up so NULL does not carry over to the workbook.
Hi Abraham! How have you set up the code to not return NULL to the workbook? Are you using a replacement value instead? I'd use some text functions to work out what you actually have in the blank cells. LEN(Value) will tell you how many characters are in the cell as a starting point. I hope it helps!
Hi Andrew, first and foremost thank you very much for providing these tutorials to us all. I can safely say that I would be a great deal further behind if it weren't for you. I am uncertain if this is the way to potentially contact you regarding a query I have, but here goes. I am successfully connecting to and pulling data from an Azure SQL Database (Driver: SQL Server Native Client 11.0) using VBA and a SQL Query within VBA. 99% of everything is pulling through correctly, however for some reason the Email address field (column) of the records I am pulling through are not populating. The column header is pulling through 'Email', however no addresses are pulling through. I am unsure as to whether or not it is successfully pulling through to the Record Set or simply breaking when pasting to the specified range in an Excel sheet. Have you experienced issues before regarding email addresses or the " @ " sign when attempting something like this? Many thanks again, Shane.
Hi Shane, thanks for the comment, it's nice to hear that the videos have helped! I haven't encountered the issue you describe but I'll try to get some time to look into it this week - a bit tricky as I'm teaching courses all week but I'll do my best to get back to you if/when I discover something!
@@WiseOwlTutorials Thank you ever so much, I can provide you with any extra information should you require it at any point when you get some time to have a look. Good luck with the course teachings, if they are anywhere near as excellent as these videos are then those are some lucky attendees!
I'm trying to do the same as here with the SQL statements at the end. I am having trouble though as I tried to make a variable entered into an input box to be inserted into the where statement. I must have something wrong for the characters in there as it comes up with an error saying my variable, MyInputBoxVariable, is an invalid column name. Any ideas how to fix this? - GetDataFromSQLServer "Select Topic_ID WHERE Topic_ID = MyInputBoxVariable"
Hi Cameron, if you'd like to include the value of a VBA variable as part of your SQL string you need to concatenate it to the string rather than include it as part of the string. If your Topic_ID column has a number data type you can simply do this: "Select Topic_ID WHERE Topic_ID = " & MyInputBoxVariable If your SQL column has a text data type you'll need to include string delimiters around the VBA variable like this: "Select Topic_ID WHERE Topic_ID = '" & MyInputBoxVariable & "'" One further thought: although concatenating a dynamic SQL string is convenient it does leave you open to a few security issues. A safer way to do this is to parameterise your SQL string as shown in this (sadly underwatched!) video th-cam.com/video/I31Mh5-8Z1g/w-d-xo.html I hope it helps!
OK I got it! I was not concatenating right for sql string variable - '" & MyInputBoxVariable & "' is now there and works great. Thanks for your great videos.
Hi Andrew,
This is one of the best video training I have ever watched.
Very clear explanation, understanding each step and why selected.
Thank you :)
Great! Thanks you! I get the detail and understand it more!
You're welcome! Happy to hear that it helped and thanks for watching!
Thank you very much. I learned the knowledge about rs.field.count-1. I could use loop to get all the rs data put them any place in excel sheet's cells. Thank you again.
You're welcome Mark, thanks for watching!
Thanks Andrew.
You are very welcome, thanks for watching!
great course, you helped me a lot. Subscribed.
You're really awesome and fascinating
Ahh thanks!
Thanks for this!
You're welcome! Thanks for watching!
Hello in which part of your videos are explaining the difference between stored procedures and views in SQL Server?
Brilliant!
Thanks Alexander!
Thank you for this amazing series. I am at a binge-watching spree. At my new job, I am required to perform a lot of VBA and SQL-related stuff in which I had no experience. But your videos really helps! I had a question if it is possible to use multiple JOIN, INNER JOIN fetching data from multiple tables. At my work, this is done via creating a temporary table. Is something like this possible directly via VBA? Have you explained it in future videos? If you could just let me know if it's possible, I will research and find out myself somehow.
Hi Nelson! Yes indeed you can write joins in your VBA SQL queries. We have a series which covers writing SQL in VBA which you can see here th-cam.com/play/PLNIs-AWhQzckUd5i0E1xeSFeBAonYIurK.html
The examples assume you're connecting to another Excel workbook as a data source but the principles apply to SQL Server as well.
I hope it helps!
Thank you very much ! Can you create one video on How to get data from stored Procedure.
Hi Mallesh! We have a video on getting data from stored procedures which you can see here th-cam.com/video/hYq0ElVLZhg/w-d-xo.html
You might find the link to the playlist useful as well th-cam.com/play/PLNIs-AWhQzclbRVLCZlsFvpz6fz2nPGbt.html I hope it helps!
Great 👍🏼 is it possible to to use late binding here. I may give excel sheet to an employee that sql data connection driver is not installed on his computer. Thank you.
Hi Abdullah, you can certainly use late binding but that wouldn't help if the database driver isn't installed on the client machine. You can still use the earlier OLEDB drivers for SQL Server if you need to, as described in this video th-cam.com/video/OWKae1pTTnE/w-d-xo.html
So, instead of setting the provider to MSOLEDBSQL you could set it to SQLNCLI11 (assuming that the SQL Server Native Client dll has been installed on the client machine) or SQLOLEDB if you wanted to go old-school. Bear in mind that these older providers have been deprecated by Microsoft and aren't recommended for new development work (as described in the video I linked to above).
dont have activex library in my office how to get that plz help
again Thank you so much, sir
HI.. thank you.. i was just working in a project using this.. i have a question though.... i have a UDF in access using VBA.. and i use it to populate a particular query in access... then im trying to get this data into excel but i get the error undefined function "name of the function" in expression... what can i do?
Hi José, I'm not sure that it's possible to do this (I'll admit that I've never tried until you asked this question!) - there's a nice explanation of why it doesn't work in the answer to the question asked here stackoverflow.com/questions/25226635/vba-function-in-excel-adodb-query
I hope that gives you some more information!
@@WiseOwlTutorials Thank you :)
You're welcome José :)
just amazing again, How would it connect to the Oracle database??
Hi Alex! It's all about that connection string www.connectionstrings.com/oracle/
Hello, I appreciate your advice. I have an SQL (which works well in SQL Query) where there Left outer join used on 2 databases. In this question how would I construct the data source in VBA Language (if there are 2 databases), Connection String and how will SQL statement will be placed in VBA? I also need to use project numbers as variables or inputs. So I would like if SQL variables or inputs can refer to certain ranges in worksheet rather than me changing each time those projects in SQL itself. Thank you so much!
Hello I’ve got a bit of a complex question.
I am pulling a stored procedure as part of my recordset, then after the data has been imported into excel, running a conditional statement to evaluate the data.
It does not appear that the code is correctly identifying blank cells for the statement if (value) “” then else end if .
However, when copy and pasting the data from the results grid in sql server, and replacing the NULL values with BLANKS in excel the code executes perfectly. Note: when querying the database using vba, it is set up so NULL does not carry over to the workbook.
Hi Abraham! How have you set up the code to not return NULL to the workbook? Are you using a replacement value instead? I'd use some text functions to work out what you actually have in the blank cells. LEN(Value) will tell you how many characters are in the cell as a starting point. I hope it helps!
@@WiseOwlTutorials using a ado recordset and a temp table
Hi Andrew, first and foremost thank you very much for providing these tutorials to us all. I can safely say that I would be a great deal further behind if it weren't for you. I am uncertain if this is the way to potentially contact you regarding a query I have, but here goes. I am successfully connecting to and pulling data from an Azure SQL Database (Driver: SQL Server Native Client 11.0) using VBA and a SQL Query within VBA. 99% of everything is pulling through correctly, however for some reason the Email address field (column) of the records I am pulling through are not populating. The column header is pulling through 'Email', however no addresses are pulling through. I am unsure as to whether or not it is successfully pulling through to the Record Set or simply breaking when pasting to the specified range in an Excel sheet. Have you experienced issues before regarding email addresses or the " @ " sign when attempting something like this? Many thanks again, Shane.
Hi Shane, thanks for the comment, it's nice to hear that the videos have helped! I haven't encountered the issue you describe but I'll try to get some time to look into it this week - a bit tricky as I'm teaching courses all week but I'll do my best to get back to you if/when I discover something!
@@WiseOwlTutorials Thank you ever so much, I can provide you with any extra information should you require it at any point when you get some time to have a look. Good luck with the course teachings, if they are anywhere near as excellent as these videos are then those are some lucky attendees!
Hi Shane, just letting you know that I haven't forgotten about this, I just haven't had time to look into it yet!
How I can get the data of only 3-4 columns which can be any header from table? Please provide solution.
You can try using the SELECT statement in your source, with the required column headers in the FROM clause.
I have sql express edition, is that ok?
Yes, that's fine!
I'm trying to do the same as here with the SQL statements at the end. I am having trouble though as I tried to make a variable entered into an input box to be inserted into the where statement. I must have something wrong for the characters in there as it comes up with an error saying my variable, MyInputBoxVariable, is an invalid column name. Any ideas how to fix this? - GetDataFromSQLServer "Select Topic_ID WHERE Topic_ID = MyInputBoxVariable"
Hi Cameron, if you'd like to include the value of a VBA variable as part of your SQL string you need to concatenate it to the string rather than include it as part of the string. If your Topic_ID column has a number data type you can simply do this:
"Select Topic_ID WHERE Topic_ID = " & MyInputBoxVariable
If your SQL column has a text data type you'll need to include string delimiters around the VBA variable like this:
"Select Topic_ID WHERE Topic_ID = '" & MyInputBoxVariable & "'"
One further thought: although concatenating a dynamic SQL string is convenient it does leave you open to a few security issues. A safer way to do this is to parameterise your SQL string as shown in this (sadly underwatched!) video th-cam.com/video/I31Mh5-8Z1g/w-d-xo.html
I hope it helps!
OK I got it! I was not concatenating right for sql string variable - '" & MyInputBoxVariable & "' is now there and works great. Thanks for your great videos.
@@camerongreen988 Excellent, glad you got it working!
@@WiseOwlTutorials I'll give that other video a look as well and see if I want to change it over to parameterize it. Thanks for the heads up.
@@camerongreen988 Before watching, this article gives a nice summary of why it might be a problem www.w3schools.com/sql/sql_injection.asp