Most of that was new to me and seems very useful, thanks Paul.
@Excelmacromastery5 жыл бұрын
Thanks. Glad you like this video Rafiullah.
@greg28655 жыл бұрын
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 KZbin subscription. Thanks again!
@Excelmacromastery5 жыл бұрын
lol - You're welcome Greg. Glad to see you're finding the techniques useful.
@rayhanrana67735 жыл бұрын
Your explanation of VBA code is just outstanding.
@Excelmacromastery5 жыл бұрын
Thanks for your comment Rayhan.
@Cyb3rP0p35 жыл бұрын
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!
@Excelmacromastery5 жыл бұрын
You're welcome David.
@letmedie4you8 ай бұрын
Thanks for your videos. Everything here saves tones of time for me everyday..
@jamithireddyr5 жыл бұрын
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
@Excelmacromastery5 жыл бұрын
That's great to hear Rajasekhar!!
@rscaht5 жыл бұрын
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 .
@michaelthomas86304 жыл бұрын
Your presentation is typically flawless and very informative. Thanks for taking the time to put these invaluable lessons together.
@niyazrafatkhan83643 жыл бұрын
Your videos are like magic spells, it just works so well with so little but correct information
@Excelmacromastery3 жыл бұрын
Glad you think so Niyaz!
@olliehopnoodle46283 жыл бұрын
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...
@LosoIAm2 жыл бұрын
Sir, your tutorial on how to get data from a closed workbook is absolutely wonderful - I THANK YOU!
@dangelorrrr5 жыл бұрын
Paul, I think we need an ADO/SQL tutorial.
@adamploof35285 жыл бұрын
Agreed, would love to see a deeper on dive on that subject.
@Excelmacromastery5 жыл бұрын
That's a good idea. I'll put it in my list.
@frikduplessis38695 жыл бұрын
Paul, please do one on ADO 🙏
@HappyAnimals3D5 жыл бұрын
@@adamploof3528 Fully agree wit you Adam
@NitaFunnyVideo Жыл бұрын
Ur a true Guru sharing knowledge that may be kept confidential or hidden.
@oneswolf4 жыл бұрын
Wow. Great teaching and fantastic information!
@serdip3 жыл бұрын
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!
@arielgarcia41994 жыл бұрын
wow! you are very proficient sir. thank you so much. its very comprehensive.
@pattabhimadhavaram77254 жыл бұрын
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!
@joecross53355 жыл бұрын
Paul, you are the most advanced user of vba that I have ever learned from, and hands down the best teacher. Thanks for the videos!
@Excelmacromastery5 жыл бұрын
Thanks Joe.
@the_feature_selector8594 жыл бұрын
Definition and Last Position....F2 and Shift+F2... Amazing
@charan8885 жыл бұрын
The things that you teach are completely new and makes life easy writing code. Thanks Paul. Love it!
@Excelmacromastery5 жыл бұрын
Thanks!
@KLiCuk13 жыл бұрын
👍 Superb tips - especially the ADO connection. I'll try this with a speed test comparing opening workbooks then copying sheet data.
@niavras3 жыл бұрын
Real treasure here! Thank you so much!
@Excelmacromastery3 жыл бұрын
Glad you enjoyed it!
@HSkraekelig5 жыл бұрын
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!
@BertandRussell4 жыл бұрын
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
@Excelmacromastery4 жыл бұрын
Thanks Abid.
@martynpage17944 жыл бұрын
Been using VBA for years but you've shown me some great new tricks. Thanks Paul!
@Excelmacromastery4 жыл бұрын
Happy to help Martyn.
@randomgoose Жыл бұрын
Always enjoyed VBA, mainly in Access, but ADO really appeals. SQL in Excel!
@gerardbakker2613 жыл бұрын
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.
@houstonvanhoy77673 жыл бұрын
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. 👍
@Excelmacromastery3 жыл бұрын
Thanks Houston.
@rrrprogram86675 жыл бұрын
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
@Excelmacromastery5 жыл бұрын
Thanks for the feedback. Plenty more videos to come.
@maciejdolinski10364 жыл бұрын
Hi Paul, Thank you for the following GREAT material. You are the class for your own.
@Excelmacromastery4 жыл бұрын
Thanks Maciej
@trinhthanhlinh1505 жыл бұрын
Thank you very much Paul!! Copying data from Closed workbook is my thorny issue for a month. This video is very useful.
@Excelmacromastery5 жыл бұрын
Glad to help
@ardeshiraqayi99764 жыл бұрын
Very very very useful ... Thank you so much for sharing this ....
@carstencardoso88765 жыл бұрын
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.
@Excelmacromastery4 жыл бұрын
Thanks Carsten
@alihamiad78495 жыл бұрын
Really impressed me these techniques!. Thanks Paul
@Excelmacromastery5 жыл бұрын
You're welcome Ali
@JonathanExcels5 жыл бұрын
I have written quite a bit of code and all of the tips in this video and the last are very helpful. Thank you
@Excelmacromastery5 жыл бұрын
You're welcome Jonathan.
@alexhoch12645 жыл бұрын
Nice video. I like the last part "ADOB". Last week it was the first time that I have used it (used it for a left join).
@Excelmacromastery5 жыл бұрын
SQL is a powerrful way of dealing with queries. Definitely worth using.
@rubenpradesgrau84304 жыл бұрын
Love your work. Really useful
@pythusiast47014 жыл бұрын
Very useful tips man.... keep up the gud work.
@Excelmacromastery4 жыл бұрын
Thanks, will do!
@sksanket4 жыл бұрын
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
@danielszalok85405 жыл бұрын
Tons of new information even after 4.5 years of vba 🙂 Definition, Parent, this infobox type, and adodb...
@Excelmacromastery5 жыл бұрын
Thanks Dániel
@butzromulo66953 жыл бұрын
I appreciate all what you're doing . . . hope we can do something in return! Thank you very much!
@Excelmacromastery3 жыл бұрын
Any time!
@mtotowamungu82595 жыл бұрын
You are great. I Thank you so much for your helpful videos.
@Excelmacromastery5 жыл бұрын
Thanks:-)
@cristonsloan5 жыл бұрын
Really great, thank you, but especially thank you for that last one, which, to be fair, is not really great, but flippin' Awesome!
@Excelmacromastery5 жыл бұрын
Thanks Criston. Glad you liked it.
@aliibrahem63134 жыл бұрын
Thank you, very informative.
@nic23lond235 жыл бұрын
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
@Excelmacromastery5 жыл бұрын
Thanks Nicolas. Glad you like it.
@Rampracaash5 жыл бұрын
You are awesome..!! I have made code with different method for the same requirement, but with this my code can run a 100 times faster..
@Excelmacromastery5 жыл бұрын
Glad you like it.
@neronjp99097 ай бұрын
Amazing
@janezklun5 жыл бұрын
Excellent tutorial, thank you for sharing, Cheers
@Excelmacromastery5 жыл бұрын
You're welcome Janez
@marcelomotta14274 ай бұрын
Perfeito ❤
@pmacgowan5 жыл бұрын
Very useful
@Excelmacromastery5 жыл бұрын
Thanks Paul
@learnexcelwithfun47164 жыл бұрын
What Can I Say..It's WoW!
@AS-ym2bp5 жыл бұрын
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?_
@ricos14975 жыл бұрын
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!
@Excelmacromastery5 жыл бұрын
Thanks for the useful advice Rico
@AS-ym2bp5 жыл бұрын
@@ricos1497 Yeah, thanks!!
@PrincePedia5 жыл бұрын
Thanks Paul , awesome I wonder if you have time to make a video about difference between used range and current region , Thanks.
@Excelmacromastery5 жыл бұрын
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.
@PrincePedia5 жыл бұрын
@@Excelmacromastery Thanks Paul
@kabukijoe99 Жыл бұрын
thank you for very useful lesson
@anthonyblenk18544 жыл бұрын
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.
@Excelmacromastery4 жыл бұрын
Hi Anthony This code works with all versions of VBA from 2007 onwards. If something is not working then it has nothing to do with the version. -Paul
@karankamal3130 Жыл бұрын
ADO is what I need mostly
@frikduplessis38695 жыл бұрын
Brilliant again thank you
@Excelmacromastery5 жыл бұрын
Thanks Frik
@ThorstenStrauch5 жыл бұрын
Whow! Again :-) Thank you a lot!
@Excelmacromastery5 жыл бұрын
Thanks Thorsten.
@ventjemazzel88225 жыл бұрын
Great video; thanks!
@Excelmacromastery5 жыл бұрын
No problem Ventje.
@grahamparker77295 жыл бұрын
Another great video 👌🏻
@Excelmacromastery5 жыл бұрын
Thanks Graham
@HappyAnimals3D5 жыл бұрын
You are amazing!
@Excelmacromastery5 жыл бұрын
Thanks
@obsoquasi5 жыл бұрын
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...
@ricos14975 жыл бұрын
Have you tried using ODBC instead? Think it is supported in Excel 2016 for Mac, but I don't have a Mac so have never tested!
@Excelmacromastery5 жыл бұрын
Unfortunately some of these useful libraries are only available on Windows.
@Cyb3rP0p35 жыл бұрын
@@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. =/
@haiderbokhari14 жыл бұрын
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!
@Excelmacromastery4 жыл бұрын
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
@haiderbokhari14 жыл бұрын
@@Excelmacromastery wow that was lightning fast! Your are the MAN! Where were you when I started learning VBA???
@sjwthomas3 жыл бұрын
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.
@rrrprogram86675 жыл бұрын
Good one... Thanks for the video
@Excelmacromastery5 жыл бұрын
You're welcome.
@terrycline86895 жыл бұрын
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?
@Excelmacromastery5 жыл бұрын
Recordset GetRows. arr = rs.GetRows
@stevennye50755 жыл бұрын
very useful!!!!
@Excelmacromastery5 жыл бұрын
Thanks Steven.
@wayneedmondson10655 жыл бұрын
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!!
@Excelmacromastery5 жыл бұрын
ADO expects records to be in database form. If not then it will complain. The easiest thing to do is run a test query and see what happens.
@houstonvanhoy7767 Жыл бұрын
@@Excelmacromastery "Database form" = Excel table with no line gaps or column gaps?
@korencek3 жыл бұрын
is using ADODB faster than "Application.Workbooks.Open" when opening files? Or is it about the same?
@sourvep4 жыл бұрын
Can we create hyperlink which redirect to a specific cell of another csv file? If yes please share ur inputs
@AS-ym2bp5 жыл бұрын
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.
@charan8884 жыл бұрын
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".
@fahrigafarzade7354 жыл бұрын
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
@BenjaminHouot5 жыл бұрын
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.
@Excelmacromastery5 жыл бұрын
Which dll?
@BenjaminHouot5 жыл бұрын
We need odbc dll to read data from a closed wb.
@briandennehy63805 жыл бұрын
Amazing Paul - out of interest you do any vba classes or course in Ireland?
@Excelmacromastery5 жыл бұрын
Thanks Brian. I don't do any live VBA classes at the moment. I may do some in the future.
@briandennehy63805 жыл бұрын
@@Excelmacromastery cheers Paul. It's really hard to find any comprehensive class based courses on vba
@edwingonzalez27492 жыл бұрын
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?
@selesli18954 жыл бұрын
Hello, for the ADO, I can't get the part of the code where it said "Dim conn As New ADODB.Connection" to work. Would appreciate your answer to this.
@luiscordeiro13974 жыл бұрын
do you need to have data in a table format in order to access it via ADODB.connection?
@boredonatuesday87934 жыл бұрын
Nope, not necessary
@uniQue_XL5 жыл бұрын
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?
@Excelmacromastery5 жыл бұрын
You can use the "Name" and "Dir" functions. Name "C:\temp\data.xls" As "C:\temp\data\xlsx". Use Dir to get all the files in a folder.
@uniQue_XL5 жыл бұрын
@@Excelmacromastery Thanks Paul!👍
@uniQue_XL5 жыл бұрын
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.
@sreejithom37265 жыл бұрын
The parent name property we can apply in immediate Window also right?
@Excelmacromastery5 жыл бұрын
Yes.
@rrrprogram86675 жыл бұрын
Right away I searched if u have made any course on udemy.... Sadly there isn't
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.
@saravananbe194 жыл бұрын
How to get vba handy book sir
@Excelmacromastery4 жыл бұрын
You can purchae the Excel VBA Handbook Course from here: www.theexcelvbahandbook.com/
@srisureshm14765 жыл бұрын
Hi Can we block vba code @ particular date Example code should not work after one month
@Excelmacromastery5 жыл бұрын
Yes. Just use an If statement. However it is very is to circumvent.
@srisureshm14765 жыл бұрын
Excel Macro Mastery Thanks for u r reply. Can I have one example
@Excelmacromastery5 жыл бұрын
@@srisureshm1476 You're very welcome.
@joecross53355 жыл бұрын
If inputBoxDate > your Cutoff Date then Msgbox ("Please select a date less than (whatever).") Exit sub End if
@houstonvanhoy77673 жыл бұрын
@@Excelmacromastery Very easy? (Not very is.)
@shyamsewram18794 жыл бұрын
M
@donepearce5 жыл бұрын
5 More things I wish I *HAD KNOWN*... Just helping out.
@Excelmacromastery5 жыл бұрын
:) I know but "Knew" is a better title. I use "Had Known" throughout the video.
@donepearce5 жыл бұрын
@@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.