There is no one like you Andrew, you were put on this earth to teach, and you do so. So many thanks for your help.
@WiseOwlTutorials
2 жыл бұрын
Thanks David!
@teodorohturnos2042
3 жыл бұрын
Thanks Andrew. Another great video start of a super interesting series. I never used SQL until today and I most probably would not have started using it, if you would not do this superb VBA "Introduction"!!! videos.
@WiseOwlTutorials
3 жыл бұрын
That's great Teodor! I hope you enjoy SQL as much as VBA, there are lots more videos on this language on the way! And yes, I agree that "Introduction" is perhaps not the best name for this series any longer!
@teodorohturnos2042
3 жыл бұрын
@@WiseOwlTutorials HI Andrew, I have one question: Do you know whether it is possible to also reference the range of listobjects of closed workbooks. If I do not know the exact range and have more than one listobject per worksheet. Thanks for your reply
@WiseOwlTutorials
3 жыл бұрын
@@teodorohturnos2042 Hi Teodor! You can't reference a ListObject in a closed workbook - unfortunately ListObjects aren't exposed to the tables schema so your SQL statement can't reference them directly. You could open the workbook and refer to the ListObject's Range property and then the Address property to return a string which you could then concatenate into your SQL statement. But, of course this means that you would need to open the file.
@youssefsedkey7616
3 жыл бұрын
The most beautiful thing is that you walk step by step with the learner and I couldn't find anyone explaining a lesson in all these details ... Andy, please keep going in this way.
@WiseOwlTutorials
3 жыл бұрын
Thank you Youssef, I'm very happy to hear that you enjoy the way we do things! Thank you for your comments and support!
@peterM20
3 жыл бұрын
Andrew, you are a gift that we don't deserve. I know I have told you this countless times but no matter what we say to express our appreciation of your work, it will never be enough. thank you for all that you do. your delivery is unmatched in all of KZitem.
@WiseOwlTutorials
3 жыл бұрын
Thank you so much for the kind words Peter!
@snjpverma
Жыл бұрын
I second your words
@bondniko
3 жыл бұрын
Hi there! I have nearly screwed my report up this month as a result of the way ado assigns data types to recordset built on excel sheet. There is a column with cost center codes in my excel source, some of which contain letters and some contain numbers only. The whole thing is supposed to be text. Unfortunately, this time the first cost center code was the one without letters. So, the query returned only number-looking codes and assigned null to all the others. IMEX=1 did not work. It was only when I selected the entire column and applied text format the problem was solved. It is a dangerous pitfall. Could you make a video on how to treat such and other tricky situations?
@WiseOwlTutorials
3 жыл бұрын
That's a great comment, thank you for sharing. A video on the topic is a good idea, I'll add it to the list. In the meantime you might find these two links useful: stackoverflow.com/questions/35291171/imex-1-seems-to-have-no-effect www.etl-tools.com/imex1.html
@jmathew6988
Жыл бұрын
I wanted to write a comment as a thank you for this tutorial. I also happened to read through some of latest nice comments left by previous viewers and discovered that those are exactly the impressions which I echo. 🙂 Sir, you truly are a blessing to students. I just cannot thank you enough for the immense help that WiseOwl VBA tutorials have been for me.
@WiseOwlTutorials
Жыл бұрын
Thanks so much!
@RohithKK-uh7pp
3 жыл бұрын
Excellent. I have learnt many things. Much more thanks.
@WiseOwlTutorials
3 жыл бұрын
As always Rohith you are very welcome, thank you for watching and for your comments!
@rosspaulson4382
2 жыл бұрын
These Videos are AMAZING!! been looking to do this for years. at my company we have been saving text files from out SQL program and then importing the csv file(s) into Excel. When i get this implemented it will save tons or time for us! I do have a quick question. This works on my computer just fine with our database. When I run it on a colleagues computer it would fail on the cn.Open line, I downloaded and install the "Microsoft OLE DB Driver for SQL Server" (msoledbsql.msi) driver. and still no luck. I finally go it the work by installing (SQL Server Management Studio (SSMS)) the the machine. D o I need to install SSMS on every workstation that plans on using the excel worksheet?
@WiseOwlTutorials
2 жыл бұрын
Hi Ross! Installing the MSOLEDBSQL driver as described in video 56.1 in this playlist should have been sufficient kzitem.info/door/PLNIs-AWhQzclbRVLCZlsFvpz6fz2nPGbt It's curious that this didn't work for you, installing SSMS shouldn't be necessary. Just for my own curiosity, can you remember if you restarted the machine after installing the MSOLEDBSQL driver before running the VBA code?
@kevinerose
3 жыл бұрын
Playback speed = 0.75 if you aren't from UK.
@kashifkhanspecial
3 жыл бұрын
Hi Andrew, Masterpiece what can I say, you described each and every topic in this video in details, I congratulate you on this, I am very much interested in this series (sql in excel vba for excel), I am waiting for the video where that how can we use case statement in excel sql, just like we use case statement in sql server query. Once again thank you so much for this video, please take good care of yourself and your family, stay safe and healthy, God bless you. Thanks Kashif
@WiseOwlTutorials
3 жыл бұрын
Thank you Kashif, I'm happy to hear that you enjoyed this video! Unfortunately, the ACE OLEDB provider which we use to connect to Excel files does not support the CASE expression. You can use the SWITCH function to produce similar results, here is one example, I hope it helps! SQLQuery = "SELECT [Title], [Run Time], SWITCH([Run Time] < 100, 'Short', [Run Time] < 150, 'Medium', [Run Time] < 200, 'Long', True, 'Very Long') AS [Film Length] FROM [Film$]"
@kashifkhanspecial
3 жыл бұрын
Oh, I didn’t know that, thank you so much for your reply and for your time that you took to write the example query, I appreciate your help. God bless you. Thanks Kashif
@WiseOwlTutorials
3 жыл бұрын
@@kashifkhanspecial My pleasure Kashif, thank you for your comments and support!
@ouzytheoriginal
7 ай бұрын
why would someone need sql excel, its better to use with other softwares ;)
@busybillyb33
9 ай бұрын
5:30 This is exactly what I needed! I was referred to this video by you from a question I had asked in another tutorial. You have an extensive collection of topics that sometimes it is not so much as me not being able to look for what I needed but it is that I don't know what I should be looking for. Thanks for making enough sense of what I was asking to point me to the right place!
@WiseOwlTutorials
9 ай бұрын
Happy to hear that it helped! Thanks for taking the time to let me know!
@GeoffroyVerzeaux
3 ай бұрын
Hi Andrew. I've benne using so many times your lessons that i fell like we know each other since 30 years!!!!! Thank you! I'm facing a new issue. Trying to delete a row in a database, i've got this error: 2147467259 ISAM pilot. Do you know how i can fix this please ?
@WiseOwlTutorials
3 ай бұрын
Hi! Thanks for watching so many of the videos! Unfortunately, the SQL DELETE statement for deleting rows in a worksheet using ADO isn't supported. You can use the INSERT and UPDATE statements however, as later videos in this playlist show kzitem.info/door/PLNIs-AWhQzckUd5i0E1xeSFeBAonYIurK&feature=shared
Thanks Sam, glad you enjoyed it and thanks for watching!
@frikduplessis8849
3 жыл бұрын
Thank you Andrew, this is what I was waiting for I really appreciate the detail and effort you have put in with this wonderful article, I surely hope you are just building up to something special,👏👏👏👏👏👏 thank you again👏👏👏👏👏
@WiseOwlTutorials
3 жыл бұрын
Thank you Frik! There's plenty more to come in this series - a few more videos of the basics on Order By, Top and Distinct followed by the good stuff on Where, Group By, Having, Join, Union, expressions, aggregate functions and then onto manipulating data!
@frikduplessis8849
3 жыл бұрын
@@WiseOwlTutorials this is awesome news Andrew I can't wait, you have made so many passed frustrations clear, as a newest I sometimes give up until I watch your tutorials then I get inspired again 😀
@WiseOwlTutorials
3 жыл бұрын
@@frikduplessis8849 Thank you Frik, I'm really happy to hear that these videos are helping. Hopefully we can solve some more frustrations soon!
@frikduplessis8849
3 жыл бұрын
@@WiseOwlTutorials Good afternoon Andrew, I figured out its possible to make the SELECT statement dynamic, it's my contribution for a change 😀 I'm sure its going to help someone else 🤞 lr = Day(Now()) Dim rg As String rg = "A" & lr & ":" & "L413" 'rg = "A238:L413" "Select * from [MySheet$" & rg & "]"
@thwbn3993
3 жыл бұрын
I wished teachers in my son's school would teach only half the quality level you do! Thanks a ton! Done again!!
@WiseOwlTutorials
3 жыл бұрын
Thank you, I'm happy to hear that you found it useful! Thanks as always for the support!
@efernandes67
3 жыл бұрын
Great as always! Awesome presentations and explanations. Keep the good work. "See You" on the next one.
@WiseOwlTutorials
3 жыл бұрын
Thank you Elio! Happy to hear that you enjoyed it and many thanks for your comments!
@imranbhatti8580
3 жыл бұрын
Thanks, Andrew! This video answered a lot of my questions before asking.
@WiseOwlTutorials
3 жыл бұрын
My pleasure Imran, happy to hear that you found it useful!
@bondniko
3 жыл бұрын
Nearly 6,5 bln people on earth have no idea about sql. Poor barbarians
@WiseOwlTutorials
3 жыл бұрын
😀 now if you can think of a way to get 6.5 billion people to watch this video I would be very happy!
@naiduvikas202
7 ай бұрын
7:00 In this place I want to use a range like the last used row in that specific table like Range("A2:D" & Last-row). Could you please suggest how we can achieve this criteria by using record set
@WiseOwlTutorials
7 ай бұрын
Hi! You'd need to have the source file open to do this sort of thing. There are various ways to find the last used row of a worksheet kzitem.info/news/bejne/0X9o1qxohKGSo4ofeature=shared Once you've found the last cell you can concatenate its address into the query. I hope that helps!
@PankajNegi007
3 жыл бұрын
Great Sir as always sir .. I really enjoy this session thanks for sharing your knowledge with us. As per your website you learn Python so please start new series of Python.
@WiseOwlTutorials
3 жыл бұрын
Thank you Pankaj, I'm happy to hear that you enjoyed it! We do plan to have Python videos but it will be some time before these will appear - at the moment we're concentrating on preparing the materials for the delegates who will be attending our first Python courses later this year. Thank you for your comments and support!
@rajb4549
2 жыл бұрын
On sample files when Pressing the Run Query - I get an error 3706 - Connection failed to open. Provider cannot be found. It may not be properly installed. I am running Excel 2007
@WiseOwlTutorials
2 жыл бұрын
Hi Raj, you can try installing this www.microsoft.com/en-us/download/details.aspx?id=54920 I hope it helps!
@alexsandoval7636
3 жыл бұрын
24 / 5000 beautiful way of teaching ...
@WiseOwlTutorials
3 жыл бұрын
Thank you Alex!
@KhalilYasser
3 жыл бұрын
Awesome. Thank you very much for these amazing tutorials.
@WiseOwlTutorials
3 жыл бұрын
My pleasure Yasser, thank you as always for your support and kind words!
@IsHardynafthardynaft
2 жыл бұрын
hey, Thank yOU
@WiseOwlTutorials
2 жыл бұрын
You're welcome, thanks for watching!
@krn14242
3 жыл бұрын
Thanks Andrew, was looking forward to this series. :)
@WiseOwlTutorials
3 жыл бұрын
My pleasure Kevin, hope you find it useful!
@venusflytrap909
2 жыл бұрын
Is it possible to set the SQLQuery to range("A1"). crurrentregion of the closed file if you will not know the closed file sheetname
@WiseOwlTutorials
2 жыл бұрын
Hi! No, you can't use CurrentRegion if the Excel file is closed.
@juanpablohorn6642
3 жыл бұрын
Such a great video! I'm eager to see how this series unfolds! I find it really useful!
@WiseOwlTutorials
3 жыл бұрын
Thank you Juan Pablo, I hope you enjoy the other videos when they appear!
@amoasena
2 жыл бұрын
Hi. I tried to open my connection with those connection string parameters, but the following error rises: Could not find installable ISAM. Do vou know what can be causing this error.
@WiseOwlTutorials
2 жыл бұрын
Hi Antônio! I'm not sure, it could be that your connection string refers to a version of the ACEOLEDB provider that is different to the one you have installed. You could try some of the suggestions here stackoverflow.com/questions/512143/error-could-not-find-installable-isam I hope it helps!
@cpro671
3 жыл бұрын
Great setup and intro to the new series; looking forward to the next episodes...
@WiseOwlTutorials
3 жыл бұрын
Thanks Charlie, happy to hear that you enjoyed it and thank you for taking the time to leave a comment!
@michaeld9149
3 жыл бұрын
Jeez..enough ads for ya?
@piotrkuc8936
3 жыл бұрын
Thank you! Looking forward to learn more!
@WiseOwlTutorials
3 жыл бұрын
Thank you Piotr!
@jorgeluisrodriguezescudero2032
3 жыл бұрын
I have only one thing to say: thank you Andrew. After i saw your videos, i only know that i know nothing .
@jorgeluisrodriguezescudero2032
3 жыл бұрын
and i´ve learned a lot
@WiseOwlTutorials
3 жыл бұрын
My pleasure Jorge! I'm happy to hear that you found the video useful and thank you for taking the time to write a comment!
@JoshuaCardenas-b8u
Жыл бұрын
What if you want to extract data from within the same workbook while it is open?
@WiseOwlTutorials
Жыл бұрын
Hi! You can just point to the path of the open workbook, MovieFilePath = ThisWorkbook.FullName Hope it helps!
@MarkJasonLleno
2 жыл бұрын
Thank you once more sir andrew. Watching different playlist is your channel is like watching another wise owl season. I haven't watch all yet but this episodes are very interesting and part of the lessons that i really wanted to learn. I would just like to ask if we can delete an entire row in a close workbook using this method? Because in access you have shown us the technique. I am not sure if it is also in this playlist. If it is possible can you show us the way? I have tried the method you have shown in sql server but when i tried to use it in excel it did not work. Thanks in advance.
@WiseOwlTutorials
2 жыл бұрын
Hi Mark! No, you can't delete rows in an Excel workbook using ADO, sorry!
@MarkJasonLleno
2 жыл бұрын
@@WiseOwlTutorials Thank you. So I think I just need to use the old fashion way. 😅 Anyways if you still remember i was the one who asked you about the google drive. The reason for that is to make the target database file location accessable online but i did not know yet before about SQL Server that it was the solution i needed in the first place maybe it's because i was happy enough to learn ADO for Access. I have though that if i will make a database for myself only then i will use the [Excel, Excel using ADO] and if for multiple users using LAN then [Excel, Access using ADO] and if online then [Excel, SQL Server] but i have figured out that port forwarding is required in order to make it accessable to different computers online. I am not sure if i am right about this. I did try my luck if i can work it out but someone told me that public IP is needed in doing port forwarding and it is for business type of internet connection only and not for private such as residential internet connection type. Is there an alternative way to make it accessable online using residential type of internet connection? Thank you in advance and Thank you for reading this. My goal is to learn all the methods in your channel. Your owl is the wisest and best owl ever. 😁
@WiseOwlTutorials
2 жыл бұрын
@@MarkJasonLleno Hi Mark! I don't know enough about this to give you good advice, I'm sorry!
@MarkJasonLleno
2 жыл бұрын
@@WiseOwlTutorials Thank you. 😊
@yasminalsham5619
3 жыл бұрын
Bunch of thanks Sir
@WiseOwlTutorials
3 жыл бұрын
You're very welcome Ysamin, thank you for watching!
@oguzturkyilmaz543
2 жыл бұрын
Thank you very much
@WiseOwlTutorials
2 жыл бұрын
You're very welcome, thanks for watching!
@savitatanwar121
2 жыл бұрын
Hi I recently viewed your videos are very simple, can you please share how to query on password protected excel file.
@WiseOwlTutorials
2 жыл бұрын
You can see a workaround here www.connectionstrings.com/how-to-open-password-protected-excel-workbook/ I hope it helps!
@savitatanwar121
2 жыл бұрын
@@WiseOwlTutorials thanks
@perfoperfo9910
7 ай бұрын
Hello WiseOwl great tutorials thanks. Could you point me to one that may help me sorting a problem out. It looks like I can get data from a worksheet using SQL and I can write data to an SQL DB. But is there a way to do a select statement on a worksheet to extract some data and then insert that directedly into an SQL table with VBA ? i.e. not copy it to another worksheet then use that to get the data to put in to the SQL table. Thanks ..
@WiseOwlTutorials
6 ай бұрын
Hi! You should be able to do it with something like this: Sub Insert_to_SQL_From_Excel() Dim cn As ADODB.Connection Dim cmd As ADODB.Command Set cn = New ADODB.Connection cn.ConnectionString = "Provider=MSOLEDBSQL;Server=ServerName\InstanceName;Database=MyDatabase;Trusted_Connection=yes;" cn.Open Set cmd = New ADODB.Command cmd.ActiveConnection = cn cmd.CommandText = _ "INSERT INTO dbo.TableName([ColumnName1], [ColumnName2]) " & _ "SELECT [ColumnName1], [ColumnName2] FROM OPENROWSET(" & _ "'Microsoft.ACE.OLEDB.12.0','Excel 12.0 Xml; HDR=YES; IMEX=1; Database=C:\MyFolder\Book1.xlsx',[Sheet1$]);" cmd.Execute cn.Close End Sub But there are all sorts of potential problems. You need to ensure ad-hoc distributed queries are enabled on the server learn.microsoft.com/en-us/sql/database-engine/configure-windows/ad-hoc-distributed-queries-server-configuration-option?view=sql-server-ver16 And I encountered numerous other problems stackoverflow.com/questions/26267224/the-ole-db-provider-microsoft-ace-oledb-12-0-for-linked-server-null Let me know if you have any luck!
@perfoperfo9910
6 ай бұрын
@@WiseOwlTutorials Great answer thanks. I haven't got any sort of admin rights to the server so can't fiddle with any settings. The code above doesn't appear to work as yet but then I have a lot more fiddling to do before I'm confident I haven't done something wrong:-) . I have a semi working version but using a recordset. So do a select query on the work book , put the results in to a recordset then loop through the record set inserting the data in to my SQL table. This is better than using a n intermediate worksheet but obviously not as good as going direct from one to the other. Out of interest would a where a=a etc statement just tag on the end of that command text ?
@perfoperfo9910
6 ай бұрын
yep my problem is my server doesn't allow ad hoc connections. My Recordset intermediate is working so I guess that will have to do. Have you a tutorial on how to do this with an open workbook ? These are quite complex subject (well for me they are) and you've found a way of explaining them clearly..so thanks again...
@ivanchan7769
2 жыл бұрын
Thank you for your amazing tutorials on Excel SQL Andrew. I have learnt a lot from that. But I would like to ask a problem about the max numbers of fields being got from adodb. I have a source excel file which contains over 300 columns. If I use "Select * from [XXX$]" to get the result from the source file, only first 255 columns data will be copied to new datasheet. How can I get all data from the source sheet? Thank you very much.
@WiseOwlTutorials
2 жыл бұрын
Hi Ivan! Sadly there isn't a convenient way to get around this limit of 255 columns. The simplest approach would be to break up your query into multiple smaller queries and either reference the columns by name in your SELECT list, or refer to a specific range of cells in the FROM clause. I hope it helps!
@amirfa4866
3 жыл бұрын
Thank you for awesome videos What if Excel header rows are Japanese ? For example does this SELEC query works ? SELECT [オスカーが勝つ] , [映画] From [Sheet1$] WHERE [オスカーが勝つ]>1
@WiseOwlTutorials
3 жыл бұрын
Yes it would but you won't be able to write it directly in the VB Editor. You could put the query in a cell in a worksheet and refer to the Value or Text property of the cell to set your query string: SQLQuery = Worksheets("Sheet1").Range("A1").Value
@MrSantoshKm
2 жыл бұрын
Thank you so much for these magical videos. It's really very helpful. I have one query:I have mixed data (Numbers and Strings) in same column but this SQL connection look for few initial rows data type and converts the entire column as Number format. Therefore, some texts available in same column are treated as null value. How can we get both values from same columns?
@WiseOwlTutorials
2 жыл бұрын
Hi Santosh, take a look at this connection string www.connectionstrings.com/ace-oledb-12-0/treating-data-as-text/ I hope it helps!
@johnmutethia3060
3 жыл бұрын
Well explained! Please can you make videos on how to import /export excel from/ to sql server in c#
@WiseOwlTutorials
3 жыл бұрын
Thank you John! I will add your suggestion to the list of questions but please be aware that it is a very long list!
@chenghonggan824
3 жыл бұрын
Good video
@WiseOwlTutorials
3 жыл бұрын
Thank you!
@arekkotrys5457
2 жыл бұрын
Super tutorial. I am very grateful for this series. Is it possible to retrieve data from the table object?
@WiseOwlTutorials
2 жыл бұрын
Hi Arek! No, Excel table names can't be used, it's worksheet names, range names or cell references. If you have the source workbook open you can read the Address property of the Range property of the ListObject to get the cell reference docs.microsoft.com/en-us/office/vba/api/excel.listobject.range
@abdelrhmanmohamed678
Жыл бұрын
Hi Andrew, I tried to connect to a dynamic named range that uses the offset formula but it fails, also happened when I tried to connect to Excel dynamic tables. do you have any ideas to overcome
@WiseOwlTutorials
Жыл бұрын
Hi! Sadly neither dynamic named ranges nor Excel tables work in this context. You'd need to open the workbook to get the address of the table object or the name object in order to reference it in the SELECT statement.
@chaitanyajoshi690
4 ай бұрын
@@WiseOwlTutorials Hi Is there any work around to refer to an Excel table and not an Excel Sheet from a closed workbook. It is needed when we have multiple tables in a single worksheet and we want to refer to a particular excel table as source. Kindly guide
@WiseOwlTutorials
4 ай бұрын
@@chaitanyajoshi690 Hi! No, none that I know of sorry.
@chaitanyajoshi690
4 ай бұрын
@@WiseOwlTutorials Thanks..... I have been regularly learning from your videos and I really appreciate them.
@WiseOwlTutorials
4 ай бұрын
@@chaitanyajoshi690 Happy to hear the videos helped, thanks for watching!
@dandeman1148
Жыл бұрын
Thanks Andrew. I seem to have an issue using code from here and other videos when the files are in Sharepoint or OneDrive. In this video I get Run Time Error 53 Error Cannot Find File on this Line If Dir(MovieFilePath) = "" and in Video Get Data from a closed file I get RunTIme Error -2147... Failure creating file on this Line cn.Open If I copy the files to My Documents i.e. C:\My Docs\My Files it works fine when referring to ThisWorkbook.Path & "\Movies.xlsx but in one Drive folder I get the errors. Only way I can get it to work in One Drive is using the full path instead of thisworkbook.path
@WiseOwlTutorials
Жыл бұрын
Hi Dan! I think the simplest solution here is to use the full path but if you're interested in some background on this issue there's a pretty comprehensive write up here stackoverflow.com/questions/33734706/excels-fullname-property-with-onedrive I hope it helps!
@sabbirmoula
3 жыл бұрын
Very useful. Will it be possible to extract data from closed CSV or TEXT file and use SQL?
@WiseOwlTutorials
3 жыл бұрын
Hi Sabbir, it certainly is! kzitem.info/news/bejne/t22J2aB_rGl5dH4 I hope it helps!
@sabbirmoula
3 жыл бұрын
@@WiseOwlTutorials This is really helpful. Thanks a lot !!
@WiseOwlTutorials
3 жыл бұрын
@@sabbirmoula You're very welcome, thanks for watching!
@deepaksiva7833
3 жыл бұрын
Hi sir, Can you post a tutorial about race bar graph, TIA
@WiseOwlTutorials
3 жыл бұрын
Hi Deepak! I had to Google what a race bar graph is! So you'd like an animated bar chart using VBA, is that correct?
@deepaksiva7833
3 жыл бұрын
@@WiseOwlTutorials yes animated bar chart , may be by using VBA, google once you will get good idea about this, can you post a tutorial on this, TIA
@WiseOwlTutorials
3 жыл бұрын
@@deepaksiva7833 I will add this topic to my list but it will be some time before I get chance to create a video tutorial on this.
@deepaksiva7833
3 жыл бұрын
@@WiseOwlTutorials Thanks a ton, waiting for the tutorial
Пікірлер: 123