Thank you Andrew for an brilliant article, I also see I'm not the only one that was inspired by your video's, and thanks for the suttle query information 😃 as requested
Thank you very much Andrews. I can't tell how you've been making me an Excel expert (at least in my environment). This tutorial came right in when I needed it, and I'm more than grateful. Yet I'm still a bit hungry for more: how do I make it work when the data are in protected worksheets? Your answer will save my life. Have a great day!
Hi Lossan! Happy to hear that you're finding the videos useful. If your worksheets are protected, that's OK, you can read data from a protected worksheet using the same code shown in this video. If it's the workbook which is protected, i.e. you need to enter a password to open the Excel file, unfortunately this won't work. The generally accepted workaround appears to be opening the workbook using either the Workbooks.Open method docs.microsoft.com/en-gb/office/vba/api/Excel.Workbooks.Open or the GetObject method (although this will prompt you for the password) www.connectionstrings.com/how-to-open-password-protected-excel-workbook/ and then use ADO to work with the opened file. Not ideal I appreciate, but I don't know of another method!
@@WiseOwlTutorials Wow! I can't believe you answered so fast. Thank you very much indeed! It's the sheet that's protected, not the workbook. So maybe the error I come across is about the [sheet1$] issue. I saw someone already asked a question about it. I'll try to exploite the answer you gave him.
@@WiseOwlTutorials Hi, Andrew. Unfortunately it did not work. Actually, the files I want to retrieve data from are macro enabled workbooks containing 5 sheets each. I copyed the ConnectionString for macro enabled workbooks and adapted it as you esplain. The data are on sheet 3 (Feuil3 _ I'm using a french version of Office 13). I wrote the routine with [Feuil3$] but when I run through it with F8, it halts at rs.Open ... . [Sheet3$] won't work either. I figured using the name of the sheet ([Compilation$], ["Compilation"$]) but it still won't accept that.
@@Robertyaro Hi Lossan! That's frustrating. I wonder if you can try extracting the list of worksheet names from the tables schema of the file. This part of another video that I made explains how to do that ua-cam.com/video/5F8q7Z9kWGw/v-deo.html I hope it helps!
Hi Andrew, I really do enjoy all Your videos, and I have learn a lot from them. This is my very first comment on any of those, so now I take the chance to say a really big-big and respectful THANK YOU for all the efforts You've put in all these videos! I am a huge fan of these. I have just put my question on an other video, but I've found this one at the same time, which is exactly the answer to my Q. So I deleted it. But I think You still deserve the compliment! :) Greetings from Hungary!
@@WiseOwlTutorials Ah yes, these banana times. You're making great smothies with them in my opinion! In the words of Neil Armstrong: "A loss for the UK but a win for the WorldWideWeb" or something like that. Thanks
First I have to tell you that you changed my career, thanks to your video I got the VBA skills that allowed to reorient my career to VBA developper in finance (I have a master degree financial engineering). I have 2 questions for you: - Is it possible to store those data directly in an array WITHOUT copying them first on the worksheet ? - Will you (Please CAN YOU) start back your videos on C# and go into more advanced programming ? (there are so many tutorial videos out there, but truth is you're really the best for teaching it, and I mean it, many ppl have already told you so)
Thank you so much for the kind comments! I'm really happy to hear that the videos have helped you so much. To your questions: Yes you can write recordsets to arrays like so: Declare an empty Variant first: Dim v As Variant Use the GerRows method of the Recordset to return an array: v = rs.GetRows The array is organised so that the first dimension contains the columns and the second dimension contains the rows. I hope that helps! The C# tutorial was never very popular in terms of the number of views which is why we stopped adding videos. I enjoy writing C# and I'd like to make more videos in the series but time is the enemy! Thank you again for the comments, I really appreciate you taking the time to write it!
@@WiseOwlTutorials Thank you very much for your answer. I do insist to make you realize that your videos changed my career, so I'm extremely thankful and mean it when I say that your videos are the best to learn VBA, SQL, Access... and so forth. Keep up the good work, I'll keep promoting your videos
@@WiseOwlTutorials I found doing this way you need to transpose the array to get it into a 2D array and to look and work like an array populate from a range. I was reading from a csv file which I believe does behave different maybe that's why.
@@chrisrobbo1974 Hi Chris, thanks for sharing your experience, it's really helpful when people do this! And yes, the same thing happens with GetRows if you're using Excel as your source as well (it's why I made sure to say which dimension contains the columns and which contains the rows because this has tripped me up in the past too 😀) I've got a couple of short videos coming up which show how to use GetRows to populate an array and how to populate a listbox on a user form where Transpose was necessary too!
Thanks Anil! I would love to be able to release a video every day but unfortunately I have to do my job as well! I'm happy that you like the videos and thank you for taking the time to leave a comment!
Hello Andrew Thanks soo much for the amazing videos .Your teaching style makes understanding a lot easier. I'd like to know if there is a video that shows how to Transfer Data from a Master File in a workbook to multiple other workbooks if a condition is met. We are eternally grateful! Shaun
Thanks Shaun, happy to hear you've found the videos useful! This video covers how to do what you've described ua-cam.com/video/YkiphvICUSo/v-deo.html It's part of a playlist which you might find useful to bookmark ua-cam.com/play/PLNIs-AWhQzckUd5i0E1xeSFeBAonYIurK.html I hope it helps!
Thanks Budddy, you have a great work!!! I am an old man, so my eyes very very have hard work to watch what you wrote. Can you make the font bigger as big as possible you comfort with that? My eyes will more enjoyed with that ... have a good day buddy Thank you so much
Thanks for the feedback! I have increased from the default font size of 10pt to 14pt but I'll see if there's scope for a larger font. There's a balance to find when we're showing longer procedures to make sure we can see enough text on screen without needing to scroll but I'll see what we can do!
Hello, your videos are awesome! The method of explaining is just right. I have a question though. I have a slightly different problem. I have to copy a certain cell value (say "SheetName"!C32) from about 2000 workbooks and I need to paste it all into one workbook (1st column workbook file name, 2nd column cell value". How would I do that? And the kicker is, not all workbooks in the folder have the sheet (so I have to put them in 2nd column in red and leave blank). I was looking online and everyone recommends ExecuteExcel4Macro(argStr), but I have problem with it that when string argument of that function is more than 255 chars, it encounters error. I see you're doing it with ADODB, so I guess that could be used in my example as well? I managed to solve everything and is working for files which argStr is less than 250, but I still have about 500 workbooks for which this doesn't work due to error mentioned above. Can you please help me out?
can you create a keyword search with multiple closed excel files, extracting the results of the keyword search into a new sheet. For example, I have multiple sheets (huge data within each file) with same headers. Similar to what you've done here with the multiple closed excel files, is it possible to create a keyword search of each closed file, and list the results in a new sheet? thank you, love your tutorials!
A few days ago I tried to create an "INSERT INTO ... FROM SELECT" in an access table that takes data from an excel workbook and I couldn't find the syntax. Would you be so kind as to make a video about that if you like?
Hi Luciano! I think that you'll find this video helpful ua-cam.com/video/mKZeKf7bE8w/v-deo.html The section starting at 9:31 shows how to use INSERT INTO SELECT correctly. I hope it helps!
You're very welcome! And yes, you can use late-binding to avoid explicitly referencing the ADO library - see this section of an earlier video for a brief explanation ua-cam.com/video/HE9CIbetNnI/v-deo.html Beware that the ADO library still needs to exist on the target machine. I hope that helps!
Its really very Nice video, I have tried to create the connecton but getting Run Time error stating External Table not in expected format. Please help me and assist with the error
Hello Andrew! An awesome video like always. Yes, there are questions. 1) How can we print the Source Filename with each line of the record, so that we can have an idea of which record is from which file (just like PQ)? 2) Can we use the full power of the SQL Queries via this library like, Inner join/grouped by, etc?
Hi Imran! Questions are always good! 1) Concatenate the MovieFileName variable as an extra column into the SELECT list of the SQL statement: rs.Open _ Source:="SELECT [Sheet1$].*, '" & MovieFileName & "' AS FileName FROM [Sheet1$]", _ ActiveConnection:=cn 2) Yes! There are some differences to the SQL you may have used in other applications. For example, if you join more than two tables you must enclose each separate join in a set of ( ) parentheses, e.g. rs.Source = _ "SELECT f.Title, f.ReleaseDate, f.RunTimeMinutes, g.Genre, d.FullName, s.Studio" & _ " FROM (([Film$] AS f" & _ " INNER JOIN [Genre$] AS g ON f.[GenreID] = g.[GenreID])" & _ " INNER JOIN [Director$] AS d ON f.[DirectorID] = d.[DirectorID])" & _ " INNER JOIN [Studio$] AS s ON f.[StudioID] = s.[StudioID]" I hope that helps!
Thanks Andrew for the detailed answers. For question 2 , so we can use all SQL queries but with a difference of syntax that needs to pick up from any resource. Once again thanks.
@@imranbhatti8580 No problem Imran! I'm reluctant to say "all SQL queries" because different SQL dialects support different features and I don't know which versions of SQL you're familiar with. But you can certainly do all the standard things that you mentioned (joins and group by). And yes, there may be other syntax differences but the parentheses around joins is the main one which catches me out!
Hi Andrew, This is one of the best and simplest explanations I've encountered so far. Thank you so much for this.👌👍 Can you guide on how can I modify the code to fetch a specific cell range (e.g. C13:S38) from a common worksheet (Sheet2) of all files?
Hi Harshit, you can find some information on how to do that in the first video of this playlist ua-cam.com/play/PLNIs-AWhQzckUd5i0E1xeSFeBAonYIurK.html I hope it helps!
@@WiseOwlTutorials Thank you Andrew. I just saw that video after reading few comments posted earlier. I came up with this: rs.Open Source:="SELECT * FROM [OPT_ECO$C13:S38]", ActiveConnection:=cn can you suggest how can I define sheet name (OPT_ECO) and range (C13:S38) as variables SourceSheet and SourceRange respectively so that I can put an input in the worksheet for the user to feed in these details and based on that input macro processes. How can I refer these input variables in the above statement?
show the video and it is very good i got what i wanted exactly but i have few modification can you help me out. how can i add cell reference for MovieFileName and in rs Source in condition where a cell value is defined so as we change a cell value condition changes accordingly.
Hi. Great videos as always although I am struggling atm.. can this be adapted for multiple individual cells (not 'currentregions' and sheets of a closed workbook to defined cells/sheets of my open one? Many Thanks
Hi Phil! We have a separate playlist covering how to write SQL for Excel files. The first video shows how to reference sheets, cells and named ranges which you might find useful ua-cam.com/play/PLNIs-AWhQzckUd5i0E1xeSFeBAonYIurK.html I hope it helps!
Hello! I have a workbook on a network drive. When the code runs the cn.open, it opens the workbook and recordset won't open. Can it be because someone had opened it before I ran my code? I made a copy of the workbook and the same code runs seemlesly.
Hi there! That's a good question and it seems a strong possibility. There are some suggestions in this post which may be useful www.vbforums.com/showthread.php?395528-Problem-with-multiple-users-accessing-an-excel-sheet-through-ado I hope it helps!
Hello Andrew. When I followed along with this tutorial, the number of files was nearly 20 and it was working fine. However. today I run it on a folder with more than 70 files and I am getting the error "Query is too complex". I know the SQL Query string length could be up to 65000 characters whereas mine has 44000+ characters. Is the length limit for VBA ADO Query string have its own limitations?. and we should be running the query in chunks?
Hi Imran! This error is more likely to be linked to the number of tables/fields/joins in the query than the length of the query string. Have a look at the "Query" section on this page support.microsoft.com/en-us/office/access-specifications-0cf3c66f-9cf2-4e32-9568-98c1025bb47c
thanks Andrew. However I fixed it using chunks. like this If Len(SQLString) > 10000 And Not TaskFile = "" Then
cn.Open Set rs = CreateObject("ADODB.Recordset") rs.activeconnection = cn rs.Source = SQLString rs.Open With ThisWorkbook.Worksheets("SystemUse") If Len(.Range("FA2")) > 0 Then .Range("FA" & .Range("FA" & Rows.Count).End(xlUp).Offset(1, 0).Row).CopyFromRecordset rs Else .Range("FA2").CopyFromRecordset rs End If End With rs.Close cn.Close SQLString = "" IsFirstFile = True End If Loop
Hi Sikho! I would start with part 1 of this playlist which explains various ways to reference sheets and cells ua-cam.com/play/PLNIs-AWhQzckUd5i0E1xeSFeBAonYIurK.html I hope it helps!
Dear Sir, take sincere gratitude. How are you Sir? Sir, related to this video, I've 2 questions. - 001) Is it possible to write something or paste some data (that may be copied from some other sources like text file or .xlsx file) in a Closed Workbook without even opening it using ADODB Library in VBA? 002) What if I want get data from some specific columns instead of all the columns. But, the data should be below the same headers. Or write something below the specific column headers.
Hi Kartick, nice to hear from you! Yes, you can write information into a closed Excel workbook using ADODB and we will have a video on this technique in the current series on Writing SQL for Excel (it will be a while before we reach that stage though!). You can select data from specific columns as described in this video ua-cam.com/video/FNjTCwV7VhM/v-deo.html I hope you find it helpful!
@@WiseOwlTutorials Thank you for your suggestion Sir. But, this is not the video, I'm looking for. I might have failed to make it understand to you. Let's assume, you have a Data with 5 Columns ([Product IDs], [Product Names], [Rates], [Units] and [Regions]) in your Source Sheet. You have a Output Sheet too which contains same 5 field names. Now in your Output Sheet, Out of these 5 columns, you want only 3 Columns ([Product Names], [Rates] and [Units]). But, the condition is, all the Product Names, Rates and their sold Units should come exactly under their field names. I do admit, that this is completely unnecessary here. But, it will come handy, when dealing with real world projects. Suppose, you have 10 client files and 1 Master Compiled File. Each Client File has some common headers, and some uncommon headers. The Order of the Headers are not same in each Client file. Now, my job is to find the position of the required headers from each Client file and then copy the value of the entire Column of that position and finally paste it just the below of relative header in the Master Compiled File. So far, I've been doing it with MATCH Function along with "File System Object" Library. But, now as I've come to know from your tutorial, that data can be copied from a closed Excel file too with ActiveX Object Library, so, I just want to replace File System Object Library with ActiveX Library.
@@kartickchakraborty9135 I understand. The simplest way to do it is probably using empty strings to create spacer columns. This query would return the Title and Oscar Wins columns with two blank columns in between SQLQuery = "SELECT [Title], '' AS [Empty Column1], '' AS [Empty Column2], [Oscar Wins] FROM [Film$]" Obviously, you'd need to know the order of the columns in your destination. Alternatively you could use an INSERT statement to assign values to the correct columns. We'll have a video on INSERT statements later in the series.
@@kartickchakraborty9135 Alternatively, you could loop through the column headers in your destination worksheet to establish the column order and build a comma separated string of columns that you want to select. You can then concatenate the comma separated string into your SELECT statement so that you will pick the correct columns in the correct order and then just use the CopyFromRecordset method as usual. I like that approach because it's more flexible than the other two suggestions in my earlier response.
It is! Declare an empty Variant first: Dim v As Variant Use the GerRows method of the Recordset to return an array: v = rs.GetRows The array is organised so that the first dimension contains the columns and the second dimension contains the rows. I hope that helps!
Hi Arvinth, we don't use SharePoint so I can't provide a detailed answer here. Perhaps some of these posts might help? stackoverflow.com/questions/28434783/opening-excel-file-stored-on-sharepoint-as-data-source-using-adodb-connection www.connectionstrings.com/questions/1078/connecting-to-an-excel-file-saved-in-sharepoint/ answers.microsoft.com/en-us/msoffice/forum/msoffice_excel-msoffice_custom-mso_2010/retrieving-data-from-an-excel-file-stored-on/d2c6af15-6c95-4aab-9741-baa0e907006a
Hi Andrew, thanks for the wonderful tutorial. I managed to extract data from one closed workbook :) But now I'm struggling with another issue: Using VBA to VLookup a range of values from closed workbook (always at Column E but with dynamic lastrow + location of workbook is not fixed). What's on top of my head is that, I need to write a VBA code to prompt user to select the file path and then input the formula manually, can't figure out how to do that. What do you suggest on how I can automate this process with VBA?
Hi there! You can press CTRL + SPACE or CTRL + J to do this. I hope that helps! Huskies are great btw. And Malamutes. And German Shepherds. And basically all dogs.
It was the kind of video I was looking for but unfortunately it didn't work for me at line where SQL statement was written. It is giving error for [Sheet1$]. I have office 365. I used the same connection string as you selected in the video.
Hi Dheeraj! Does the file you're connecting to actually contain a worksheet called Sheet1? The code shown in the video works if it does and you can download the working version using the link in the description. I hope that helps!
Hi Umesh, you can use the INSERT statement to do this. We'll have a video on this topic in our SQL for Excel series in the coming weeks ua-cam.com/play/PLNIs-AWhQzckUd5i0E1xeSFeBAonYIurK.html
Currently I'm using office 2013 and I can use Microsoft.ACE.OLEDB.15.0 instead 12 with no error. I'm not sure if there's any performance increment or if this work on the latest version of The Office maybe changing to 16 for example.
Hi Rafael, yes the Access Database Engine changed to version 16.0 in Office 2016. Office 2019 and Office 365 still use version 16.0. You can install this version of the database engine independently of Office if you like www.microsoft.com/en-us/download/details.aspx?id=54920 I'm not aware of any significant differences between the 12.0 and 16.0 versions although there are odd reports of things behaving differently: stackoverflow.com/questions/59986363/microsoft-access-database-engine-2016-issues-with-certain-sql-queries-that-wor social.msdn.microsoft.com/Forums/en-US/6e0f7cd7-693c-4613-a32a-c30f69dd0475/difference-between-ms-access-database-engine-2010-and-ms-access-database-engine-2016?forum=accessdev I haven't encountered these issues myself so I can't verify that. There is an Access 365 Runtime which supports Access 2019 and 365 but you only really need this if you want to run an Access database on a machine which doesn't have Access installed support.microsoft.com/en-us/office/download-and-install-microsoft-365-access-runtime-185c5a32-8ba9-491e-ac76-91cbe3ea09c9 I hope that helps!
Run Time Error -2147467259(8004005): [ Microsoft ][ODBC Driver Manager] Data source name not found and no default driver specified. This is the error I am getting, Sir. Kindly tell me the solution on how resolve this issue.
project system high label educational tutorial , So I have searched Excel to Google sheet import tutorial . multiple close Excel file import to Google sheets in apend data process . only summary row from all of file from defin folder ,maitain the serial.import last row , may be you wil publish a tutorial , if you have made same as catagories video ,so link those video . I am waiting for yor tutorial . thank a lot.
Hi Summer! You can use the connection string shown here www.connectionstrings.com/microsoft-text-odbc-driver/ to connect to the folder containing your csv file. You can then use the filename in your select statement like so: rs.Source = "SELECT * FROM [MyFile.csv]" I hope it helps!
Dear Andrew, thanks so much for sharing! However, when i run the code, I got error message " Run-Time Error '446' : Object doesn't support named arguments", with these codes been highlighted in yellow" cn.ConnctionString = _ "Provider=Microsoft.ACE.OLEDB.12.0;" & _ "Data Source=" & MyFilesPath & TrackerFileName & ";" & _ "Extended Properties='Excel 12.0 xml;HDR=YES';" Appreciate if you could help me look into it. Thanks much !
@@WiseOwlTutorials omg, thank you!! And if you don’t mind I ask another question. After I get the data from the files, I will do some formats like fonts color. But after I add new rows to ‘data files’ and run get data again, it will refresh the destination file and formats are gone. Is there a way to only get new added data?
@@pianoandviolinbrotherandsi5785 Hi! You'd need some way to identify which rows in the data source are new. A common way to do this is store a time stamp with each record that is added to your database. You can then use a WHERE clause in the query which populates your recordset to retrieve only those rows which were added after the previous import. Or more simply, you could always import all the data and run a macro to format everything afterwards!
It's so amazing that you can create powerful tutorials in such a short period of time. Better than the avengers. Lol!
😀 thanks! I enjoyed "assembling" this one!
Thank you Andrew for an brilliant article, I also see I'm not the only one that was inspired by your video's, and thanks for the suttle query information 😃 as requested
You're very welcome Frik, glad you enjoyed it! 😀
Thank you very much Andrews. I can't tell how you've been making me an Excel expert (at least in my environment). This tutorial came right in when I needed it, and I'm more than grateful. Yet I'm still a bit hungry for more: how do I make it work when the data are in protected worksheets? Your answer will save my life. Have a great day!
Hi Lossan! Happy to hear that you're finding the videos useful. If your worksheets are protected, that's OK, you can read data from a protected worksheet using the same code shown in this video. If it's the workbook which is protected, i.e. you need to enter a password to open the Excel file, unfortunately this won't work. The generally accepted workaround appears to be opening the workbook using either the Workbooks.Open method docs.microsoft.com/en-gb/office/vba/api/Excel.Workbooks.Open or the GetObject method (although this will prompt you for the password) www.connectionstrings.com/how-to-open-password-protected-excel-workbook/ and then use ADO to work with the opened file.
Not ideal I appreciate, but I don't know of another method!
@@WiseOwlTutorials Wow! I can't believe you answered so fast. Thank you very much indeed! It's the sheet that's protected, not the workbook. So maybe the error I come across is about the [sheet1$] issue. I saw someone already asked a question about it. I'll try to exploite the answer you gave him.
@@Robertyaro It's my pleasure! I hope you find the answer but please let me know if not and I'll see if I can help!
@@WiseOwlTutorials Hi, Andrew. Unfortunately it did not work. Actually, the files I want to retrieve data from are macro enabled workbooks containing 5 sheets each. I copyed the ConnectionString for macro enabled workbooks and adapted it as you esplain. The data are on sheet 3 (Feuil3 _ I'm using a french version of Office 13). I wrote the routine with [Feuil3$] but when I run through it with F8, it halts at rs.Open ... . [Sheet3$] won't work either. I figured using the name of the sheet ([Compilation$], ["Compilation"$]) but it still won't accept that.
@@Robertyaro Hi Lossan! That's frustrating. I wonder if you can try extracting the list of worksheet names from the tables schema of the file. This part of another video that I made explains how to do that ua-cam.com/video/5F8q7Z9kWGw/v-deo.html I hope it helps!
Hi Andrew,
I really do enjoy all Your videos, and I have learn a lot from them. This is my very first comment on any of those, so now I take the chance to say a really big-big and respectful THANK YOU for all the efforts You've put in all these videos! I am a huge fan of these.
I have just put my question on an other video, but I've found this one at the same time, which is exactly the answer to my Q. So I deleted it.
But I think You still deserve the compliment! :)
Greetings from Hungary!
Thank you Zsolt for taking the time to write such a nice comment! I'm happy that you found the answer to your question here, enjoy!
I enjoy watching your videos.
Thanks Youssef, happy to hear that you're enjoying the tutorials!
Awesome video like always. Thank you!
My pleasure, thank you for watching!
Appreciate you removing the twilight saga ones! Many thanks for the video (all the videos) WiseOwl. Have a fantastic day
You're very welcome! I'm just sad that I can't do the same in real life. Have a great day yourself!
@@WiseOwlTutorials Ah yes, these banana times. You're making great smothies with them in my opinion! In the words of Neil Armstrong: "A loss for the UK but a win for the WorldWideWeb" or something like that. Thanks
First I have to tell you that you changed my career, thanks to your video I got the VBA skills that allowed to reorient my career to VBA developper in finance (I have a master degree financial engineering).
I have 2 questions for you: - Is it possible to store those data directly in an array WITHOUT copying them first on the worksheet ?
- Will you (Please CAN YOU) start back your videos on C# and go into more advanced programming ? (there are so many tutorial videos out there, but truth is you're really the best for teaching it, and I mean it, many ppl have already told you so)
Thank you so much for the kind comments! I'm really happy to hear that the videos have helped you so much. To your questions:
Yes you can write recordsets to arrays like so:
Declare an empty Variant first:
Dim v As Variant
Use the GerRows method of the Recordset to return an array:
v = rs.GetRows
The array is organised so that the first dimension contains the columns and the second dimension contains the rows. I hope that helps!
The C# tutorial was never very popular in terms of the number of views which is why we stopped adding videos. I enjoy writing C# and I'd like to make more videos in the series but time is the enemy!
Thank you again for the comments, I really appreciate you taking the time to write it!
@@WiseOwlTutorials Thank you very much for your answer. I do insist to make you realize that your videos changed my career, so I'm extremely thankful and mean it when I say that your videos are the best to learn VBA, SQL, Access... and so forth. Keep up the good work, I'll keep promoting your videos
@@WiseOwlTutorials I found doing this way you need to transpose the array to get it into a 2D array and to look and work like an array populate from a range. I was reading from a csv file which I believe does behave different maybe that's why.
@@chrisrobbo1974 Hi Chris, thanks for sharing your experience, it's really helpful when people do this! And yes, the same thing happens with GetRows if you're using Excel as your source as well (it's why I made sure to say which dimension contains the columns and which contains the rows because this has tripped me up in the past too 😀)
I've got a couple of short videos coming up which show how to use GetRows to populate an array and how to populate a listbox on a user form where Transpose was necessary too!
Great content as always, keep this series going on.... I wish it will be good if you share a video daily
Thanks Anil! I would love to be able to release a video every day but unfortunately I have to do my job as well!
I'm happy that you like the videos and thank you for taking the time to leave a comment!
Really brilliant and amazing. Thank you very much.
Thank you Yasser for you support!
Hello Andrew
Thanks soo much for the amazing videos .Your teaching style makes understanding a lot easier. I'd like to know if there is a video that shows how to Transfer Data from a Master File in a workbook to multiple other workbooks if a condition is met.
We are eternally grateful!
Shaun
Thanks Shaun, happy to hear you've found the videos useful!
This video covers how to do what you've described ua-cam.com/video/YkiphvICUSo/v-deo.html
It's part of a playlist which you might find useful to bookmark ua-cam.com/play/PLNIs-AWhQzckUd5i0E1xeSFeBAonYIurK.html
I hope it helps!
Thank you so much Sir
You're very welcome, thanks for watching!
Thanks Budddy, you have a great work!!!
I am an old man, so my eyes very very have hard work to watch what you wrote. Can you make the font bigger as big as possible you comfort with that?
My eyes will more enjoyed with that ... have a good day buddy
Thank you so much
Thanks for the feedback! I have increased from the default font size of 10pt to 14pt but I'll see if there's scope for a larger font. There's a balance to find when we're showing longer procedures to make sure we can see enough text on screen without needing to scroll but I'll see what we can do!
Hello, your videos are awesome! The method of explaining is just right.
I have a question though. I have a slightly different problem. I have to copy a certain cell value (say "SheetName"!C32) from about 2000 workbooks and I need to paste it all into one workbook (1st column workbook file name, 2nd column cell value". How would I do that? And the kicker is, not all workbooks in the folder have the sheet (so I have to put them in 2nd column in red and leave blank).
I was looking online and everyone recommends ExecuteExcel4Macro(argStr), but I have problem with it that when string argument of that function is more than 255 chars, it encounters error. I see you're doing it with ADODB, so I guess that could be used in my example as well?
I managed to solve everything and is working for files which argStr is less than 250, but I still have about 500 workbooks for which this doesn't work due to error mentioned above. Can you please help me out?
Great tutoral Andrew, as they all are. Quick question is there a way to get around the error "odbc data source name too long"?
Hi Martin! Sorry, not sure, StackOverflow has a few possible causes - I'd start there if I were you (if you haven't already solved it!)
can you create a keyword search with multiple closed excel files, extracting the results of the keyword search into a new sheet. For example, I have multiple sheets (huge data within each file) with same headers. Similar to what you've done here with the multiple closed excel files, is it possible to create a keyword search of each closed file, and list the results in a new sheet? thank you, love your tutorials!
A few days ago I tried to create an "INSERT INTO ... FROM SELECT" in an access table that takes data from an excel workbook and I couldn't find the syntax. Would you be so kind as to make a video about that if you like?
Hi Luciano! I think that you'll find this video helpful ua-cam.com/video/mKZeKf7bE8w/v-deo.html The section starting at 9:31 shows how to use INSERT INTO SELECT correctly. I hope it helps!
An excellent tutorial! Thank you!
Question: Is it possible to use this code without referencing the ADO library?
You're very welcome! And yes, you can use late-binding to avoid explicitly referencing the ADO library - see this section of an earlier video for a brief explanation ua-cam.com/video/HE9CIbetNnI/v-deo.html
Beware that the ADO library still needs to exist on the target machine.
I hope that helps!
Its really very Nice video, I have tried to create the connecton but getting Run Time error stating External Table not in expected format. Please help me and assist with the error
Hello Andrew! An awesome video like always. Yes, there are questions. 1) How can we print the Source Filename with each line of the record, so that we can have an idea of which record is from which file (just like PQ)? 2) Can we use the full power of the SQL Queries via this library like, Inner join/grouped by, etc?
Hi Imran! Questions are always good!
1) Concatenate the MovieFileName variable as an extra column into the SELECT list of the SQL statement:
rs.Open _
Source:="SELECT [Sheet1$].*, '" & MovieFileName & "' AS FileName FROM [Sheet1$]", _
ActiveConnection:=cn
2) Yes! There are some differences to the SQL you may have used in other applications. For example, if you join more than two tables you must enclose each separate join in a set of ( ) parentheses, e.g.
rs.Source = _
"SELECT f.Title, f.ReleaseDate, f.RunTimeMinutes, g.Genre, d.FullName, s.Studio" & _
" FROM (([Film$] AS f" & _
" INNER JOIN [Genre$] AS g ON f.[GenreID] = g.[GenreID])" & _
" INNER JOIN [Director$] AS d ON f.[DirectorID] = d.[DirectorID])" & _
" INNER JOIN [Studio$] AS s ON f.[StudioID] = s.[StudioID]"
I hope that helps!
Thanks Andrew for the detailed answers. For question 2 , so we can use all SQL queries but with a difference of syntax that needs to pick up from any resource. Once again thanks.
@@imranbhatti8580 No problem Imran! I'm reluctant to say "all SQL queries" because different SQL dialects support different features and I don't know which versions of SQL you're familiar with. But you can certainly do all the standard things that you mentioned (joins and group by). And yes, there may be other syntax differences but the parentheses around joins is the main one which catches me out!
Hi Andrew,
This is one of the best and simplest explanations I've encountered so far. Thank you so much for this.👌👍
Can you guide on how can I modify the code to fetch a specific cell range (e.g. C13:S38) from a common worksheet (Sheet2) of all files?
Hi Harshit, you can find some information on how to do that in the first video of this playlist ua-cam.com/play/PLNIs-AWhQzckUd5i0E1xeSFeBAonYIurK.html
I hope it helps!
@@WiseOwlTutorials Thank you Andrew.
I just saw that video after reading few comments posted earlier.
I came up with this:
rs.Open Source:="SELECT * FROM [OPT_ECO$C13:S38]", ActiveConnection:=cn
can you suggest how can I define sheet name (OPT_ECO) and range (C13:S38) as variables SourceSheet and SourceRange respectively so that I can put an input in the worksheet for the user to feed in these details and based on that input macro processes. How can I refer these input variables in the above statement?
show the video and it is very good i got what i wanted exactly but i have few modification can you help me out.
how can i add cell reference for MovieFileName and in rs Source in condition where a cell value is defined so as we change a cell value condition changes accordingly.
Hi. Great videos as always although I am struggling atm..
can this be adapted for multiple individual cells (not 'currentregions' and sheets of a closed workbook to defined cells/sheets of my open one?
Many Thanks
Hi Phil! We have a separate playlist covering how to write SQL for Excel files. The first video shows how to reference sheets, cells and named ranges which you might find useful
ua-cam.com/play/PLNIs-AWhQzckUd5i0E1xeSFeBAonYIurK.html
I hope it helps!
Hello! I have a workbook on a network drive. When the code runs the cn.open, it opens the workbook and recordset won't open. Can it be because someone had opened it before I ran my code? I made a copy of the workbook and the same code runs seemlesly.
Hi there! That's a good question and it seems a strong possibility. There are some suggestions in this post which may be useful www.vbforums.com/showthread.php?395528-Problem-with-multiple-users-accessing-an-excel-sheet-through-ado
I hope it helps!
Amazing 👏
Thanks Serge!
Hello Andrew. When I followed along with this tutorial, the number of files was nearly 20 and it was working fine. However. today I run it on a folder with more than 70 files and I am getting the error "Query is too complex". I know the SQL Query string length could be up to 65000 characters whereas mine has 44000+ characters. Is the length limit for VBA ADO Query string have its own limitations?. and we should be running the query in chunks?
Hi Imran! This error is more likely to be linked to the number of tables/fields/joins in the query than the length of the query string. Have a look at the "Query" section on this page support.microsoft.com/en-us/office/access-specifications-0cf3c66f-9cf2-4e32-9568-98c1025bb47c
thanks Andrew. However I fixed it using chunks. like this
If Len(SQLString) > 10000 And Not TaskFile = "" Then
cn.Open
Set rs = CreateObject("ADODB.Recordset")
rs.activeconnection = cn
rs.Source = SQLString
rs.Open
With ThisWorkbook.Worksheets("SystemUse")
If Len(.Range("FA2")) > 0 Then
.Range("FA" & .Range("FA" & Rows.Count).End(xlUp).Offset(1, 0).Row).CopyFromRecordset rs
Else
.Range("FA2").CopyFromRecordset rs
End If
End With
rs.Close
cn.Close
SQLString = ""
IsFirstFile = True
End If
Loop
Hi! If I trying it with Named sheet then getting error as object not found or something. Can you please help me ?
Hi Sikho! I would start with part 1 of this playlist which explains various ways to reference sheets and cells ua-cam.com/play/PLNIs-AWhQzckUd5i0E1xeSFeBAonYIurK.html
I hope it helps!
@@WiseOwlTutorials ok thanks for update.I will check and update.
Dear Sir, take sincere gratitude. How are you Sir? Sir, related to this video, I've 2 questions. -
001) Is it possible to write something or paste some data (that may be copied from some other sources like text file or .xlsx file) in a Closed Workbook without even opening it using ADODB Library in VBA?
002) What if I want get data from some specific columns instead of all the columns. But, the data should be below the same headers. Or write something below the specific column headers.
Hi Kartick, nice to hear from you! Yes, you can write information into a closed Excel workbook using ADODB and we will have a video on this technique in the current series on Writing SQL for Excel (it will be a while before we reach that stage though!).
You can select data from specific columns as described in this video ua-cam.com/video/FNjTCwV7VhM/v-deo.html
I hope you find it helpful!
@@WiseOwlTutorials Thank you Sir, thank you very much.
@@WiseOwlTutorials Thank you for your suggestion Sir. But, this is not the video, I'm looking for. I might have failed to make it understand to you.
Let's assume, you have a Data with 5 Columns ([Product IDs], [Product Names], [Rates], [Units] and [Regions]) in your Source Sheet. You have a Output Sheet too which contains same 5 field names. Now in your Output Sheet, Out of these 5 columns, you want only 3 Columns ([Product Names], [Rates] and [Units]). But, the condition is, all the Product Names, Rates and their sold Units should come exactly under their field names. I do admit, that this is completely unnecessary here. But, it will come handy, when dealing with real world projects. Suppose, you have 10 client files and 1 Master Compiled File. Each Client File has some common headers, and some uncommon headers. The Order of the Headers are not same in each Client file. Now, my job is to find the position of the required headers from each Client file and then copy the value of the entire Column of that position and finally paste it just the below of relative header in the Master Compiled File. So far, I've been doing it with MATCH Function along with "File System Object" Library. But, now as I've come to know from your tutorial, that data can be copied from a closed Excel file too with ActiveX Object Library, so, I just want to replace File System Object Library with ActiveX Library.
@@kartickchakraborty9135 I understand. The simplest way to do it is probably using empty strings to create spacer columns. This query would return the Title and Oscar Wins columns with two blank columns in between
SQLQuery = "SELECT [Title], '' AS [Empty Column1], '' AS [Empty Column2], [Oscar Wins] FROM [Film$]"
Obviously, you'd need to know the order of the columns in your destination.
Alternatively you could use an INSERT statement to assign values to the correct columns. We'll have a video on INSERT statements later in the series.
@@kartickchakraborty9135 Alternatively, you could loop through the column headers in your destination worksheet to establish the column order and build a comma separated string of columns that you want to select. You can then concatenate the comma separated string into your SELECT statement so that you will pick the correct columns in the correct order and then just use the CopyFromRecordset method as usual.
I like that approach because it's more flexible than the other two suggestions in my earlier response.
is it possible to put a recordset directly in an array instead of writing/copying it to a range ?
It is! Declare an empty Variant first:
Dim v As Variant
Use the GerRows method of the Recordset to return an array:
v = rs.GetRows
The array is organised so that the first dimension contains the columns and the second dimension contains the rows. I hope that helps!
Thank you. This is very helpful. Can you show me how i could get data from an excel file that is stored in SharePoint (via link)?
Hi Arvinth, we don't use SharePoint so I can't provide a detailed answer here. Perhaps some of these posts might help?
stackoverflow.com/questions/28434783/opening-excel-file-stored-on-sharepoint-as-data-source-using-adodb-connection
www.connectionstrings.com/questions/1078/connecting-to-an-excel-file-saved-in-sharepoint/
answers.microsoft.com/en-us/msoffice/forum/msoffice_excel-msoffice_custom-mso_2010/retrieving-data-from-an-excel-file-stored-on/d2c6af15-6c95-4aab-9741-baa0e907006a
Hi Andrew, thanks for the wonderful tutorial. I managed to extract data from one closed workbook :)
But now I'm struggling with another issue: Using VBA to VLookup a range of values from closed workbook (always at Column E but with dynamic lastrow + location of workbook is not fixed).
What's on top of my head is that, I need to write a VBA code to prompt user to select the file path and then input the formula manually, can't figure out how to do that. What do you suggest on how I can automate this process with VBA?
Connection is not working , please suggest me
6:06 how do you open the list of properties/methods without using right mouse clic on a name ?
Hi there! You can press CTRL + SPACE or CTRL + J to do this. I hope that helps!
Huskies are great btw. And Malamutes. And German Shepherds. And basically all dogs.
It was the kind of video I was looking for but unfortunately it didn't work for me at line where SQL statement was written. It is giving error for [Sheet1$]. I have office 365. I used the same connection string as you selected in the video.
Hi Dheeraj! Does the file you're connecting to actually contain a worksheet called Sheet1? The code shown in the video works if it does and you can download the working version using the link in the description.
I hope that helps!
@@WiseOwlTutorials Thank you for quick response. It is working now.
@@dheerajjain5754 No problem, glad you got it working!
Hello, may I know how do I insert data into closed workbook?
Hi Umesh, you can use the INSERT statement to do this. We'll have a video on this topic in our SQL for Excel series in the coming weeks ua-cam.com/play/PLNIs-AWhQzckUd5i0E1xeSFeBAonYIurK.html
Currently I'm using office 2013 and I can use Microsoft.ACE.OLEDB.15.0 instead 12 with no error. I'm not sure if there's any performance increment or if this work on the latest version of The Office maybe changing to 16 for example.
Hi Rafael, yes the Access Database Engine changed to version 16.0 in Office 2016. Office 2019 and Office 365 still use version 16.0. You can install this version of the database engine independently of Office if you like www.microsoft.com/en-us/download/details.aspx?id=54920
I'm not aware of any significant differences between the 12.0 and 16.0 versions although there are odd reports of things behaving differently:
stackoverflow.com/questions/59986363/microsoft-access-database-engine-2016-issues-with-certain-sql-queries-that-wor
social.msdn.microsoft.com/Forums/en-US/6e0f7cd7-693c-4613-a32a-c30f69dd0475/difference-between-ms-access-database-engine-2010-and-ms-access-database-engine-2016?forum=accessdev
I haven't encountered these issues myself so I can't verify that.
There is an Access 365 Runtime which supports Access 2019 and 365 but you only really need this if you want to run an Access database on a machine which doesn't have Access installed support.microsoft.com/en-us/office/download-and-install-microsoft-365-access-runtime-185c5a32-8ba9-491e-ac76-91cbe3ea09c9
I hope that helps!
While making the connection string. I'm getting Run Time error message. Kindly help me on this issue.
Hi, if you post your code, show which line causes the run time error and give details of the error message I might be able to help.
@@WiseOwlTutorials Ok. I will post my code as well as the run time error message code. Thanks a lot, Sir, for responding so quickly!!
Run Time Error -2147467259(8004005): [ Microsoft ][ODBC Driver Manager] Data source name not found and no default driver specified. This is the error I am getting, Sir. Kindly tell me the solution on how resolve this issue.
How do i choose I.e I14 in each file?
Hi! You'll find the first video in this playlist useful ua-cam.com/play/PLNIs-AWhQzckUd5i0E1xeSFeBAonYIurK.html
I hope it helps!
project system high label educational tutorial , So I have searched Excel to Google sheet import tutorial . multiple close Excel file import to Google sheets in apend data process . only summary row from all of file from defin folder ,maitain the serial.import last row , may be you wil publish a tutorial , if you have made same as catagories video ,so link those video . I am waiting for yor tutorial . thank a lot.
Hi! Sorry I don't have any experience with Google sheets so it's unlikely that we'll be posting videos on that topic any time soon!
How do you get data from a csv file?
Hi Summer! You can use the connection string shown here www.connectionstrings.com/microsoft-text-odbc-driver/ to connect to the folder containing your csv file. You can then use the filename in your select statement like so:
rs.Source = "SELECT * FROM [MyFile.csv]"
I hope it helps!
@@WiseOwlTutorials Thank You! It worked!
@@summer3916 That's great! Thanks for letting us know!
Dear Andrew, thanks so much for sharing! However, when i run the code, I got error message " Run-Time Error '446' : Object doesn't support named arguments", with these codes been highlighted in yellow"
cn.ConnctionString = _
"Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=" & MyFilesPath & TrackerFileName & ";" & _
"Extended Properties='Excel 12.0 xml;HDR=YES';"
Appreciate if you could help me look into it. Thanks much !
Hi! You have a typo in ConnectionString (you're missing an "e").
Hope it helps!
@@WiseOwlTutorials omg, thank you!! And if you don’t mind I ask another question. After I get the data from the files, I will do some formats like fonts color. But after I add new rows to ‘data files’ and run get data again, it will refresh the destination file and formats are gone. Is there a way to only get new added data?
@@pianoandviolinbrotherandsi5785 Hi! You'd need some way to identify which rows in the data source are new. A common way to do this is store a time stamp with each record that is added to your database. You can then use a WHERE clause in the query which populates your recordset to retrieve only those rows which were added after the previous import.
Or more simply, you could always import all the data and run a macro to format everything afterwards!
@@WiseOwlTutorials thanks you so much!
@@pianoandviolinbrotherandsi5785 You're very welcome!