I have been resistant about doing data extraction stuff outside the usual Excel method (open the source file, copy things over, close source file) because the tutorials are usually abstruse and frustrating. But this video is crystal clear and you go through the steps at the appropriate level of detail and with the right pacing for me to follow. Excellent stuff! Thank you, thank you.
sorry to be so offtopic but does anybody know of a way to get back into an instagram account?? I was stupid lost my login password. I appreciate any tricks you can offer me.
Wow! Just wow! Excellent material and demonstration. I'm going on 60 and been writing VBA and Studio for about 5-6 years now. To see other examples and styles of coding is so enlightening. Thank you!
Amazing to learn new technology that's a life saver but has been available all along, right? Wish we knew this 10 years ago, but of course we weren't as wise back then.
Hands down, the best VBA training resource on the web! I have a question: can you modify this procedure to copy data into another new workbook from a closed xlsx file given the following conditions. 1) I need to filter data for 1 or more criteria under the same header. 2) My source file table headers are on row 5. 3) I need source file rows 1-4 copied to the new workbook as they contain information about the file data that needs to go into the filtered reports. 4) I need to keep the same formatting (font size, colour, bold, cell colour etc.). If you would say that this is possible, I could attempt a bit of tinkering to make it so. If not, I would be glad to be told to not waste time and to go back to the old open file and copypaste method.
Hi! Happy to hear that you've found the videos useful! To answer your questions: 1) and 2) are no problem 3) is a little awkward and depends on the layout of the header rows 4) isn't possible with this technique - if you need formatting information you'll need to have the file open, sorry!
@@WiseOwlTutorials Thank you for saving me from going down the fruitless rabbit hole that is #4 with this technique! I work with another different set of reports that use 1, 2 and 3. While #3 is nice to have, it is not necessary. But I'm most intrigued about #2. My source file has the Report Name and ID populated in Cells A1 and B1. The headers for the actual data start from A2 and goes across about 20 columns. Would your code above be sufficient for this, and if not, what have I got to tweak? I just need to pull the data filtered for 1 or more criteria. Row 1 with Report Name and ID can be disregarded.
@@busybillyb33 Hi again! You can reference the range of cells you want to query in a few different ways - it might be worth taking a look at the first video in this playlist which shows a few different techniques ua-cam.com/play/PLNIs-AWhQzckUd5i0E1xeSFeBAonYIurK.html&feature=shared The playlist also has multiple videos on adding criteria to your queries. Hope you find what you're looking for in there!
your training video is great and helps me a lot to Pull data form Closed Workbook, need little help to pull headers too, as you mentioned in your video some headers name not showed , same problem I face and need a solution to pull every header
Thanks! Brilliant as always. I wonder if there is an elegant way to find the last row in as specific column on af specific sheet? To make it more complicated, there might be empty cells in top of the column.
Hello Andrew, You have a way of explaining complex things in a calm, simple way-Thank you! Following on from this video, is there a way to a) determine whether there is an image in the data from your closed workbook. b) How do I retrieve the image to include it in another workbook?
Thanks Martin! No, you can't check for images in a closed workbook, at least as far as I know. You'd need to open the file and then apply copy and paste methods to the Shape object representing the image.
@@WiseOwlTutorials Thank you Andy that's exactly what I did, but was hoping to speed up the query by doing it with a closed file. I think I will have to convert my 12 workbooks with multiple sheets into an access database, while trying to keep it normalised... Any suggestions?
@@martingregson7136 Hi Martin! It's tricky subject to offer simple help on but if you haven't done this before I'd start with these two links learn.microsoft.com/en-us/office/troubleshoot/access/database-normalization-description support.microsoft.com/en-us/office/move-data-from-excel-to-access-90c35a40-bcc3-46d9-aa7f-4106f78850b4 I hope it helps!
Wooow!! Very helpful!! Thanks. I wondering if you could explain how to get the same data (with ADODB connection) but based on an array within the workbook open.. I mean get only certain rows from the closed workbook that match with a list (array) in the openned book... Thanks!!
You're very welcome Xavi! And that's a nice suggestion, we could do that by constructing a comma separated list of values to pass into the IN operator of the SQL query. I'll add this to my list and look at creating a video to show how this might work, thanks for the suggestion!
(However it returns a run-time error in my existing Macro Enabled Workbook, but not when I experiment with a new Brand New Macro Enabled workbook, so the code works, its very odd. Error - -21474672259 (80004005) Cannot update - Database or object is read-only. Spent hours trying to resolve.
@@WiseOwlTutorials I subscribed, you have loads of videos! I develop vba applications at work but I am self taught and learn so much from people like you on youtube.
I've just watched this wonderful video and didnt try yet ('ll do this tomorrow on the work), it looks great (THANKS!). But my next question is how to write portion of data into defined place of closed workbook still without it's opening?
Thanks John! You might find this playlist useful in general ua-cam.com/play/PLNIs-AWhQzckUd5i0E1xeSFeBAonYIurK.html Parts 58.28 to 58.30 cover how to insert new data or update existing data using ADO. I hope it helps!
@@WiseOwlTutorials Parts 58.28 to 58.30 are to far for me now, so i think i should start from the beginning. And i hope "i'll try so hard and 'll get so far". :) Great thanks, Sir for sharing your priceless material for my many weekends forward. :)
Hi Andrew, wonderful tutorial, thanks a lot. I followed the instructions and after some trouble got the results. Thanks again. I have a question rhough. Why we use thisworkbook in cn string? Whereas we are reading from closed workbook. I my practice, i removed it and still got the result.
Hi! We use ThisWorkbook.Path to return the path to the folder that the current workbook is stored in. It's shorter than writing the complete folder path! I hope that helps!
I have found this concept pretty intriguing. I've been looking for naswers in the Excel community, but that having failed going to try to reach out here. Can I use this method with a match function of the closed workbook? I want to find the row number in the closed book worksheet matching a value and then extract data from cells in that row?
Hi! In short, no, you can't. But the good news is that you don't need to - you can use the SELECT statement to retrieve any values you need. I made an entire series on using SQL to query Excel files which you can find here ua-cam.com/play/PLNIs-AWhQzckUd5i0E1xeSFeBAonYIurK.html I hope it's useful!
Thank you for your very informative video! Can I ask if it is possible to open two connections (to two external workbooks), and do something like a LEFT JOIN?
Thanks Joel! You could create a join if the worksheets were in the same workbook but I'm not sure that it's possible if the worksheets belong to separate workbooks but I'll put it on my list to investigate!
@@joelwong3189 OK, got it! I'll put it into a video format at some point but here's the basic code: cn.ConnectionString = _ "Provider=Microsoft.ACE.OLEDB.12.0;" & _ "Data Source=" & ThisWorkbook.Path & "\My Files\Film.xlsx;" & _ "Extended Properties='Excel 12.0 Xml;HDR=YES';"
cn.Open
rs.ActiveConnection = cn
rs.Source = _ "SELECT f.Title, f.ReleaseDate, f.RunTimeMinutes, g.Genre, s.Studio" & _ " FROM (([Film$] AS f" & _ " INNER JOIN (SELECT * FROM [Excel 12.0 Xml;Database=" & ThisWorkbook.Path & "\My Files\Genre.xlsx;].[Genre$]) AS g ON f.[GenreID] = g.[GenreID])" & _ " INNER JOIN (SELECT * FROM [Excel 12.0 Xml;Database=" & ThisWorkbook.Path & "\My Files\Studio.xlsx;].[Studio$]) AS s ON f.[StudioID] = s.[StudioID])" rs.Open There are three files: Film.xlsx with a worksheet called Film Genre.xlsx with a worksheet called Genre Studio.xlsx with a worksheet called Studio Each table has a primary key FilmID, GenreID, StudioID The Film worksheet contains foreign keys GenreID, StudioID There's at least two ways of referencing the other workbooks in the FROM clause but I felt that this was the cleanest. Hope it helps!
Hello Wise owl thanks for the tutorial but I have a question. I wonder if you can change the position of the header (HDR) in the in your request because mine is located in the 4th row. Thanks in advance for your answer.
Hi Antoine! You might find the first video of this playlist useful ua-cam.com/play/PLNIs-AWhQzckUd5i0E1xeSFeBAonYIurK.html It shows various techniques for referencing cells in a worksheet. I hope it helps!
Amazing video. Do you have a video showing how to get data from specific sheet in a closed workbook and insert that data into a closed access database using SQL?
Hi Daniel! We don't have a single video which shows that specific technique but you'll find this playlist helpful ua-cam.com/play/PLNIs-AWhQzckUd5i0E1xeSFeBAonYIurK.html&feature=shared And this video for the Access-related INSERT statement ua-cam.com/video/-c2QvyPpkAM/v-deo.htmlfeature=shared I hope it helps!
@@WiseOwlTutorials Thanks for the links. I am going to check them out right now. I did have 1 more question I was hoping you could help me with. Would it be considered "best practice" to open a connection to the Excel file and Access Db at the same time and then SELECT and INSERT the data using 1 SQL statement and then close both connections or open a connection to Excel, SELECT the data to memory and close the connection to Excel and then open a connection to Access, INSERT the data from memory and close the connection to Access or does it really not matter?
@@danielsteele6938 Ultimately I don't think it matters. I'm not sure if this approach works for Excel and Access but the technique shown in the last part of this video is what I'd try to start with ua-cam.com/video/mKZeKf7bE8w/v-deo.htmlfeature=shared
Hi Andrew, many thanks for your videos. I'm trying to use this technic to combine data from differents closed excel files and put results in an excel table. Is there a video where you already covered this topic? Many thanks again.
Hi Alessandro! Yes, we have several videos which explain how to do this in various ways. If you go to the Wise Owl Tutorials channel page here on UA-cam you can use the search tool to search for "ADODB" I hope it helps!
Hi :) Great Video!! I am needing this information for a work project. I do have a question. Do you have another video explaining how to use a FORM with ADO? My co-workers are not excel savvy and I wanted to make this process as easy as possible. I wanted to have a form and when you enter a specific identifier (ie. "Work Order") and click a command button, any information on that identifier would populate from the closed spreadsheet into our working spreadsheet. Even more complicated is that I need only specific columns of information to be populated into specific cells on our worksheet. Any help in that? Any video? I do appreciate your time.
Thank you SO much for this useful method! I have one question. Is it possible to make the Data Source property to be dynamic (e.g. to prompt the user to choose the file to make the connection with)?
Hi! As far as I know you can't pass a password to the connection string for ACE.OLEDB. There's a long discussion which includes a workaround for this issue here but I haven't tried it myself www.vbforums.com/showthread.php?842899-RESOLVED-How-to-get-data-from-a-password-protected-closed-excel-workbook I hope it helps!
Hello Wise Owl. This is awesome I like it I wish I had learned to code like this. Could you help me to understand what happen if you don't close the connection? Does the connected document will have irreparable problems ? Thanks
Hi Ezra! The connection will close automatically when the variable goes out of scope so you don't technically need to close it in the small example shown in this video. It's good practice to close the connection when you have finished with it to free any resources used by it. Here is some documentation from Microsoft docs.microsoft.com/en-us/sql/ado/reference/ado-api/close-method-ado?view=sql-server-ver15 I hope it helps!
Hi WiseOwl, As always brilliant job done here. Unfortunately I have encountered error which says that database engine could not find object (which is sheet name [Oct 21$]. Connection is working but after adding SQL statement it fails. I need to add that file is on shared drive. Code error -2147217865 (80040e37) Thanks in advance
Hello. How do I open several files from a specific folder, copy from them and paste in a master excel sheet please? Also Since the headers are not written in the same way in the different excel sheets, i will need to do a matching so as to tell the macro when it sees for example "NAME1" in row 10, it should copy the columns information and paste in the master excel file in column with header "Name"?
Hello wise owl. Thank you for all those learning videos. They are great. I just discovered you and can not wait to watch and take some notes from them. However I had question to you. I face a problem while I pull the data from closed workbooks if they are opened by someones at that time. ADO opens the excel file first if the source excel file was opened at that time by somebody else, even though it pull and copy the data correctly afterwards. How can we enable ADO not open the source file in any case? Is there a way about it?
Hi Deniz! Sorry, I don't know the answer to that but you're not the only person to experience it stackoverflow.com/questions/25400750/why-does-excel-file-opens-when-opening-connection-using-ado
Excellent vedio ❤❤, i use sumifs function to get data from closed excel files. Can you please explan, how to use this methord for sumifs function. Thanks
Hi! You can't use SUMIF with this technique as it's an Excel function and this technique relies on a version of SQL. You can learn about all the things you can do with this technique in this playlist ua-cam.com/play/PLNIs-AWhQzckUd5i0E1xeSFeBAonYIurK.html I hope it helps!
I just love your videos, you are truly a hero! I have a little question that I hope you see and can answer. I wonder (if possible) how you would do, to copy/move/push data from a sheet or sheets (in a master document if you like) to several closed woorkbooks? I would (again if possible) want the master document to be .xlsm and the other woorkbooks to be .xlsx. Hope you see this and have the time to help us all lost in the VBA-universe. take care!
Hi there! We can insert and update data in a closed Excel workbook using ADO. I plan to have a video on how to do this soon but if you'd like an idea of how it works with a Microsoft Access database here's an older video I made which you might find useful ua-cam.com/video/-c2QvyPpkAM/v-deo.html I hope it helps!
Wow, what an (other) amazing and very useful video. Thank you very much. So, if we use this technique to import datas from multiples Excel Workbooks (hundreds of files); it will be quicker than open and closing each one of those files?
Thank you Sylvain! And yes, that's the idea at least - connecting to each file without opening it should be quicker than opening and closing each file in turn. Thanks for taking the time to leave a comment!
Thank you for your response. Another question: it is possible with this technique to select only specifics cells? I mean by example: values of B15, F17 and W30. Can we do it with only one sql command?
@@sylvainlavallee8263 Hi Sylvain! Yes you can although it's not very elegant! You'll need to set the HDR property to NO "Extended Properties='Excel 12.0 Xml;HDR=NO';" You can then UNION SELECT individual cell values like so: rs.Source = _ "SELECT * FROM [Sheet1$B15:B15] " & _ "UNION SELECT * FROM [Sheet1$F17:F17] " & _ "UNION SELECT * FROM [Sheet1$N30:N30]" You must make sure that each selection contains the same number of columns but you can select any number of rows from each separate union. You'll get the results returned as a single column so you'll have to work out how to pick out which value is which. I hope that helps!
Hi, is possible to not have fix path in the Data Source? The reason is my source file is generated with different name and date every day. Is it got an option excel will ask you to browse the file after you click the button?
Thank you so much. After I have watched your tutorials about adodb for access 4 years ago i have been practicing it until now. One thing I have learned from access is that there is no need to save the file to save the added data unlike in excel workbooks that you need to save the workbook to save the changes. Is it possible to do this is excel also using this method? Thank you so much in advance. I really love your tutorials. Can't use the right words to praise your works but still you are the very best among the best.
Hi there! It's nice to know that the Access ADO videos are still useful even now! And yes, you can modify data in a closed Excel workbook using ADO. As long as the workbook is closed, you don't need to save changes. You can send INSERT and UPDATE statements like this (assuming you've created and opened the Connection): Dim cmd As ADODB.Command
Wow thank you so much. I really thought that it can be done only by using access. Thank you once again. Can't wait to try this. P.S. All of your videos have helped me a lot. Not to mention I have watched the Data Labels in Scatter Charts. If i'm not mistaken it was Part 42 back then even though it is no longer in your list i still remembered it. This is my way of saying that you really are the best teacher and I am one of the many people out there that respects you. More power.
@@MyAudioBookCompilation My pleasure! The video you mentioned is still there by the way, we just renumbered it so that it fit with the other videos on charts ua-cam.com/video/SidxAJ1MRFo/v-deo.html Good memory you have!
Thanks for another great tutorial. I noticed that the source data file (the closed workbook) name must be in English. If it's not in English, the code generates an Run-time error message. This code works perfectly when data (in the closed workbook) starts at the very first row (including the header row). But what do you do when data starts, for example, in row 10 and you don’t want to copy any content about that row? Thank you in advance for any useful advice.
Thanks Tal, glad you enjoyed it! I wasn't aware of the English name requirement, that's interesting to know, thanks for sharing! If you want to avoid copying data outside a specific range you can use the cell references in the SELECT statement as shown at 13:01. You can also use range names if you have those available, like so: 'Use cell references rs.Source = "SELECT * FROM [Sheet1$J9:L41]"
'Use a range name rs.Source = "SELECT * FROM [Films2016]" I hope that helps!
@@litmol Hi Tal! Excel table names don't appear to be supported. If the table is in an open workbook you can return the Address property to get the cell references as described here stackoverflow.com/questions/47029764/use-table-name-in-sql-query-in-vba-excel Otherwise it's cell references or range names only as far as I know!
Thanks a lot for the wonderful videos. Would like to know how would i fetch the data if my workbook is password protected. How to pass the password using the connection string. Thanks in advance.
You're very welcome Rajesh! As far as I know you can't pass a password to the connection string for ACE.OLEDB. There's a long discussion which includes a workaround for this issue here but I haven't tried it myself www.vbforums.com/showthread.php?842899-RESOLVED-How-to-get-data-from-a-password-protected-closed-excel-workbook I hope it helps!
Hi Paul! Yes, sure you can! Your SQL statement is just a string - you can concatenate it from as many parts as you like. Try this part of an earlier video (it uses Access for the example but the same principle applies to Excel) ua-cam.com/video/HE9CIbetNnI/v-deo.html If you want to do it the formal way, you can use parameters instead. This video explains how (it uses SQL Server but the same principle applies) ua-cam.com/video/I31Mh5-8Z1g/v-deo.html I hope it helps!
Hi! Do you mean you want to use the 2nd row of data in the recordset as headers? Or do you want to place all the data in row 2 of the output worksheet?
@@liefschneider3123 Hi! I would just import everything from the recordset, don't bother with the field names, and then delete the first row from the worksheet. I think that's the easiest thing to do.
@@liefschneider3123 No problem! I guess you could set up a named range in the source workbook which starts at the second row of data and then use the named range in the select statement rather than the worksheet name. But I think it's much easier to continue doing what you're doing!
Hi Yash! You can see how to write data into a closed workbook in part 58.28, 58.29 and 58.30 in this playlist ua-cam.com/play/PLNIs-AWhQzckUd5i0E1xeSFeBAonYIurK.html I hope it helps!
Thanks Yerzhan! Yes you can write data to a closed workbook using ADODB using SQL INSERT statements. I plan to have a video on this at some point but just in case you needed a quick answer you can see this post, I hope it helps! www.mrexcel.com/board/threads/insert-multiple-records-using-ado.110166/post-589361
@@yerzhankosbayev6436 Indeed it does still work! Insert statements haven't changed. Hopefully we'll have the video up at some point this week, depending on the courses I'm teaching.
@@WiseOwlTutorials As always the most useful tutorials! If you are still working on this video, could you include a Delete statement? Could one delete all the rows (except header) in the source sheet after retrieving it?
@@deonmuller595 Thanks Deon! I'm still adding videos on ADODB and modifying data videos will appear some time hopefully soon. Sadly, however, the ACE.OLEDB provider doesn't support deleting rows from an Excel workbook. You'd have to do this using regular Excel techniques which annoyingly would involve opening the file first.
I know it's been 3 years but I need your help with a scenario like this. I have an online workbook stored on OneDrive it should have served as a database but ACE OLEDB doesn't work with online files or file path. Is there another way I can dynamically connect workbooks to other workbooks serving as databases based on certain criteria? Let me know if need more info
@designexperience, I ran into the same issue. I solved by figuring out which local drive OneDrive syncs with (C:\users\xxx\...) and referencing that directory in the connection string instead of the OneDrive path (which throws errors in VBA due to not recognizing the path's URL syntax).
Hi Andy, can you tell me why do you use the ADODB library, instead of setting a reference to the workbook with the GetObject methode, is it better, quicker ?
Hi Karim, funnily enough I added a video on early binding and late binding recently! It shows one reason why late binding and the CreateObject function can be better than early binding ua-cam.com/video/qmJPbub-D0E/v-deo.html In general I use early binding because it's so much more convenient to have the IntelliSense prompt you with the relevant classes, constants, properties and methods without having to look them up. If I'm writing code for someone else and I'm not sure which version of the library they have installed it's trivial to switch to late binding after writing all the code, as the video I linked to shows. There's a suggestion here www.cpearson.com/excel/optimize.htm under the Early Binding section that it's faster to use early binding than late binding but I've never noticed a significant difference and convenience is the main reason to use it for me. I hope that helps!
Pl let us know how to extract data from closed file by using vlookup.There are two file one file name keep updating which file need data from master data file .Master data file is updating once in week .
Hello @wiseowltutorials, is it possible to get data from closed workbook, keeping the format of the source data intact? In the way that you have explained, it is just getting plain data without the formatting and formulae. Thanks
Hi Pratik! No, ADODB doesn't retrieve any formatting information. You will need to open the file if you want to copy that type of information. I hope that helps!
Nice work Andrew. A couple of questions here. 1) Can we use this on the same workbook, i.e. to query a sheet that is in the same workbook? 2) What if we don't know the Field Names of the sheet that we want to query from? 3) Can we populate the filtered results to userform listbox directly i.e without first placing them on a sheet and then loading from there?....There are more questions but for the time being. By the way Adrew you gave a long pause in your Excel VBA tutorials.
Hi Imran, great to hear from you again! Yes, there was a big gap there - it's been a strange year for us at Wise Owl! As to your questions: 1) Yes, you can! cn.ConnectionString = _ "Provider=Microsoft.ACE.OLEDB.12.0;" & _ "Data Source=" & ThisWorkbook.Path & "\" & ThisWorkbook.Name & ";" & _ "Extended Properties='Excel 12.0 Xml;HDR=YES';" 2) You can list the field names by looping through the fields collection. There are a couple of ways to do this, this one requires an Integer variable called i: rs.Open For i = 0 To rs.Fields.Count - 1 Debug.Print rs(i).Name Next i 3) Yes, you can! rs.Open Me.ListBox1.ColumnCount = rs.Fields.Count Me.ListBox1.List = Application.Transpose(rs.GetRows) rs.Close I hope it helps, good to see a familiar name!
@@WiseOwlTutorials Thank you so much for answering my questions in detail. I will use this in my projects and will let you know. Your tutorials are always helpful. The very first thing that I learnt from you was the concept of arrays(single and multi-dimensional, which I am much appreciated by my boss when I use them in my macros). Please continue sharing your knowledge with the community.
Hello Andrew. I am getting an error "either bof or eof is true or the current record has been deleted" on this line "Me.ListBox1.List = Application.Transpose(rs.GetRows)". My rs has more than 2 columns and I checked in the watch window that it has the records in it. but cannot load them to listbox. Can you please guide?
@@imranbhatti8580 Hi Imran! Did you perform any other operation on the recordset before GetRows, like CopyFromRecordset for example? If so, add this line before you attempt to use GetRows: rs.MoveFirst I hope it helps!
Dear wise owl, awesome video ...one small query is there a limitation of extracting data up to 255 columns?? as i have tried to extract data from my closed workbook and it is extracting data up to 255 columns and the rest i don't get it...
Hi Andrew, thank you very much for the great work. I have a question, How to skip the workbook when a specific worksheet is not found, close it and then go to the next workbook? will appreciate your help.
Hi! I'd take a look at this set of lessons on writing error-handling code in VBA www.wiseowl.co.uk/online-training/excel-vba/error-handling/ I hope it helps!
Greatest Tutorial Andrew. I have a SQL query (through an ADODB connection) that populates an Excel spreadsheet. The thing is that I want add a criteria "WHERE x = [cell reference]" For example : CriteriaCity=sheet1.range("A1").value in vba editor rn.source = "SELECT * FROM [sheet1$] WHERE [CITY]= [CriteriaCity]" but it throws error. could you please help. Thanks in advance
Hi! You need to concatenate the value of the cell into your query string, making sure to include the quote marks around the string: rn.source = "SELECT * FROM [sheet1$] WHERE [CITY]= '" & CriteriaCity & "'"
Hi, very helpful video! However i got an error "The SELECT statement includes a reserved word for argument name that is misspelled or missing, or the punctuation is incorrect." when i tried to get the data with [Run Time], [Studio], [Budget] . I double checked everything. All other methods worked just like in the video. Any ideas? thanks
Hi! It's difficult to say what the problem is without seeing your code but here's what it should look like "SELECT [Title], [Run Time], [Studio], [Budget] FROM [Sheet1$]" I hope it helps!
@@WiseOwlTutorials Hi Andrew! Thanks a lot for this step-by-step video! May I whether this is valid for Excel16 64bit as well? I've activated the Microsoft ActiveX Data Objects 6.1 Library and I am using Excel16 64bit, but I keep getting the error: Run-time error '446':Object doesn't support named arguments
@@GuilhermeNassifBarbosa Hi Gill! Yes, this works for 64bit Office. There are several reasons you might be seeing this error, here are a couple of possibilities, I hope it helps! stackoverflow.com/questions/32303911/object-doesnt-support-named-arguments stackoverflow.com/questions/48397088/error-object-doesnt-support-named-arguments-vba
@@WiseOwlTutorials Awesome!! Thanks again Andrew!! It’s working now! Had left an extra espace btw the text and the semicolon!!!! Btw I’ve learned VBA thanks to you!! You are a legend! 🏆🏆🏆
Hi! You could use an inner join to return all the rows where the joined fields have a matching value. You might find the videos on joins in this playlist useful ua-cam.com/play/PLNIs-AWhQzckUd5i0E1xeSFeBAonYIurK.html I hope it helps!
@@WiseOwlTutorials Hm... i'm a bit upset... At work i have many files with rows number over 200k (base files). And to get simple description of material with ID it's quite slow. Usually at the moment i need to get descriptions about hundred ID's. It will take eternity for the beginning, when i need to make further analysis with data i've got... :(
I've "solved" the problem with saving book in xlsb. Now it takes in twice less than with xlsx. But after restarting excel, it works more worse than it was before. :)
I encountered another problem and would appreciate your wise (owl) comment: I tried to read data from specific columns in the source file. Data in the source file is stores in a table that DOES NOT starts at A1... I’m getting an error when I try to read the data (the arrow is in the rs.open line of code). Is there any way to bypass this problem? Thank you in advance for any comment.
Hi Tal! I'd take a look at the first video in this playlist ua-cam.com/play/PLNIs-AWhQzckUd5i0E1xeSFeBAonYIurK.html particularly the part which explains how to select from a range. I hope it helps!
Hi I understood how to get the data from a closed workbook but can you tell me how to delete the data and update data using record set in a closed workbook as we are not allowed to use ms access database hence I am using as a database file.... Please help
Hi Safeer! You can use an UPDATE statement to modify existing data - we'll have a video on that topic later in our playlist on SQL for Excel Files. Unfortunately, the DELETE statement isn't supported when using the ACE OLEDB provider to connect to an Excel workbook. The best option appears to be using the UPDATE statement to change all the values of a record to null. You'll then need to run some sort of clean-up periodically to delete the empty rows from the workbook, but you'll need to open the file to do this. I hope that helps!
I find this works on some files but not others. The query doesn't seem to find anything on some files yet does on others (all exactly the same format). VBA does not report any errors.
Hi Keith, when you say it doesn't find anything do you mean it doesn't return any rows or it doesn't even find any fields? If you step through the code and use the Locals window when the recordset has been populated, how many Fields does it contain? If it's finding fields but no rows have a quick check of any criteria you've added to the query and make sure that rows in the source file match the criteria. I hope it helps!
@@WiseOwlTutorials well, I've had a few days to look at this. I have a folder with 9 .xlsx files of various row numbers (90 to 5000+) all containing 12 columns of data. when I loop through the folder to add the remaining 8 files to the first file only 4 files return any data. As you suggested , I have checked via the locals Window and the recordset in four of the files contain 12 fields but the other four recordsets show only one field and the recordset is not populated. at the end of the process I should have a file with 22000+ rows but it only contains 8500 or so.
@@keithjacks915 Hi Keith! That's really strange if those four files have exactly the same format as the other four. I'm not sure what to suggest at this point.
@@WiseOwlTutorials I think it must be the files, I've just changed to Office 365 and the files were sent to me via Google. I have other folders which give me the same problem and I find 365's security settings a nightmare.
Great video, thank you so much! My coworker and I are trying to implement and running into a "Run-time error -2147217865 (80040e37)" when hitting the rs.Open line. Not sure how to adjust around that, do you have any advice?
@@trispiral Not Microsoft's most helpful error message! What does the rest of your code look like? Particularly the part which sets the source for the recordset.
@WiseOwlTutorials Hi there, Coworker here. We've both tried multiple times to reply with the code however the replies never seem to stay up. Is there a different way we can get it to you?
@@christinaperry3124 Hi Christina, sorry that UA-cam's comment system isn't being very helpful! You can try posting a comment on the relevant video on the Wise Owl website www.wiseowl.co.uk/vba-macros/videos/vba-ado-net-recordsets/copy-from-closed-workbook/
Hi Andrew, I came across a situation where I need to get data from a closed file. However, the closed file has an Excel table in it call "tblCorporate_Tax_Rates". I would like to get only 2 columns of that table i.e., the first column in full and then column "United States" in full. I have your full code just need to configure it for Excel table name instead of range. Would appreciate your help
Hi Muhammad! Sadly, this technique doesn't support Excel table names. The first video in this playlist shows various ways to reference Excel ranges in your SELECT statements, I hope it helps! ua-cam.com/play/PLNIs-AWhQzckUd5i0E1xeSFeBAonYIurK.html
Hi Andrew, Thanks for this awesome video. I was trying to replicate your did but I am getting "Could not find installable ISAM" error. It occurs when I am trying to check my connection string. Can you please tell what could be the issue behind this error ?
Hi Ravi, do you have the correct driver installed? This link has 32bit and 64bit drivers available, just pick the one which corresponds to your version of Office www.microsoft.com/en-us/download/details.aspx?id=54920 For SQL Server you'll need this driver docs.microsoft.com/en-us/sql/connect/oledb/oledb-driver-for-sql-server?view=sql-server-ver15 We have a separate video which covers connections to SQL Server ua-cam.com/video/OWKae1pTTnE/v-deo.html I hope it helps!
Hi Dan, absolutely! It's all about the connection string of course - www.connectionstrings.com/textfile/ For text files, the connection string points to the folder containing the files rather than a specific file. The recordset source treats a text file as a table for the SELECT statement. Here's a basic demo: Sub ConnectToCSV() Dim cn As ADODB.Connection Dim rs As ADODB.Recordset
@@WiseOwlTutorials That's great! Thank you! Is there any way of making numbers and dates format correctly doing it this way instead if just being formatted as text?
@@danforster1707 Hi Dan! You can apply any formatting you need after getting the data into the cells. The NumberFormat property of a Range is probably the best option docs.microsoft.com/en-us/office/vba/api/excel.range.numberformat I hope that helps!
Thanks for your tutorials WiseOwl! Can we dynamically change the path of the data source, perhaps add a wildcard or some inputbox? Saw some interesting code to possibly address this but not sure if this would work: Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" + @[User::InputFolder] + "\\"+ @[User::FileName] +"; Extended Properties=\"EXCEL 12.0 XML;HDR=YES\";
Hi Alvin! Sure, the connection string is just a string, you can concatenate it from as many different strings as you like. If you want the user to pick a file I'd recommend using a File Dialog, here's a video ua-cam.com/video/6ZIFNAV1rOQ/v-deo.html Incidentally, the code you've posted looks like it's from SSIS, not VBA. The principle of concatenating strings is the same but VBA uses different operators and uses different syntax to refer to variables. I hope it helps!
@@WiseOwlTutorials Perfect! Really appreciate your response. Also, thank you for making these videos as it certainly adding value to a lot of people who wants to learn. Kudos!
Is it possible to use this technique in the environment where multiple users need to connect to same file at random times? Mostly for reading but occasionally writing.
Hi Dimitri! I haven't tried but this post suggests not www.vbforums.com/showthread.php?395528-Problem-with-multiple-users-accessing-an-excel-sheet-through-ado
@@WiseOwlTutorials Thanks. Figured it out. It works for reading data. And there's a workaround where, to make long story short, you kill & replace entire file when writing. With proper safeguards works well enough. PS You're literally the best teacher on UA-cam, no exaggeration, I've had a lot, you're the bomb. Keep it up o/
I can't manage to overcome the limitation of 255 columns to be read, actually i found some threads online talking about reading 255 column each time and then merging the data bases, but i just can't find any code or real explanation on how to do that, could you please help me? this problem is driving me crazy
My problem is that i need one specific column in a big database of 1500x5200, if the SELECT parameter could search for more than 255 columns it would be easy, but this limitation just seem to make my problem impossible to solve
Hi Alexandre! I just thought you'd like to know that I've added a members-only video which explains how to achieve what you need. Here's the link in case you find it useful ua-cam.com/video/c516t2HVH2A/v-deo.html
Hi Safeer, I'm sorry I don't know why Excel would stop responding. There are lots of reasons that the code might not work: not having the correct driver installed, not having the source file stored in the specified folder, etc. but all of these would display a specific error message.
@@MatrixRKL I don't know what's in your folder and workbook variables but have you made sure there's a \ character separating the two? The connection string looks fine otherwise - this example works correctly MovieFilePath = ThisWorkbook.Path & "\Movies.xlsb"
Hi Andrew. What happens if your closed worksheet isn't set up like an SQL database table and you want to retrieve data from a specific cell that is always next to a cell with the text "Bank Statement"? I know in VBA, you would search for that text and then .offset to get to the data, but for closed workbooks, are we restricted to using SQL to get data? In that case, can this still work?
Hi Roger! You need to use SQL but the worksheet doesn't necessarily need to be structured as a table. Look at the sections in this video which show how to refer to a specific range of cells and how to refer to columns without using column headers. You can add criteria to the query to look for the text 'Bank statement' in a specific column and return the value from the adjacent column using the [F1] [F2] style of referencing columns. Good luck!
@@WiseOwlTutorials Gotcha, I see how it can work but it sounds like ADODB connection was meant for actual databases which makes sense in order to automate working with large amounts of data downloaded to Excel. Still, this ability to use this connection opens up a lot doors. Just need to brush up on SQL. Thanks!
@@rogerh2694 Agreed, it's certainly easier to work with well-organised tables! We have a playlist on using SQL for Excel in VBA which you might find helpful ua-cam.com/play/PLNIs-AWhQzckUd5i0E1xeSFeBAonYIurK.html Enjoy!
Dear wise owl, your videos are extremely helpful. The functions of this video work without problems applying it from excel. However, I am trying to make use of the same code from an old program in Visual Basic 6.0 and it gives me a problem. EVERYTHING WORKS PERFECTLY ONCE: the ADODB.Conecction is established, it performs the SQL query, it returns the expected values, but after that nothing of the program can be used, it gives an error that closes the program without being able to catch the error . I have tried with different versions of excel setting the ConnectionString properly, but still the same problem. Might you help me? Thanks a lot.
Hi Robert! You can add cell references after the worksheet name like so [Sheet1$A1:G100] You can return blank rows in the results or remove them by applying criteria to any of the columns: SELECT * FROM [Sheet1$A1:G100] WHERE [ColumnName] IS NOT NULL You might find this playlist useful, we'll be adding more videos to it this week ua-cam.com/play/PLNIs-AWhQzckUd5i0E1xeSFeBAonYIurK.html
Hi Andrew, "SELECT * FROM [Sheet1$]"...is there any way to replace [Sheet1$] with a variable? I have to loop through all Report Filter Pages (around 90 reports) of a Pivot Table and update each Tab to its respective file almost daily. Now using Open then Close file method...Open [A] file, copy data from Pivot Tab[A], then after little touch up, close [A] file; then Open [B]... If there is any old episode that mentioning this technique please feel free to point me to, no need to type everything here. Thanks for your kind instruction in advance. J
Hi J! Yes, you can use a string variable to specify the name of the worksheet. This video ua-cam.com/video/5F8q7Z9kWGw/v-deo.html uses the technique in a different way to the question you're asking but might be enough to point you in the right direction. I hope it helps!
@@WiseOwlTutorials Hi Andrew! Great video 👍 What if I don’t know the name of the sheet but I do know that the closed workbook has just one single sheet or the data I want to copy is in the first sheet? Is there a simple way to point to this sheet or has to passed always the string of the sheet in the SELECT statement?
@@pacoperez2780 Thanks Paco! I think you might find this video useful ua-cam.com/video/5F8q7Z9kWGw/v-deo.html It shows how to discover the names of the worksheets in the closed workbook and loop through them. I hope it helps!
Have a problem, the file that I am trying to query is protected, is that the reason why I get the message "Could not decrypt file". The range I am trying to query is not protected. Can someone help me with this?
Hi Andrew, as far as I know you can't pass a password to the connection string for ACE.OLEDB. There's a long discussion which includes a workaround for this issue here but I haven't tried it myself www.vbforums.com/showthread.php?842899-RESOLVED-How-to-get-data-from-a-password-protected-closed-excel-workbook I hope it helps!
@@WiseOwlTutorials You went to the trouble to reply to me, I did not expect this at all. What a great guy. I will read that work around, thank you so much for this. I continue to watch your excellently presented videos, unbelievably clear.
Thanks a lot sir, Need help? Want to set password in access database using excel vba I have created the database and table using vba but can't set the password Can you help me on this Using office 365
So, Question. How would I do the opposite and instead of getting data FROM a closed Excel File, How would I get data INTO a closed Excel file using ADO?
Hi Rohit! Can you explain more what you mean by "same reference Id"? Is this the name of a column in your data, or a cell reference or a range name or something else entirely?
@@WiseOwlTutorials reference Id is a column name.example coulmn A A1 $20 and A2 $40 and coulmn B B1 reference Id 23456789 , B2 reference Id 23456789. So result should be in A1 amount $60 with B1 23456789.
@@rohit_pandit33 Not sure if that's helpful, but the following should give you a unique list of reference ids with their summed values: SELECT SUM(column_A_name), [reference id] FROM [Sheet1$] GROUP BY [reference id]
Thanks! I have been looking around for these for the whole day!
Happy that you found it, thank you for your support!
I have been resistant about doing data extraction stuff outside the usual Excel method (open the source file, copy things over, close source file) because the tutorials are usually abstruse and frustrating. But this video is crystal clear and you go through the steps at the appropriate level of detail and with the right pacing for me to follow. Excellent stuff! Thank you, thank you.
Thanks! I'm happy to hear that you found it useful and I appreciate you taking the time to leave a comment!
I followed your instructions explicitly and it worked great! Thank you for your videos.
Happy to hear that Thomas, thanks for your support!
Deserves a Nobel prize, as always :)
😀 thank you! I'm happy that you enjoyed it!
sorry to be so offtopic but does anybody know of a way to get back into an instagram account??
I was stupid lost my login password. I appreciate any tricks you can offer me.
@Brandon Malik Instablaster :)
I had been looking for this for years.
Thank you so much.
Thanks for the support!
Dear Wise Owl Highly appreciate the way you explain, its just Excellent.
Thank you so much for the kind comment!
Wow! Just wow! Excellent material and demonstration. I'm going on 60 and been writing VBA and Studio for about 5-6 years now. To see other examples and styles of coding is so enlightening. Thank you!
Thanks Erick! Happy to hear that you enjoyed it and thank you for taking the time to write such a nice comment, it's much appreciated!
Amazing to learn new technology that's a life saver but has been available all along, right? Wish we knew this 10 years ago, but of course we weren't as wise back then.
Hands down, the best VBA training resource on the web!
I have a question: can you modify this procedure to copy data into another new workbook from a closed xlsx file given the following conditions.
1) I need to filter data for 1 or more criteria under the same header.
2) My source file table headers are on row 5.
3) I need source file rows 1-4 copied to the new workbook as they contain information about the file data that needs to go into the filtered reports.
4) I need to keep the same formatting (font size, colour, bold, cell colour etc.).
If you would say that this is possible, I could attempt a bit of tinkering to make it so. If not, I would be glad to be told to not waste time and to go back to the old open file and copypaste method.
Hi! Happy to hear that you've found the videos useful! To answer your questions:
1) and 2) are no problem
3) is a little awkward and depends on the layout of the header rows
4) isn't possible with this technique - if you need formatting information you'll need to have the file open, sorry!
@@WiseOwlTutorials Thank you for saving me from going down the fruitless rabbit hole that is #4 with this technique!
I work with another different set of reports that use 1, 2 and 3. While #3 is nice to have, it is not necessary. But I'm most intrigued about #2.
My source file has the Report Name and ID populated in Cells A1 and B1. The headers for the actual data start from A2 and goes across about 20 columns. Would your code above be sufficient for this, and if not, what have I got to tweak? I just need to pull the data filtered for 1 or more criteria. Row 1 with Report Name and ID can be disregarded.
@@busybillyb33 Hi again! You can reference the range of cells you want to query in a few different ways - it might be worth taking a look at the first video in this playlist which shows a few different techniques ua-cam.com/play/PLNIs-AWhQzckUd5i0E1xeSFeBAonYIurK.html&feature=shared
The playlist also has multiple videos on adding criteria to your queries.
Hope you find what you're looking for in there!
I have learned excel and VBA using your videos.....
Happy to hear that you've found the videos useful Safeer!
Your explaining is complete and wonderful, thanks.
Thank you! I appreciate the comments!
Great tutorial, the best vba on YT, Thank you Andrew
Thanks for your support janez!
Sir, your lectures are perfect!!!! THANK YOU SO MUCH!!!!
You're very welcome! Glad you like the videos, thanks for watching and for the comment!
Greatest tutorial ever on this topic 👍👍
Thanks, glad you enjoyed it!
Very nice and interesting. Wonderfully explained. Thank you.
Thank you Rohith, I appreciate your support as always!
👍🏻Thanks a lot.. exactly what i was looking for. Very nicely explained in simple manner
Happy to hear you found it useful, thanks for watching!
your training video is great and helps me a lot to Pull data form Closed Workbook,
need little help to pull headers too, as you mentioned in your video some headers name not showed , same problem I face and need a solution to pull every header
For this video you get at least one Oscar!
😀 thank you Robert! I'd like to thank my mum, my agent...
As always, excellent demo
Thanks David!
Nicely done. Worked perfectly. Thank you
Happy to hear it Gregg, thanks for watching!
Thanks! Brilliant as always. I wonder if there is an elegant way to find the last row in as specific column on af specific sheet? To make it more complicated, there might be empty cells in top of the column.
Hello Andrew, You have a way of explaining complex things in a calm, simple way-Thank you! Following on from this video, is there a way to a) determine whether there is an image in the data from your closed workbook. b) How do I retrieve the image to include it in another workbook?
Thanks Martin! No, you can't check for images in a closed workbook, at least as far as I know. You'd need to open the file and then apply copy and paste methods to the Shape object representing the image.
@@WiseOwlTutorials Thank you Andy that's exactly what I did, but was hoping to speed up the query by doing it with a closed file. I think I will have to convert my 12 workbooks with multiple sheets into an access database, while trying to keep it normalised... Any suggestions?
@@martingregson7136 Hi Martin! It's tricky subject to offer simple help on but if you haven't done this before I'd start with these two links
learn.microsoft.com/en-us/office/troubleshoot/access/database-normalization-description
support.microsoft.com/en-us/office/move-data-from-excel-to-access-90c35a40-bcc3-46d9-aa7f-4106f78850b4
I hope it helps!
Wooow!! Very helpful!! Thanks. I wondering if you could explain how to get the same data (with ADODB connection) but based on an array within the workbook open.. I mean get only certain rows from the closed workbook that match with a list (array) in the openned book... Thanks!!
You're very welcome Xavi! And that's a nice suggestion, we could do that by constructing a comma separated list of values to pass into the IN operator of the SQL query. I'll add this to my list and look at creating a video to show how this might work, thanks for the suggestion!
One word - AMAZING 👏
Thank you Serge!
WOW that was really really well explained, thanks so much!
You're very welcome Andrew, happy to hear that you found it useful!
(However it returns a run-time error in my existing Macro Enabled Workbook, but not when I experiment with a new Brand New Macro Enabled workbook, so the code works, its very odd. Error - -21474672259 (80004005) Cannot update - Database or object is read-only. Spent hours trying to resolve.
I got it working :)
@@andrewwest5344 Good work, happy to hear that you found a solution!
@@WiseOwlTutorials I subscribed, you have loads of videos! I develop vba applications at work but I am self taught and learn so much from people like you on youtube.
Great video! Thanks for sharing and Thumbs Up!!
Thanks Victor, glad you liked it!
Super helpful!! Thank you.
You're very welcome, thanks for watching!
Very Extortionary and power full . Next month i will get promotion
Thanks for watching!
I've just watched this wonderful video and didnt try yet ('ll do this tomorrow on the work), it looks great (THANKS!).
But my next question is how to write portion of data into defined place of closed workbook still without it's opening?
Thanks John! You might find this playlist useful in general ua-cam.com/play/PLNIs-AWhQzckUd5i0E1xeSFeBAonYIurK.html
Parts 58.28 to 58.30 cover how to insert new data or update existing data using ADO.
I hope it helps!
@@WiseOwlTutorials oh, big thanks, Sir! Now i have what to do on this weekend.😊
@@Rice0987 It sounds like you're in for a fun weekend! 😀
@@WiseOwlTutorials Parts 58.28 to 58.30 are to far for me now, so i think i should start from the beginning. And i hope "i'll try so hard and 'll get so far". :)
Great thanks, Sir for sharing your priceless material for my many weekends forward. :)
@@Rice0987 Have fun John!
Hi Andrew, wonderful tutorial, thanks a lot. I followed the instructions and after some trouble got the results. Thanks again.
I have a question rhough. Why we use thisworkbook in cn string? Whereas we are reading from closed workbook. I my practice, i removed it and still got the result.
Hi! We use ThisWorkbook.Path to return the path to the folder that the current workbook is stored in. It's shorter than writing the complete folder path!
I hope that helps!
Really love ur vedios....
Thank you!
Really amazing tutorial. Thanks a lot.
My pleasure Yasser and thank you, as always, for your continued support!
I have found this concept pretty intriguing. I've been looking for naswers in the Excel community, but that having failed going to try to reach out here. Can I use this method with a match function of the closed workbook? I want to find the row number in the closed book worksheet matching a value and then extract data from cells in that row?
Hi! In short, no, you can't. But the good news is that you don't need to - you can use the SELECT statement to retrieve any values you need. I made an entire series on using SQL to query Excel files which you can find here ua-cam.com/play/PLNIs-AWhQzckUd5i0E1xeSFeBAonYIurK.html
I hope it's useful!
Thank you for your very informative video! Can I ask if it is possible to open two connections (to two external workbooks), and do something like a LEFT JOIN?
Thanks Joel! You could create a join if the worksheets were in the same workbook but I'm not sure that it's possible if the worksheets belong to separate workbooks but I'll put it on my list to investigate!
@@WiseOwlTutorials Thanks! Hope there will be a tutorial on that in the pipeline!
@@joelwong3189 There might be 😀
@@joelwong3189 OK, got it! I'll put it into a video format at some point but here's the basic code:
cn.ConnectionString = _
"Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=" & ThisWorkbook.Path & "\My Files\Film.xlsx;" & _
"Extended Properties='Excel 12.0 Xml;HDR=YES';"
cn.Open
rs.ActiveConnection = cn
rs.Source = _
"SELECT f.Title, f.ReleaseDate, f.RunTimeMinutes, g.Genre, s.Studio" & _
" FROM (([Film$] AS f" & _
" INNER JOIN (SELECT * FROM [Excel 12.0 Xml;Database=" & ThisWorkbook.Path & "\My Files\Genre.xlsx;].[Genre$]) AS g ON f.[GenreID] = g.[GenreID])" & _
" INNER JOIN (SELECT * FROM [Excel 12.0 Xml;Database=" & ThisWorkbook.Path & "\My Files\Studio.xlsx;].[Studio$]) AS s ON f.[StudioID] = s.[StudioID])"
rs.Open
There are three files:
Film.xlsx with a worksheet called Film
Genre.xlsx with a worksheet called Genre
Studio.xlsx with a worksheet called Studio
Each table has a primary key FilmID, GenreID, StudioID
The Film worksheet contains foreign keys GenreID, StudioID
There's at least two ways of referencing the other workbooks in the FROM clause but I felt that this was the cleanest.
Hope it helps!
@@WiseOwlTutorials you are AWESOME! Thank you a lot!!!!
thank you so much sir❤️
You're very welcome Manoj, thank you for watching and for the comment!
You are amazing. You explained everything step by step 😍.
Thanks Amit, glad you enjoyed it!
Hello Wise owl thanks for the tutorial but I have a question. I wonder if you can change the position of the header (HDR) in the in your request because mine is located in the 4th row. Thanks in advance for your answer.
Hi Antoine! You might find the first video of this playlist useful ua-cam.com/play/PLNIs-AWhQzckUd5i0E1xeSFeBAonYIurK.html
It shows various techniques for referencing cells in a worksheet. I hope it helps!
@@WiseOwlTutorials thanks a bunch i'll check it out
Amazing video. Do you have a video showing how to get data from specific sheet in a closed workbook and insert that data into a closed access database using SQL?
Hi Daniel! We don't have a single video which shows that specific technique but you'll find this playlist helpful ua-cam.com/play/PLNIs-AWhQzckUd5i0E1xeSFeBAonYIurK.html&feature=shared
And this video for the Access-related INSERT statement ua-cam.com/video/-c2QvyPpkAM/v-deo.htmlfeature=shared
I hope it helps!
@@WiseOwlTutorials Thanks for the links. I am going to check them out right now. I did have 1 more question I was hoping you could help me with. Would it be considered "best practice" to open a connection to the Excel file and Access Db at the same time and then SELECT and INSERT the data using 1 SQL statement and then close both connections or open a connection to Excel, SELECT the data to memory and close the connection to Excel and then open a connection to Access, INSERT the data from memory and close the connection to Access or does it really not matter?
@@danielsteele6938 Ultimately I don't think it matters. I'm not sure if this approach works for Excel and Access but the technique shown in the last part of this video is what I'd try to start with ua-cam.com/video/mKZeKf7bE8w/v-deo.htmlfeature=shared
Hi Andrew, many thanks for your videos. I'm trying to use this technic to combine data from differents closed excel files and put results in an excel table. Is there a video where you already covered this topic? Many thanks again.
Hi Alessandro! Yes, we have several videos which explain how to do this in various ways. If you go to the Wise Owl Tutorials channel page here on UA-cam you can use the search tool to search for "ADODB"
I hope it helps!
Hi :) Great Video!! I am needing this information for a work project. I do have a question. Do you have another video explaining how to use a FORM with ADO? My co-workers are not excel savvy and I wanted to make this process as easy as possible. I wanted to have a form and when you enter a specific identifier (ie. "Work Order") and click a command button, any information on that identifier would populate from the closed spreadsheet into our working spreadsheet. Even more complicated is that I need only specific columns of information to be populated into specific cells on our worksheet. Any help in that? Any video? I do appreciate your time.
Thank you SO much for this useful method! I have one question. Is it possible to make the Data Source property to be dynamic (e.g. to prompt the user to choose the file to make the connection with)?
For sure! You can use a FileDialog like this ua-cam.com/video/6ZIFNAV1rOQ/v-deo.html
I hope it helps!
Thanks Andrew for this great tutorial and kindly accept my questions is how to connect encrypted closed work sheet with a password ?
Hi! As far as I know you can't pass a password to the connection string for ACE.OLEDB. There's a long discussion which includes a workaround for this issue here but I haven't tried it myself
www.vbforums.com/showthread.php?842899-RESOLVED-How-to-get-data-from-a-password-protected-closed-excel-workbook
I hope it helps!
Hello Wise Owl. This is awesome I like it I wish I had learned to code like this. Could you help me to understand what happen if you don't close the connection? Does the connected document will have irreparable problems ?
Thanks
Hi Ezra! The connection will close automatically when the variable goes out of scope so you don't technically need to close it in the small example shown in this video. It's good practice to close the connection when you have finished with it to free any resources used by it. Here is some documentation from Microsoft docs.microsoft.com/en-us/sql/ado/reference/ado-api/close-method-ado?view=sql-server-ver15
I hope it helps!
What can I do if I didn't close a connection
Thanks!
Hi WiseOwl,
As always brilliant job done here. Unfortunately I have encountered error which says that database engine could not find object (which is sheet name [Oct 21$]. Connection is working but after adding SQL statement it fails. I need to add that file is on shared drive.
Code error -2147217865 (80040e37)
Thanks in advance
Hello. How do I open several files from a specific folder, copy from them and paste in a master excel sheet please? Also Since the headers are not written in the same way in the different excel sheets, i will need to do a matching so as to tell the macro when it sees for example "NAME1" in row 10, it should copy the columns information and paste in the master excel file in column with header "Name"?
Hello wise owl. Thank you for all those learning videos. They are great. I just discovered you and can not wait to watch and take some notes from them. However I had question to you. I face a problem while I pull the data from closed workbooks if they are opened by someones at that time. ADO opens the excel file first if the source excel file was opened at that time by somebody else, even though it pull and copy the data correctly afterwards. How can we enable ADO not open the source file in any case? Is there a way about it?
Hi Deniz! Sorry, I don't know the answer to that but you're not the only person to experience it stackoverflow.com/questions/25400750/why-does-excel-file-opens-when-opening-connection-using-ado
Thank you so much. How do i make a User define function that return dynamic arry (result of adodb recorset) when i put in a single cell in wotkbook?
I don't know, sorry!
Excellent vedio ❤❤, i use sumifs function to get data from closed excel files. Can you please explan, how to use this methord for sumifs function. Thanks
Hi! You can't use SUMIF with this technique as it's an Excel function and this technique relies on a version of SQL. You can learn about all the things you can do with this technique in this playlist ua-cam.com/play/PLNIs-AWhQzckUd5i0E1xeSFeBAonYIurK.html I hope it helps!
Thanks
I just love your videos, you are truly a hero!
I have a little question that I hope you see and can answer.
I wonder (if possible) how you would do, to copy/move/push data from a sheet or sheets (in a master document if you like) to several closed woorkbooks? I would (again if possible) want the master document to be .xlsm and the other woorkbooks to be .xlsx.
Hope you see this and have the time to help us all lost in the VBA-universe. take care!
Hi there! We can insert and update data in a closed Excel workbook using ADO. I plan to have a video on how to do this soon but if you'd like an idea of how it works with a Microsoft Access database here's an older video I made which you might find useful ua-cam.com/video/-c2QvyPpkAM/v-deo.html
I hope it helps!
@@WiseOwlTutorials thank you for taking your time to answer! Ill have a look. 👍❤️
@@mesotin No problem!
Wow, what an (other) amazing and very useful video. Thank you very much. So, if we use this technique to import datas from multiples Excel Workbooks (hundreds of files); it will be quicker than open and closing each one of those files?
Thank you Sylvain! And yes, that's the idea at least - connecting to each file without opening it should be quicker than opening and closing each file in turn. Thanks for taking the time to leave a comment!
Thank you for your response. Another question: it is possible with this technique to select only specifics cells? I mean by example: values of B15, F17 and W30. Can we do it with only one sql command?
@@sylvainlavallee8263 Hi Sylvain! Yes you can although it's not very elegant! You'll need to set the HDR property to NO
"Extended Properties='Excel 12.0 Xml;HDR=NO';"
You can then UNION SELECT individual cell values like so:
rs.Source = _
"SELECT * FROM [Sheet1$B15:B15] " & _
"UNION SELECT * FROM [Sheet1$F17:F17] " & _
"UNION SELECT * FROM [Sheet1$N30:N30]"
You must make sure that each selection contains the same number of columns but you can select any number of rows from each separate union. You'll get the results returned as a single column so you'll have to work out how to pick out which value is which.
I hope that helps!
Excellent 👌
Thank you!
Hi, is possible to not have fix path in the Data Source? The reason is my source file is generated with different name and date every day. Is it got an option excel will ask you to browse the file after you click the button?
Thank you so much. After I have watched your tutorials about adodb for access 4 years ago i have been practicing it until now. One thing I have learned from access is that there is no need to save the file to save the added data unlike in excel workbooks that you need to save the workbook to save the changes. Is it possible to do this is excel also using this method? Thank you so much in advance. I really love your tutorials. Can't use the right words to praise your works but still you are the very best among the best.
Hi there! It's nice to know that the Access ADO videos are still useful even now! And yes, you can modify data in a closed Excel workbook using ADO. As long as the workbook is closed, you don't need to save changes. You can send INSERT and UPDATE statements like this (assuming you've created and opened the Connection):
Dim cmd As ADODB.Command
Set cmd = New ADODB.Command
cmd.ActiveConnection = cn
cmd.CommandType = adCmdText
cmd.CommandText = _
"INSERT INTO [Sheet1$] ([Title],[Release Date],[Run Time],[Director]) " & _
"VALUES ('Tenet','12 Jan 2021',123,'Christopher Nolan')"
cmd.Execute
And for UPDATE:
Set cmd = New ADODB.Command
cmd.ActiveConnection = cn
cmd.CommandType = adCmdText
cmd.CommandText = _
"UPDATE [Sheet1$] SET [Run Time] = 150 " & _
"WHERE [Title] = 'Tenet'"
cmd.Execute
I hope it helps!
Wow thank you so much. I really thought that it can be done only by using access. Thank you once again. Can't wait to try this.
P.S. All of your videos have helped me a lot. Not to mention I have watched the Data Labels in Scatter Charts. If i'm not mistaken it was Part 42 back then even though it is no longer in your list i still remembered it. This is my way of saying that you really are the best teacher and I am one of the many people out there that respects you. More power.
@@MyAudioBookCompilation My pleasure! The video you mentioned is still there by the way, we just renumbered it so that it fit with the other videos on charts ua-cam.com/video/SidxAJ1MRFo/v-deo.html
Good memory you have!
Thanks for another great tutorial.
I noticed that the source data file (the closed workbook) name must be in English. If it's not in English, the code generates an Run-time error message.
This code works perfectly when data (in the closed workbook) starts at the very first row (including the header row). But what do you do when data starts, for example, in row 10 and you don’t want to copy any content about that row?
Thank you in advance for any useful advice.
Thanks Tal, glad you enjoyed it! I wasn't aware of the English name requirement, that's interesting to know, thanks for sharing!
If you want to avoid copying data outside a specific range you can use the cell references in the SELECT statement as shown at 13:01. You can also use range names if you have those available, like so:
'Use cell references
rs.Source = "SELECT * FROM [Sheet1$J9:L41]"
'Use a range name
rs.Source = "SELECT * FROM [Films2016]"
I hope that helps!
@@WiseOwlTutorials Thank you for your prompt reply. Is it possible to use a Table name instead of a range name?
@@litmol Hi Tal! Excel table names don't appear to be supported. If the table is in an open workbook you can return the Address property to get the cell references as described here stackoverflow.com/questions/47029764/use-table-name-in-sql-query-in-vba-excel
Otherwise it's cell references or range names only as far as I know!
Thanks a lot for the wonderful videos. Would like to know how would i fetch the data if my workbook is password protected. How to pass the password using the connection string. Thanks in advance.
You're very welcome Rajesh!
As far as I know you can't pass a password to the connection string for ACE.OLEDB. There's a long discussion which includes a workaround for this issue here but I haven't tried it myself
www.vbforums.com/showthread.php?842899-RESOLVED-How-to-get-data-from-a-password-protected-closed-excel-workbook
I hope it helps!
Hello Wise Owl, Briljant tutorial. Can i also use variables for the critirials and how?
Hi Paul!
Yes, sure you can! Your SQL statement is just a string - you can concatenate it from as many parts as you like. Try this part of an earlier video (it uses Access for the example but the same principle applies to Excel)
ua-cam.com/video/HE9CIbetNnI/v-deo.html
If you want to do it the formal way, you can use parameters instead. This video explains how (it uses SQL Server but the same principle applies)
ua-cam.com/video/I31Mh5-8Z1g/v-deo.html
I hope it helps!
Thank you! This was a huge help!
Question, is there an easy way to make your row 2 your header row?
Hi! Do you mean you want to use the 2nd row of data in the recordset as headers? Or do you want to place all the data in row 2 of the output worksheet?
@@WiseOwlTutorials Thanks for the reply. I want to use row 2 of the data record as the header. You already showed how to place data in the 2nd row.
@@liefschneider3123 Hi! I would just import everything from the recordset, don't bother with the field names, and then delete the first row from the worksheet. I think that's the easiest thing to do.
@@WiseOwlTutorials Thanks for the reply! That was my solution as well, just figured there would be a more elegant way
@@liefschneider3123 No problem! I guess you could set up a named range in the source workbook which starts at the second row of data and then use the named range in the select statement rather than the worksheet name. But I think it's much easier to continue doing what you're doing!
Hi Andrew. Just wanted to one thing how to paste the content in the closed workbook and that also in the required column Or row or cell?
Hi Yash! You can see how to write data into a closed workbook in part 58.28, 58.29 and 58.30 in this playlist ua-cam.com/play/PLNIs-AWhQzckUd5i0E1xeSFeBAonYIurK.html
I hope it helps!
Hi, Mr. That is really coool. Apprecite! Is there any chance to do versus versa? I mean to copy data from current workbook to closed workbook?
Thanks Yerzhan! Yes you can write data to a closed workbook using ADODB using SQL INSERT statements. I plan to have a video on this at some point but just in case you needed a quick answer you can see this post, I hope it helps! www.mrexcel.com/board/threads/insert-multiple-records-using-ado.110166/post-589361
@@WiseOwlTutorials Thanks, but that posy is from 2005. Is it still workable in our days? Not sure. Waiting for your video about this.
@@yerzhankosbayev6436 Indeed it does still work! Insert statements haven't changed. Hopefully we'll have the video up at some point this week, depending on the courses I'm teaching.
@@WiseOwlTutorials As always the most useful tutorials! If you are still working on this video, could you include a Delete statement? Could one delete all the rows (except header) in the source sheet after retrieving it?
@@deonmuller595 Thanks Deon! I'm still adding videos on ADODB and modifying data videos will appear some time hopefully soon. Sadly, however, the ACE.OLEDB provider doesn't support deleting rows from an Excel workbook. You'd have to do this using regular Excel techniques which annoyingly would involve opening the file first.
I know it's been 3 years but I need your help with a scenario like this. I have an online workbook stored on OneDrive it should have served as a database but ACE OLEDB doesn't work with online files or file path. Is there another way I can dynamically connect workbooks to other workbooks serving as databases based on certain criteria? Let me know if need more info
@designexperience, I ran into the same issue. I solved by figuring out which local drive OneDrive syncs with (C:\users\xxx\...) and referencing that directory in the connection string instead of the OneDrive path (which throws errors in VBA due to not recognizing the path's URL syntax).
@davidkopec5587 it doesn't even recognize URL, only absolute path like a local directory
@@designexperience Yes, exactly.
Hi Andy, can you tell me why do you use the ADODB library, instead of setting a reference to the workbook with the GetObject methode, is it better, quicker ?
Hi Karim, funnily enough I added a video on early binding and late binding recently! It shows one reason why late binding and the CreateObject function can be better than early binding ua-cam.com/video/qmJPbub-D0E/v-deo.html
In general I use early binding because it's so much more convenient to have the IntelliSense prompt you with the relevant classes, constants, properties and methods without having to look them up. If I'm writing code for someone else and I'm not sure which version of the library they have installed it's trivial to switch to late binding after writing all the code, as the video I linked to shows.
There's a suggestion here www.cpearson.com/excel/optimize.htm under the Early Binding section that it's faster to use early binding than late binding but I've never noticed a significant difference and convenience is the main reason to use it for me.
I hope that helps!
Pl let us know how to extract data from closed file by using vlookup.There are two file one file name keep updating which file need data from master data file .Master data file is updating once in week .
Hello @wiseowltutorials, is it possible to get data from closed workbook, keeping the format of the source data intact? In the way that you have explained, it is just getting plain data without the formatting and formulae.
Thanks
Hi Pratik! No, ADODB doesn't retrieve any formatting information. You will need to open the file if you want to copy that type of information.
I hope that helps!
Nice work Andrew. A couple of questions here. 1) Can we use this on the same workbook, i.e. to query a sheet that is in the same workbook? 2) What if we don't know the Field Names of the sheet that we want to query from? 3) Can we populate the filtered results to userform listbox directly i.e without first placing them on a sheet and then loading from there?....There are more questions but for the time being.
By the way Adrew you gave a long pause in your Excel VBA tutorials.
Hi Imran, great to hear from you again! Yes, there was a big gap there - it's been a strange year for us at Wise Owl!
As to your questions:
1) Yes, you can!
cn.ConnectionString = _
"Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=" & ThisWorkbook.Path & "\" & ThisWorkbook.Name & ";" & _
"Extended Properties='Excel 12.0 Xml;HDR=YES';"
2) You can list the field names by looping through the fields collection. There are a couple of ways to do this, this one requires an Integer variable called i:
rs.Open
For i = 0 To rs.Fields.Count - 1
Debug.Print rs(i).Name
Next i
3) Yes, you can!
rs.Open
Me.ListBox1.ColumnCount = rs.Fields.Count
Me.ListBox1.List = Application.Transpose(rs.GetRows)
rs.Close
I hope it helps, good to see a familiar name!
@@WiseOwlTutorials Thank you so much for answering my questions in detail. I will use this in my projects and will let you know. Your tutorials are always helpful. The very first thing that I learnt from you was the concept of arrays(single and multi-dimensional, which I am much appreciated by my boss when I use them in my macros). Please continue sharing your knowledge with the community.
@@imranbhatti8580 You're very welcome Imran, I hope that it helped!
Hello Andrew. I am getting an error "either bof or eof is true or the current record has been deleted" on this line "Me.ListBox1.List = Application.Transpose(rs.GetRows)". My rs has more than 2 columns and I checked in the watch window that it has the records in it. but cannot load them to listbox. Can you please guide?
@@imranbhatti8580 Hi Imran! Did you perform any other operation on the recordset before GetRows, like CopyFromRecordset for example? If so, add this line before you attempt to use GetRows:
rs.MoveFirst
I hope it helps!
Dear wise owl, awesome video ...one small query is there a limitation of extracting data up to 255 columns?? as i have tried to extract data from my closed workbook and it is extracting data up to 255 columns and the rest i don't get it...
Hi Amit! Yes, unfortunately there is a limit of 255 columns when using the ACEOLEDB provider to query an Excel file.
Hi Andrew, thank you very much for the great work. I have a question, How to skip the workbook when a specific worksheet is not found, close it and then go to the next workbook? will appreciate your help.
Hi! I'd take a look at this set of lessons on writing error-handling code in VBA www.wiseowl.co.uk/online-training/excel-vba/error-handling/
I hope it helps!
Greatest Tutorial Andrew.
I have a SQL query (through an ADODB connection) that populates an Excel spreadsheet. The thing is that I want add a criteria "WHERE x = [cell reference]"
For example : CriteriaCity=sheet1.range("A1").value
in vba editor
rn.source = "SELECT * FROM [sheet1$] WHERE [CITY]= [CriteriaCity]"
but it throws error.
could you please help.
Thanks in advance
Hi! You need to concatenate the value of the cell into your query string, making sure to include the quote marks around the string:
rn.source = "SELECT * FROM [sheet1$] WHERE [CITY]= '" & CriteriaCity & "'"
Hi, very helpful video! However i got an error "The SELECT statement includes a reserved word for argument name that is misspelled or missing, or the punctuation is incorrect." when i tried to get the data with [Run Time], [Studio], [Budget] . I double checked everything. All other methods worked just like in the video. Any ideas? thanks
Hi! It's difficult to say what the problem is without seeing your code but here's what it should look like "SELECT [Title], [Run Time], [Studio], [Budget] FROM [Sheet1$]"
I hope it helps!
nice sir 👍🏻👍🏻👍🏻👍🏻👍🏻💯
Thank you Vijay, I'm glad you liked it!
@@WiseOwlTutorials Hi Andrew! Thanks a lot for this step-by-step video! May I whether this is valid for Excel16 64bit as well? I've activated the Microsoft ActiveX Data Objects 6.1 Library and I am using Excel16 64bit, but I keep getting the error: Run-time error '446':Object doesn't support named arguments
@@GuilhermeNassifBarbosa Hi Gill! Yes, this works for 64bit Office. There are several reasons you might be seeing this error, here are a couple of possibilities, I hope it helps!
stackoverflow.com/questions/32303911/object-doesnt-support-named-arguments
stackoverflow.com/questions/48397088/error-object-doesnt-support-named-arguments-vba
@@WiseOwlTutorials Awesome!! Thanks again Andrew!! It’s working now! Had left an extra espace btw the text and the semicolon!!!! Btw I’ve learned VBA thanks to you!! You are a legend! 🏆🏆🏆
@@GuilhermeNassifBarbosa Excellent Gill! Happy to hear that you got it working!
Dear wise owl, How do I get CMPAIR data from two closed Excel file if that found same entries, write it to the currently open file using VBA?
Hi! You could use an inner join to return all the rows where the joined fields have a matching value. You might find the videos on joins in this playlist useful ua-cam.com/play/PLNIs-AWhQzckUd5i0E1xeSFeBAonYIurK.html
I hope it helps!
Also, it's wonderful how fast ADO reads huge datasets.🤔
Agreed John!
@@WiseOwlTutorials Hm... i'm a bit upset... At work i have many files with rows number over 200k (base files). And to get simple description of material with ID it's quite slow. Usually at the moment i need to get descriptions about hundred ID's. It will take eternity for the beginning, when i need to make further analysis with data i've got... :(
And because of over 200k strings command rs.Open just hangs a bit while it will find pointed value. :/
I've "solved" the problem with saving book in xlsb. Now it takes in twice less than with xlsx.
But after restarting excel, it works more worse than it was before. :)
@@Rice0987 It sounds like you've been busy John!
How i press Ctrl+F2 key using VBA Sendkeys for editing a value in a cell. What is the syntax for pressing Ctrl+F2 key
docs.microsoft.com/en-us/office/vba/api/excel.application.sendkeys
I encountered another problem and would appreciate your wise (owl) comment: I tried to read data from specific columns in the source file. Data in the source file is stores in a table that DOES NOT starts at A1... I’m getting an error when I try to read the data (the arrow is in the rs.open line of code). Is there any way to bypass this problem? Thank you in advance for any comment.
Hi Tal! I'd take a look at the first video in this playlist ua-cam.com/play/PLNIs-AWhQzckUd5i0E1xeSFeBAonYIurK.html particularly the part which explains how to select from a range. I hope it helps!
Hi I understood how to get the data from a closed workbook but can you tell me how to delete the data and update data using record set in a closed workbook as we are not allowed to use ms access database hence I am using as a database file.... Please help
Hi Safeer! You can use an UPDATE statement to modify existing data - we'll have a video on that topic later in our playlist on SQL for Excel Files. Unfortunately, the DELETE statement isn't supported when using the ACE OLEDB provider to connect to an Excel workbook. The best option appears to be using the UPDATE statement to change all the values of a record to null. You'll then need to run some sort of clean-up periodically to delete the empty rows from the workbook, but you'll need to open the file to do this.
I hope that helps!
I find this works on some files but not others. The query doesn't seem to find anything on some files yet does on others (all exactly the same
format). VBA does not report any errors.
Hi Keith, when you say it doesn't find anything do you mean it doesn't return any rows or it doesn't even find any fields? If you step through the code and use the Locals window when the recordset has been populated, how many Fields does it contain?
If it's finding fields but no rows have a quick check of any criteria you've added to the query and make sure that rows in the source file match the criteria.
I hope it helps!
@@WiseOwlTutorials well, I've had a few days to look at this. I have a folder with 9 .xlsx files of various row numbers (90 to 5000+) all containing 12 columns of data. when I loop through the folder to add the remaining 8 files to the first file only 4 files return any data. As you suggested , I have checked via the locals Window and the recordset in four of the files contain 12 fields but the other four recordsets show only one field and the recordset is not populated. at the end of the process I should have a file with 22000+ rows but it only contains 8500 or so.
@@keithjacks915 Hi Keith! That's really strange if those four files have exactly the same format as the other four. I'm not sure what to suggest at this point.
@@WiseOwlTutorials I think it must be the files, I've just changed to Office 365 and the files were sent to me via Google. I have other folders which give me the same problem and I find 365's security settings a nightmare.
@@keithjacks915 Hope you find a solution Keith, I'd love to hear about it if you do!
I followed your code but ran into an error message when the source file is saved as "Read Only". Is there a way to get around this?
Hi! I don't know of a way around this, I'm sorry!
Great video, thank you so much!
My coworker and I are trying to implement and running into a "Run-time error -2147217865 (80040e37)" when hitting the rs.Open line. Not sure how to adjust around that, do you have any advice?
Hi! Do you have an error message to go along with that?
@@WiseOwlTutorials oops! Yes- Run-time error ,-2147217865 (80040e37)': Automation error
That's all it gave
@@trispiral Not Microsoft's most helpful error message! What does the rest of your code look like? Particularly the part which sets the source for the recordset.
@WiseOwlTutorials Hi there, Coworker here. We've both tried multiple times to reply with the code however the replies never seem to stay up. Is there a different way we can get it to you?
@@christinaperry3124 Hi Christina, sorry that UA-cam's comment system isn't being very helpful! You can try posting a comment on the relevant video on the Wise Owl website www.wiseowl.co.uk/vba-macros/videos/vba-ado-net-recordsets/copy-from-closed-workbook/
Hi Andrew, I came across a situation where I need to get data from a closed file. However, the closed file has an Excel table in it call "tblCorporate_Tax_Rates". I would like to get only 2 columns of that table i.e., the first column in full and then column "United States" in full. I have your full code just need to configure it for Excel table name instead of range. Would appreciate your help
Hi Muhammad! Sadly, this technique doesn't support Excel table names. The first video in this playlist shows various ways to reference Excel ranges in your SELECT statements, I hope it helps!
ua-cam.com/play/PLNIs-AWhQzckUd5i0E1xeSFeBAonYIurK.html
Thanks Andrew for confirming
Hi Andrew, Thanks for this awesome video. I was trying to replicate your did but I am getting "Could not find installable ISAM" error. It occurs when I am trying to check my connection string. Can you please tell what could be the issue behind this error ?
I got the same error while connecting to SQL server using ADOB.
Hi Ravi, do you have the correct driver installed? This link has 32bit and 64bit drivers available, just pick the one which corresponds to your version of Office www.microsoft.com/en-us/download/details.aspx?id=54920
For SQL Server you'll need this driver docs.microsoft.com/en-us/sql/connect/oledb/oledb-driver-for-sql-server?view=sql-server-ver15
We have a separate video which covers connections to SQL Server ua-cam.com/video/OWKae1pTTnE/v-deo.html
I hope it helps!
Very helpful! Is it possible to do the same to get data from csv files?
Hi Dan, absolutely! It's all about the connection string of course - www.connectionstrings.com/textfile/
For text files, the connection string points to the folder containing the files rather than a specific file. The recordset source treats a text file as a table for the SELECT statement. Here's a basic demo:
Sub ConnectToCSV()
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Sheet1.Cells.Clear
Set cn = New ADODB.Connection
cn.ConnectionString = _
"Driver={Microsoft Text Driver (*.txt; *.csv)};" & _
"Dbq=" & ThisWorkbook.Path & "\My Files\;" & _
"Extensions=asc,csv,tab,txt;"
cn.Open
Set rs = New ADODB.Recordset
rs.ActiveConnection = cn
rs.Source = "SELECT * FROM [ListOfFilms.csv]"
rs.Open
Sheet1.Range("A1").CopyFromRecordset rs
rs.Close
cn.Close
End Sub
Hope it helps!
@@WiseOwlTutorials That's great! Thank you! Is there any way of making numbers and dates format correctly doing it this way instead if just being formatted as text?
@@danforster1707 Hi Dan! You can apply any formatting you need after getting the data into the cells. The NumberFormat property of a Range is probably the best option docs.microsoft.com/en-us/office/vba/api/excel.range.numberformat
I hope that helps!
Thanks for your tutorials WiseOwl!
Can we dynamically change the path of the data source, perhaps add a wildcard or some inputbox?
Saw some interesting code to possibly address this but not sure if this would work:
Provider=Microsoft.ACE.OLEDB.12.0;
Data Source=" + @[User::InputFolder] + "\\"+ @[User::FileName] +";
Extended Properties=\"EXCEL 12.0 XML;HDR=YES\";
Hi Alvin! Sure, the connection string is just a string, you can concatenate it from as many different strings as you like. If you want the user to pick a file I'd recommend using a File Dialog, here's a video ua-cam.com/video/6ZIFNAV1rOQ/v-deo.html
Incidentally, the code you've posted looks like it's from SSIS, not VBA. The principle of concatenating strings is the same but VBA uses different operators and uses different syntax to refer to variables.
I hope it helps!
@@WiseOwlTutorials Perfect! Really appreciate your response. Also, thank you for making these videos as it certainly adding value to a lot of people who wants to learn. Kudos!
@@leciasalvin No problem Alvin, I hope you come up with a solution!
Is it possible to use this technique in the environment where multiple users need to connect to same file at random times? Mostly for reading but occasionally writing.
Hi Dimitri! I haven't tried but this post suggests not www.vbforums.com/showthread.php?395528-Problem-with-multiple-users-accessing-an-excel-sheet-through-ado
@@WiseOwlTutorials Thanks. Figured it out. It works for reading data. And there's a workaround where, to make long story short, you kill & replace entire file when writing. With proper safeguards works well enough.
PS You're literally the best teacher on UA-cam, no exaggeration, I've had a lot, you're the bomb. Keep it up o/
@@DimitriBoyarski Oh that's excellent, thank you for taking the time to come back and share your knowledge, I really appreciate it!
@@WiseOwlTutorials FYI bro that connectionstrings.com site seems to be down :/
@@DimitriBoyarski Indeed, I hope that it's only temporary!
I can't manage to overcome the limitation of 255 columns to be read, actually i found some threads online talking about reading 255 column each time and then merging the data bases, but i just can't find any code or real explanation on how to do that, could you please help me? this problem is driving me crazy
My problem is that i need one specific column in a big database of 1500x5200, if the SELECT parameter could search for more than 255 columns it would be easy, but this limitation just seem to make my problem impossible to solve
Hi Alexandre! I just thought you'd like to know that I've added a members-only video which explains how to achieve what you need. Here's the link in case you find it useful ua-cam.com/video/c516t2HVH2A/v-deo.html
Hi, when I am trying to run this program on my excel I get this error excel stopped responding.. Do you know what will be the reason I get this error
Hi Safeer, I'm sorry I don't know why Excel would stop responding. There are lots of reasons that the code might not work: not having the correct driver installed, not having the source file stored in the specified folder, etc. but all of these would display a specific error message.
Hi, I don't find the connectionstring for a .CSV excel worksheet. Could you help me please?
Hi Ezra, you might find this video helpful ua-cam.com/video/V7SslIs7HAI/v-deo.html
@@WiseOwlTutorials thanks !!
@@ezraosmarflores2938 No problem!
Is there a similar method for extracting from closed .xlsb workbooks? This doesn't seem to work for xlsb types
Are you using the correct connection string for xlsb files? It's not the same as for xlsx.
@@WiseOwlTutorials the connection string I'm using for Excel 2016 .xlsb is ...
cn.Provider = "Microsoft.ACE.OLEDB.12.0"
can.ConnectionString = "Data Source=" & strFolder & strWorkbook & ";Extended Properties='Excel 12.0;HDR=YES';"
@@MatrixRKL I don't know what's in your folder and workbook variables but have you made sure there's a \ character separating the two? The connection string looks fine otherwise - this example works correctly
MovieFilePath = ThisWorkbook.Path & "\Movies.xlsb"
Set cn = New ADODB.Connection
cn.ConnectionString = _
"Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=" & MovieFilePath & ";" & _
"Extended Properties='Excel 12.0;HDR=YES';"
Is this faster than powerquery?
Could you please explain ..how to get data from a file save on sharepoint folder
Hi Anil, I'm really sorry but we don't use SharePoint here at Wise Owl so I have no way to show this.
Hi Andrew. What happens if your closed worksheet isn't set up like an SQL database table and you want to retrieve data from a specific cell that is always next to a cell with the text "Bank Statement"? I know in VBA, you would search for that text and then .offset to get to the data, but for closed workbooks, are we restricted to using SQL to get data? In that case, can this still work?
Hi Roger! You need to use SQL but the worksheet doesn't necessarily need to be structured as a table. Look at the sections in this video which show how to refer to a specific range of cells and how to refer to columns without using column headers. You can add criteria to the query to look for the text 'Bank statement' in a specific column and return the value from the adjacent column using the [F1] [F2] style of referencing columns.
Good luck!
@@WiseOwlTutorials Gotcha, I see how it can work but it sounds like ADODB connection was meant for actual databases which makes sense in order to automate working with large amounts of data downloaded to Excel. Still, this ability to use this connection opens up a lot doors. Just need to brush up on SQL. Thanks!
@@rogerh2694 Agreed, it's certainly easier to work with well-organised tables! We have a playlist on using SQL for Excel in VBA which you might find helpful ua-cam.com/play/PLNIs-AWhQzckUd5i0E1xeSFeBAonYIurK.html
Enjoy!
could not find installable ISAM
This error occurs
Dear wise owl, your videos are extremely helpful.
The functions of this video work without problems applying it from excel.
However, I am trying to make use of the same code from an old program in Visual Basic 6.0 and it gives me a problem.
EVERYTHING WORKS PERFECTLY ONCE: the ADODB.Conecction is established, it performs the SQL query, it returns the expected values, but after that nothing of the program can be used, it gives an error that closes the program without being able to catch the error .
I have tried with different versions of excel setting the ConnectionString properly, but still the same problem.
Might you help me?
Thanks a lot.
Hi Javi! I'm sorry but I think that's beyond my skill to solve! I'd recommend posting a question on StackOverflow. I hope you find a solution!
@@WiseOwlTutorials Thank you very much for your answer
What if you don't want to select everything, maybe you just want a specific range. And what if that range has blank rows?
Hi Robert! You can add cell references after the worksheet name like so [Sheet1$A1:G100]
You can return blank rows in the results or remove them by applying criteria to any of the columns:
SELECT * FROM [Sheet1$A1:G100] WHERE [ColumnName] IS NOT NULL
You might find this playlist useful, we'll be adding more videos to it this week ua-cam.com/play/PLNIs-AWhQzckUd5i0E1xeSFeBAonYIurK.html
@@WiseOwlTutorials That's amazing. I will give this a try in my solution. Very much appreciated. Thank you.
@@robertw236 No worries Robert, I hope it works for you!
Hi Andrew, "SELECT * FROM [Sheet1$]"...is there any way to replace [Sheet1$] with a variable? I have to loop through all Report Filter Pages (around 90 reports) of a Pivot Table and update each Tab to its respective file almost daily. Now using Open then Close file method...Open [A] file, copy data from Pivot Tab[A], then after little touch up, close [A] file; then Open [B]... If there is any old episode that mentioning this technique please feel free to point me to, no need to type everything here. Thanks for your kind instruction in advance. J
Hi J! Yes, you can use a string variable to specify the name of the worksheet. This video ua-cam.com/video/5F8q7Z9kWGw/v-deo.html uses the technique in a different way to the question you're asking but might be enough to point you in the right direction.
I hope it helps!
@@WiseOwlTutorialsIt does, many thanks.
@@kojosephine6255 You're very welcome!
@@WiseOwlTutorials Hi Andrew! Great video 👍 What if I don’t know the name of the sheet but I do know that the closed workbook has just one single sheet or the data I want to copy is in the first sheet? Is there a simple way to point to this sheet or has to passed always the string of the sheet in the SELECT statement?
@@pacoperez2780 Thanks Paco! I think you might find this video useful ua-cam.com/video/5F8q7Z9kWGw/v-deo.html It shows how to discover the names of the worksheets in the closed workbook and loop through them. I hope it helps!
Hello Sir, can you make video on how to edit excel file without opening it?
Hi Nur, you might find the videos at the end of this playlist useful ua-cam.com/play/PLNIs-AWhQzckUd5i0E1xeSFeBAonYIurK.html
I hope it helps!
Have a problem, the file that I am trying to query is protected, is that the reason why I get the message "Could not decrypt file". The range I am trying to query is not protected. Can someone help me with this?
Hi Andrew, as far as I know you can't pass a password to the connection string for ACE.OLEDB. There's a long discussion which includes a workaround for this issue here but I haven't tried it myself
www.vbforums.com/showthread.php?842899-RESOLVED-How-to-get-data-from-a-password-protected-closed-excel-workbook
I hope it helps!
@@WiseOwlTutorials You went to the trouble to reply to me, I did not expect this at all. What a great guy. I will read that work around, thank you so much for this. I continue to watch your excellently presented videos, unbelievably clear.
@@andrewwest5344 I try my best to reply to all the sensible questions! I'm happy that you're enjoying the videos Andrew, thank you for watching!
Thanks a lot sir,
Need help?
Want to set password in access database using excel vba
I have created the database and table using vba but can't set the password
Can you help me on this
Using office 365
Sorry I don't know the answer to that question.
So, Question. How would I do the opposite and instead of getting data FROM a closed Excel File, How would I get data INTO a closed Excel file using ADO?
Hi Robert! You might find videos 58.28 and 29 in this playlist useful ua-cam.com/play/PLNIs-AWhQzckUd5i0E1xeSFeBAonYIurK.html
If you post here VBA code would be helpful...
How to add two cell amount in single cell with same reference Id in vba.
Hi Rohit! Can you explain more what you mean by "same reference Id"? Is this the name of a column in your data, or a cell reference or a range name or something else entirely?
@@WiseOwlTutorials reference Id is a column name.example coulmn A A1 $20 and A2 $40 and coulmn B B1 reference Id 23456789 , B2 reference Id 23456789. So result should be in A1 amount $60 with B1 23456789.
@@rohit_pandit33 OK I understand, thank you for the clarification! I'll add this to my ToDo list, thank you for the question!
@@rohit_pandit33 Not sure if that's helpful, but the following should give you a unique list of reference ids with their summed values:
SELECT SUM(column_A_name), [reference id] FROM [Sheet1$] GROUP BY [reference id]
@@marcchuah7535 thanks for the help... But i want do it in VBA.
How to make a backtesting Simulation in excel from data
I just had to look up what a backtesting simulation is! I don't think that I know enough about the subject to help with this one, sorry!
@@WiseOwlTutorials please do look after it
👏👏👏