All of your videos are unique n commendable... I am an expert in VBA but still your videos are life-savers to me...I have No words to appreciate your work...Thanks from depth of my heart.. God bless you... May God fulfill your wishes and your offsprings'...Live long n healthy. Amen
I'm mostly self taught and been doing macros in excel for about 8 years... everything in this video was new to me and all your videos have had excellent, new to me, tips and tricks. Thanks for your contributions, they are awesome!
You have became my quick go to Guy after the video "running the code 1000 times faster". In fact, I optimized my worksheets your way and they run 10000 times faster
Much more fast using WorksheetFunction.Match instead of "for cells() next" or "Range.find" . and also ARRAY instead of ranges . My workbook improoved a lot . The Use of MicroTimer helped me understand the changes , what is fast and what is slow .
WARNING - Watching Paul's videos will cause discontent with perfectly good VBA code. I have rewritten a fairly complicated sheet twice in the last week to take advantage of techniques that were new to me. My new favorite RU-vid subscription. Thanks again!
That ADO code was such a powerful piece of information and explained very succinctly. I have one burning question though that I'd be grateful to you if you could address or answer: _Is there a way to write to a closed file using ADO? How would that code be?_
Same code, but just use an "Insert into" SQL query instead of the Select statement. I use it all the time. The only piece of advice I'd give is to ensure that you always set up your data in a single sheet (try to start in A1 with your headers) and make sure it is laid out in columnar format. You're effectively turning a spreadsheet into a DB, so it is very powerful. You may also notice that when you do an Insert into on a sheet with no existing lines of data that you sometimes get data type errors as Excel doesn't know what type the data in the column should be (dates etc might cause issue). I believe it takes the type from the data in the first row of your data table (for example, if you have a cell in the first row below the header that is a date, Excel will expect you to be inserting a date). Hope this makes sense!
ADO sounds really useful and would probably speed up my code 100 times. Unfortunately I have to code VBA for Mac users and ADO is not supported on Excel VBA for Mac. The culprit is Active-X...
@@Excelmacromastery This is painfully true. While 99% of my organization is on Windows... there of course a few MAC users which make my VBA life a nightmare... even more painful is they are high level execs. =/
More great stuff, thanks Paul. While I was aware of ADO, I haven't worked with it to date. It will definitely solve some challenges I have been having. Rather than "copy/paste" the query results to another spreadsheet, could it be put directly into an array?
Hey Paul, The below line is giving me an error while trying to read from closed workbook. Conn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & "Data source=" & FileName & ";" & _ "Extended Properties=""Excel 12.0;HDR=Yes;"";" Error is "Cannot update. Database or Object is read-only".
Try to use following as connection string: Dim ConnStr as String ConnStr = "DSN=Excel Files;DBQ=" & ActiveWorkbook.FullName I implicitly assumed that the 'proper database' which ADODB understands is in your open workbook. If it is not the case, you just need to replace "ActiveWorkbook.FullName" part with the proper fullname of the workbook. Someting like: C:\Users\w8\Desktop\The Work\Excel_Query_Workbook.xlsm
Hi Paul.. another excellent video. Thanks a million for these gold tips and tricks. Question on ADO and SQL: Is it possible to design an ADO connection and SQL query that returns to a target worksheet whatever is in a defined range of the closed source workbook/worksheet? So, let's say I want to copy everything contained in the source Workbook, Sheet1, Range A1:Z100 and that range is not set up as a data table or database, it just contains text and numbers in various cells within the range and some cells are even blank. What I am after is something similar to actually opening the source and then copying and pasting whatever is the contents of A1:Z100 to the target and then closing the source. Right now, I do this with code that allows me to pick the source file with Application.GetOpenFilename and then code that does the copy from source, paste to target, close the source. If I could do the same, but skip the open/close of the source file, that would be brilliant! Any thoughts? Thanks again and Thumbs up!!
Thank you for this video. I am interested in working with closed excel files. I followed your steps for the ADO and got Run-Time error '-2147467259 (8000040005)' Failure creating file. I looked online and says that I need to unprotect the sheet but I never protected the "5 more things Data.xlsx" file. I already checked the MS ActiveX Data Object 6.1 Library. The macro file and the data file are in the same folder. Do you have any suggestions?
Hi. When you someone use the macro, he could have a problem by not having the dll i suppose ? or not exactly the last one. However it a nice trick to read data from workbook. I have already used it in a C# code, but i have never thank to use it in vba itself.
I have found these articles very interesting, thanks Paul. One question on the 5th item which was "Reading from closed workbook". When I try the exact example I get a Compile Error on the line "shResult.Cells.ClearContents" saying Variable Not Defined". Any idea why I get this error.
Awesome explanation... Previously i read ur article on the same topic which i got in mail.... But it was hard read.... But ur video explanation is absolutely interesting... Love to see more videos... I guess... U should make this channel BIG now... All thr topics which u have in website should be made as a video series.. U will have big vba fan following for sure
After viewing numerous videos from Sir Paul, I just have to state that if the high standards of the medical profession were enforced on the IT teaching profession, our technical outcomes would have a much higher success rate. Sir Paul, M.D. is our doctor of VBA. 👍
Thanks Paul for great video! Is it possiable to change the extention of excel files, on a folder, from .xls to .xlsx, without haveing to open each files on the code?
Have just checked it and you cannot change the extension only, as excel popup massage that the file is corrupted. probebely the file must be open and save with the new extention.
I just started working with VBA. I am an old school 'send to the compiler' programmer from last century. But I have some stuff I want to do in excel and watching your video's will give me a huge head start on setting up my environment and some of the concepts in this new to me environment. Liked, subscribed and saved...
Advanced Filter is faster if the data is in the current workbook. ADO doesn't require a workbook to be open so in this scenario ADO will more than likely be quicker.
I dearly would love to use these techniques, but I don't know which version of VBA for Excel Paul is using. I know that it's not the on I use: some of the code just doesn't work with my version. Knowing which version would really help, and, IMHO, is a necessary part of a tutorial.
Excellent video, thanks Paul. Love the ADO, that's going to be very useful. How about making a series like this. I have learned many new skills in your first 10 tips.
Wow! What a great tutorial! I didn't know that Watch Window was editable! I always found it frustrating that for Range objects, the Address property is not present in the Watch Window. I would copy the Range variable to the Immediate Window and query its Address property there but thanks to this video I can save that step and simply add the Address property to the variable in the Watch Window. Neat! I am familiar with using ADO to query Excel workbooks but I was operating under the assumption that the workbook being queried had to be open on the desktop first. Thanks for helping me understand that it's not necessary to open it. As you mentioned, using ADO to query a workbook on a server is especially useful since it can otherwise take some time to open and download a copy of a workbook from the file server to the local machine. Some folks think VBA is just a simple scripting language but, as you constantly demonstrate in your videos, it is a very powerful programming platform. Thank you!
Hi Paul, I must say excellent contents and great way of explanation skills.... keep sharing knowledge it will definitely help people's like me. Another thoughts is it possible to creat video on how to download attachments from perticular subject mail or perticular attachment name. Also SQL videos are most welcome. Sanket
Hi Paul, I've been using the ADO and it works great so far. I've even been able to SELECT individual records from the database by learning SQL and reading them onto my worksheet. I've just been wondering if there is any way to _(while running the _*_UseADO_*_ macro)_ place the individual row's data that I imported into cells of my choice :- eg. data from "A2" is read into "A4" data from "B2" is read into "C3" data from "C2" is read into "E10" etc. So basically, while firing the query and reading the data, I want to place the data in each column into cells of my choice. Hope it's clear enough to understand.
I've been using VBA for years and still find your videos incredibly resourceful. I appreciate your help. Please post any Patreon links so we can donate if you are accepting!
The ADO thing is going to be my flavour of the week. I had a user lock his workstation yesterday with a shared workbook open. I think I see how to make that a thing of the past. Thanks!
Very good content! Thank you very much! I made a fairly large database on Excel for recurring invoicing a couple of years ago... If only I had known this... The ADO thing sounds quite powerful... being able to run SQL queries on a Workbook means that most applications I need for business can be run on Excel... lol If you find yourself looking for a video topic... an SQL database on Excel would be EPIC
Great stuff. Never thought about using ADO to access closed workbooks. I think it is worth mentioning that adding the reference to the 6.1 library is optional - instead, late binding may be used (Set conn = CreateObject("ADODB.Connection")). There are pros and cons for both options, but I find it sometimes useful in order to mitigate compatibility issues, if it is a spreadsheet that will be run by other users.
As always your videos are AMAZING! I have two questions on this video if that's ok Q1) At time 6:15 this line of code confused me: shorders.range("h1").currentregion.offset(1).clearcontents What does the offset(1) do in relation to the currentregion? Q2) What would be the difference between rng and rng2 here: dim rng as range dim rng2 as range rng=thisworkbook.sheets("A1").currentregion rng=thisworkbook.sheets("A1").currentregion.value I'd really appreciate any help. Thank you!
Glad you like the videos. Q1) another way to write it would be dim rg as Range Set rg= shorders.range("h1").currentregion ' Get the current region range Set rg = rg.Offset(1) ' Move the range down one row to avoid deleting the header. Q2) CurrentRegion returns a range currentRegion.Value returns the array of values in the range -Paul
Used range is the range of all used cells on the worksheet. From the first used cell to the last used. CurrentRegion is all the adjacent cells with data. The region ends at a blank row or column.
@@Excelmacromastery Hmmm. That is the same reasoning that let Ron Howard commit that misquote crime on the line of the century "Houston, we've had a problem". OK, yours isn't exactly heinous, but it's there.
