How to Replace VBA with Python(Step-By-Step Tutorial)
Рет қаралды 255,654
Күн бұрынHow to Automate Excel with Python
In this video, I'm going to show you how to get started using Python with Excel.
We will cover how to:
* Install Python and all the relevant libraries
* Install the IDE(code editor)
* Run a script(3 different ways and it's important to understand each)
* Read and write Excel workbooks using Python
* How to trigger Python scripts from Excel events
#ExcelPython #xlWings
SUBSCRIBE TO THE CHANNEL: bit.ly/36hpTCY
DOWNLOAD THE SOURCE CODE FOR THIS VIDEO: bit.ly/2ZX6kRM
Related Training
The Excel VBA Handbook Course(TheExcelVBAHandbook.com)
Webinar Archives - 60+ Hours of VBA training(excelmacromastery.com/excel-v...)
Free Excel VBA Resources
Excel VBA Articles (excelmacromastery.com/vba-art...)
Useful VBA Shortcut Keys
========================
Debugging:
Compile the code: Alt + D + C OR Alt + D + Enter
Run the code from the current sub: F5
Step into the code line by line: F8
Add a breakpoint to pause the code: F9(or click left margin)
Windows:
View the Immediate Window: Ctrl + G
View the Watch Window: Alt + V + H
View the Properties Window: F4
Switch between Excel and the VBA Editor: Alt + F11
View the Project Explorer Window: Ctrl + R
Writing Code:
Search keyword under cursor: Ctrl + F3
Search the word last searched for: F3
Auto complete word: Ctrl + Space
Get the definition of the item under the cursor: Shift + F2
Go to the last cursor position: Ctrl + Shift + F2
Get the current region on a worksheet: Ctrl + Shift + 8(or Ctrl + *)
To move lines of code to the right(Indent): Tab
To move lines of code to the left(Outdent): Shift + Tab
Delete a Line: Ctrl + Y(note: this clears the clipboard)
Table of Contents:
00:00 - Introduction
03:23 - Running Python code
08:05 - How to use OpenPyxl to read files
14:00 - How to use Pandas for data analysis
19:58 - How to use xlWings
24:43 - Installing the xlWings Addin
27:06 - Call Python from VBA
29:00 - Call VBA from Python
30:18 - Summary
@Excelmacromastery 2 жыл бұрын
I hope you enjoy this video. Let me know in the comments if you plan to use Python with Excel.
@anilaeek4374 2 жыл бұрын
I'm getting error will trying to import openpyxl thru vs code also tried to run thru terminal the error says "module error unable to import" and asking me to install mkl-packages for intel from git. How to fix this??
@banyagatiger 2 жыл бұрын
Your tutorials are great! Keep it up mate! Hoping to see more python in excel samples! Thanks bud!
@OssMoss1 2 жыл бұрын
Excellent tutorial - well explained, 'no fluff, no noise; honest and down-to-earth'... I was missing a few bits and it was a pain of trial n'error... your video I filled the gaps... now I'm ready to take the plunge...
@kasio24 2 жыл бұрын
thanks for this report I see you from Colombia nice work. you have the new subscriber
@ExcelInstructor Жыл бұрын
Hello I like your VBA videos, but will you make "python macro mastery" channel in which ull teach how to use python with excel / SAP / ERP systems?
@elsheep6951 2 жыл бұрын
Hi Paul, since doing your webinar VBA course (thank you it has proved useful time and again) I wandered into Python, pandas and saw that they were powerful and useful but could not for the life of me see how I could use them in the office. So glad you are still here to help. It also looks like my role playing project is getting an upgrade!
@yvonplancade725 Жыл бұрын
Big thanks! After long searches without success on the net, I arrived on your site thanks to which I finally managed to understand and implement xlwings.A great moment!!!
@rickwize1182 2 жыл бұрын
You are an excellent teacher & I learned about VBA from you. Would love to see you have a full course on Python / Excel / xlwings. Would sign up immediately!
@sindiso_ndlovu 2 жыл бұрын
This video worth all the credits I can possibly give, so much information in just 30 minutes. Wow this was an amazing tutorial trust me I've done a lot
@z.r.777 2 жыл бұрын
Opened the video, jumped to an area of interest, within 5 seconds I learned something new... Thank you.
@michaellean 2 жыл бұрын
Fantastic! Have been wanting to "jump ship" to Python - I can now steadily upgrade and use the best of both worlds! Thanks for the great and easy to follow intro
@Excelmacromastery 2 жыл бұрын
You're welcome Michael.
@Freeflier1978 2 жыл бұрын
I was in Python before I got into Excel. I'd rather just run Python as excels backend vs VBA. I write most of my excel sheets using xlxswriter Python library as well.
@KLiCuk1 2 жыл бұрын
This is great - hope there are more to come. Just a quick question.... print(sheet["A10"].Value) returned "Student 9" but this is the value in cell B10 on the example sheet. I'm assuming that wasn't the actual sheet that the Python code was pointing at ? If it was then I'm confused
@Excelmacromastery 2 жыл бұрын
You're right. Actually I inserted a column after I recorded that. It did work correctly but the ID column didn't exist when I ran it.
@KLiCuk1 2 жыл бұрын
@@Excelmacromastery Cheers - I thought so
@blazer125 2 жыл бұрын
Glad I read the comments and I'm glad you asked this question.
@DavidRejavinski Жыл бұрын
This is really a very concise and powerful mini-tutorial. Take your time and revise every detail. Thanks Paul K.
@Excelmacromastery Жыл бұрын
Very welcome
@jaimesastre6393 2 жыл бұрын
Wow, I do use python and Xls separately, sometimes with Pandas. But here the combination with other libraries open infinite possibilities. Thanks 🙏👍
@SpManos-zeke Жыл бұрын
Installation worked like a charm, thank you. Though I did have to use this method to find the XLSTART folder since I don't know my way around the VBA Editor in Excel 1-Click File > Options. 2-Click Trust Center, and then under Microsoft Office Excel Trust Center, click Trust Center Settings. 3-Click Trusted Locations, and then verify the path to the XLStart folder in the list of trusted locations.
@isadom7586 3 ай бұрын
You are a Magician! Me reading "Hello There" after you pyxld it: "Wow!" Thank you for the content in the description, I have spent 13 minutes watching this video. We're on Sunday, 6PM. Finally I found you!
@wayneedmondson1065 2 жыл бұрын
Hi Paul. Thanks for this intro to Python with EXCEL. Nice to know the options and how they work together. Thanks for sharing :)) Thumbs up!!
@surat-dt9sr 2 жыл бұрын
Thanks so much. I'm newbie . Your explanation quick but we got the point
@zetandpeligaming 2 жыл бұрын
Very useful thanks. Been using VBA in excel for a while now, and wanted to learn another language. This bridges the gap nicely and allows me to apply what I've learnt with VBA to start playing with Python a bit more :)
@mKravedi 2 жыл бұрын
Same goes for myself! Very useful video! Thanks Paul!
@MrPaunty 2 жыл бұрын
Thanks Paul, that will help me in start xl automation through python
@pravinshingadia7337 10 ай бұрын
Thank you - this is awesome. First step taken today to learn Python thanks to you
@ImtiazAhmad-hg4yk Жыл бұрын
This kind of teaching is addictive
@dwaynefong 10 ай бұрын
OMg this video is such much beter than the others. very clear and concise Such a good instructor. great work thank you!
@SaidElnaffar 2 жыл бұрын
Unique tutorial with a great contirbution!
@freeliberalminds Жыл бұрын
Great thanks for this tutorial sir Paul. This is very interesting and very informative.
@pbeeby Жыл бұрын
Cracking video. Really useful and informative. Thanks, mate. Best wishes from Melbourne australia
@arielspalter7425 Жыл бұрын
Incredible tutorial. 👍
@renatocorreaarrieche 2 жыл бұрын
Nice video, thanks, I learned a lot. Tip: maybe there is a wrong image in 11:05. You may had removed ID column in file and used an older image.
@Excelmacromastery 2 жыл бұрын
Yes, you are right.
@kennethstephani692 2 жыл бұрын
Excellent video!
@MsOfficeLearning 2 жыл бұрын
Thank you for the best tutorial sir.
@azekialbayrak 2 жыл бұрын
Thank you very much Paul.
@johnwayne8059 2 жыл бұрын
Well done, nice video Paul!👍👍👍 that's pretty helpful!🤟😎
@Excelmacromastery 2 жыл бұрын
Thanks John.
@mattkriese7170 Жыл бұрын
Excellent info!
@TheJonathanLugo 2 жыл бұрын
Genius! Thank you :)
@sanjayvaghela 2 жыл бұрын
Awesome video Paul, I am always waiting for video, "VBA Code Run 1000 Times Faster" was amazing, I implemented on my code and my macro super fast now, keep posting, appreciated.. this video motivate me to learn Python.. thanks..
@Excelmacromastery 2 жыл бұрын
Thanks Sanjay.
@OpenSesame001 2 жыл бұрын
Awesome video.. Love VBA and now I can even use VBA via Python.. thanks for insight.. also I didnt know xlsz was a zip.. nice discovery too...
@Excelmacromastery 2 жыл бұрын
Glad you liked it.
@edclarke7398 Жыл бұрын
Excellent tutorial
@micheltw 2 жыл бұрын
Great intro video for this entry of python in Excel, looking forward to get more. Can you show how to get data from a file to another file?
@ggod00 2 жыл бұрын
This is awesome. More stuff like this please.
@Excelmacromastery 2 жыл бұрын
Glad you like it.
@eprepr5914 2 жыл бұрын
Very interesting. Thanks.
@exeldom Жыл бұрын
thank you very much good sir. you have shared some valuable knowledge to the world of excel users.
@daves4026 2 жыл бұрын
Great video.
@akshayc7152 2 жыл бұрын
Thank you..the video was very informative.. looking forward for more such videos..i am a beginner in python and this can be a good starting point..thankyou once again 🙏
@Excelmacromastery 2 жыл бұрын
Glad it was helpful!
@taxlala 2 жыл бұрын
i like the vedio and knowledge most.....
2 жыл бұрын
I have to postpone the babysitting job for 30 minutes just to watch and like this lol. Awesome! Tom from Danang.
@Excelmacromastery 2 жыл бұрын
Hi Tom. Video was made in Danang😀.
@manassehkisame6286 Жыл бұрын
Splendid!
@riteshsingh118 2 жыл бұрын
Could you please share a comparision video on efficiency of these tools.
@J_Barry_S 2 жыл бұрын
Thanks for sharing
@marshallhosel1247 2 жыл бұрын
Thank you
@MrSabotage83 2 жыл бұрын
Hey Paul, Brilliant stuff. I just started to learn PY and I have a question if I may... Q1 - when you set the df to the example range (a1:c13), can you set it to named ranges ex. df = sheet.range("myNameRNG").options(pd.DataFrame).value
@Denis-pq9kq 2 жыл бұрын
Awesome video! Do you know if I can install the xlwings addin on a Mac by any chance? Is there any pip install command for that? Thank you!
@sajeersayed2008 2 жыл бұрын
Can't wait
@MrMallesh1 2 жыл бұрын
Awesome video..... how to use msgbox in python, to interact with user. error handling etc.
@Metal-BIM 2 жыл бұрын
Is is great, I will use many of this for my work
@Excelmacromastery 2 жыл бұрын
Thaks Andres.
@vikramjagtap6849 2 жыл бұрын
Wonderful
@tomoo79 2 жыл бұрын
Paul, thanks for this it is really interesting! i think the environment setup is not so straight forward, at least for me. I had to: reinstall numpy from within vscode update xlwings and conda: "conda update -c conda-forge xlwings" wk.close does not seem to work but wk.close() does seem to work initialising excel if it is not already open is also necessary xw.App (visible=True, add_book=True)
@1612ganesh 2 жыл бұрын
Excited
@MrMallesh1 2 жыл бұрын
Looking one more video on python ! no video since last one month ! desperately waiting one more video. thanks
@taarigo6119 2 жыл бұрын
Great video, Thank you. Please I tried to run my xlsm file (with goalseek function) using python and import xlwings, but I have a run time error '1004' reference isn't valid, if I run the xlsm directly without python it works. Please what is the error?
@iggymach Жыл бұрын
Excellent video, quick question if I may, I ended up watching this video as I am learning Macros for Excel but I find VBA to be archaic and old-fashion in its syntax (not to mention some limitations as not being able to resize both dimensions in a dynamic bi-dimensional array, but only the second one). Also, for me it is a non- transferable skill (where else I am going to use VBA? The question is: Can I use python (and those specific libraries) to achieve the same as I would with VBA, without the need to learn VBA syntax? or I still need to know the VBA syntax and only then do the translation into python? Thank you in advance.
@georgeiskander2458 2 жыл бұрын
Great Video ,,,, Check the pictures of the excel sheet it has ID col which is wrong
@reng7777 2 жыл бұрын
Hi i have a prolbem by importing openpyxl.. the massege says: " openpyxl is not accessed pylance, do you know what can i do from here?
@speedyg2295 9 ай бұрын
Wish you had this for MAC. Very informative but i couldn't get the runPython from VBA too work. I did however get PyCharm and VSCode to reach in take from and give back to Excel
@MGE0007 2 жыл бұрын
Ooooh! I think I will hang around and have a butchers at this..!!
@sanjayvaghela 2 жыл бұрын
Hay, one suggestion.. can you make video on VBA and selenium driver? If you can its very helpful.. Thanks in advance..
@timucintimucin4146 Жыл бұрын
For those who gets "ValueError: Cannot mask with non-boolean array containing NA / NaN values" Error. Please add "na=False" parameter to match function just like following: results = df[df['Class'].str.match('A', na=False)]
@ojieuntoo 2 жыл бұрын
Any idea how to add data to table in worksheet with python?
@vishnur9773 2 жыл бұрын
For running the macro using phyton, does the excel file need to be kept opened?
@thomasl.4081 Жыл бұрын
Best way for me (and 1:1 VBA commands) is with the Python win32 functionality
@jonsmile4279 2 жыл бұрын
QQ: at 11:09 your code shows the print “Student 9” from cell A10. Cell A10 contains “9”. Cell B10 contains “Student 9”. What am I missing?
@estebanalfaroorozco9289 2 жыл бұрын
Is possibly because his first column was interpreted by pandas as an index
@eliotharreau7627 2 жыл бұрын
Hi Paul, what is your color theme for VS code ? Thank you.
@Excelmacromastery 2 жыл бұрын
I picked the default dark one.
@markyaney9820 2 жыл бұрын
Can the examples in this video be performed in Intelli J
@munafdamani6233 2 жыл бұрын
Thank you for sharing knowledge which is rarely available in KZfaq world. Dear sir, I am using Excel in stock market data analysis. It is automated with some VBA and formulas. On daily basis, I need to download 5 sheets (CSV and excel) from one site, need to change the dates at 2-3 places and have to appy some VBA code. Can the above procedure be automated? Currently the above procedure takes 15-20 and then I have to analyse data. If it gives direct results within 5 minutes, then it will be fantastic.
@Nedim89 2 жыл бұрын
Give me your contact I can make automation?
@varunaggarwal3958 2 жыл бұрын
Did you get a reply to your question? I can't access the reply.
@munafdamani6233 2 жыл бұрын
@@varunaggarwal3958 no
@firstcommenter202 2 жыл бұрын
can python do OLE ActiveX? GUI programming, Windows API?
@bhaskarmukherjee7889 2 жыл бұрын
This is advanced... Is there any source to learn Python from very basics
@plainvanilla7901 2 жыл бұрын
When he changed the file extension to zip, I felt like I took the red pill.
@Excelmacromastery 2 жыл бұрын
😄
@phillipssky8049 2 жыл бұрын
When you do print(sheet["A10"].value) I thought you should get just number 9; how come it returned Student 9? Sorry maybe I missed something.
@tigreonice2339 6 ай бұрын
Related with selenium: I have an excel or csv file, some cells have NaN value. I want when there's no value (Nan) the driver doesn't fill in the web input(with 'Nan' or nan), just skip it to the next line code instruction.... how can I do that???
@jimmybrancho6775 Жыл бұрын
hello & thank you for a wonderful tutorial! i've been fighting with xlwings for about three hours and i can't get the xlwings ribbon to appear in excel 2013. all other parts appear to be working. i'm working from a fresh anaconda install and xlwings v. 0.27.15 which conda installed itself. inside the vba editor, i see and can check xlwings as a reference. it also appears and appears to load correctly as an excel add-in. the only thing that is going wrong is that the ribbon won't appear. is there anything to be done at this point?
@khalilothman5638 2 жыл бұрын
when I press the button it say : Error --------------------------- Traceback (most recent call last): File "", line 1, in NameError: name 'fromvba' is not defined -
@vasanthkumarkallannavar7594 4 ай бұрын
Good, Hey Excel MAcro Mastery i need command that reads the plotted charts and graphs in the sheet and save those charts to device, any support ?
@SpecArbor 2 жыл бұрын
Im conufsed. At 11:09, you asked python to return the value in cell A10, but it returned the value in cell B10??? Can someone explain?
@winstondemoura8267 2 жыл бұрын
Great video! Random question: Where are you from?
@rsdyeahh 2 жыл бұрын
Any chance to get RTD working?
@champak_chacha251 7 ай бұрын
How can I get first row number after applying filter in Excel sheet using xlwings?
@lastspring Жыл бұрын
FYI, I could only get the xlWings section to work if Excel was actually running. It didn't have to be on the file being used (blank work sheet was OK).
@SeamusHarper1234 2 жыл бұрын
This is a really great introductions into the python / XLS ecosystem. I've played a lot with integrating pandas into my workflows, but I must say in most cases it's best to keep excel and other systems separate. Excels toolkit, especially considering power query, will solve nearly all problems you encounter in the wild and the benefits of python do not justify the added hassle. For example, your XLS files will not work properly on other peoples systems. You will have to setup python on your colleagues machines, and if you ship to customers.... Maybe you can freeze your script into an exe, but as an it admin, I would instantly fire any employee who executes random exe files... On the other hand, you can prepare your data with python, save to a CSV file and pull it in with power query. That's much cleaner.
@Excelmacromastery 2 жыл бұрын
Thanks for the feedback Seamus. You raised some interesting points.
@Dr.Acula787 2 жыл бұрын
I agree. I am the admin's nightmare myself, because I like to install software on my working PC (like Python or Total Commander as a replacement for the Windows Explorer as a filemanager) and I would love to automate more stuff. But the problem is, that other people couldn't work with the files anymore (but they have to), if I exaggerate it. So I usally stuck with Excel formulas, which works in many cases, at least for the most part.
@SeamusHarper1234 2 жыл бұрын
@@Dr.Acula787 try power query ;-) It does great work in automation and it ships with excel.
@Dr.Acula787 2 жыл бұрын
@@SeamusHarper1234 Thanks, I will give it a try. 🙂
@emilymn4468 Жыл бұрын
Agreed fully. In finance & accounting teams work with huge excel files. I am yet to see any video/training on python based automation that can replace the manual/macro based processes. If anyone has such a video/training please share
@user-rr2vs8zl4u Жыл бұрын
Thank You~~As Always~~!!!
@thsetharus 2 жыл бұрын
Ser, when trying to import, I get the error "Import "openpyxl" could not be resolved from source". So what is the reason of this? may you help?
@martinholmes3305 Жыл бұрын
Wow!!!!!!!!!!!!!! I did not even know... will be working through this on my project and seeing what gives, thank you so much Maestro. Can this be done on mobile phone, maybe need to run pyhton on colab? Or can my VBA project only be converted easily into an Android app using a PC, or running the python on Colab and Google Drive, on the Chrome browser for android? It would be cool if I could scrape the data and process it, as the VBA does currently, but on android mobile phone as well.
@heyjude1971 2 жыл бұрын
Great video! Thanks so much for the info! It amazes me that only 9% of viewers upvoted it. We know the 91% who didn't upvote likely learned something. (And there are presently no down votes.) We can do better than this. If you learned something from (or enjoyed) a video, let's at least give it a click; it's effortless.
2 жыл бұрын
Please launch this course, I will sign up your python course immediately.
@muthoosmathew Жыл бұрын
Hi Paul, Newbie question. How can this be shared with other users who doesn’t have python installed? The advantage of using VBA is that distribution of the tool does not need any additional installations. Is there a workaround? Thanks in advance for your response!
@dannym.1602 Жыл бұрын
@11:01 why does 'print(sheet["A10"].value)' print "Student 9" when it's actually in B10 in the workbook? Then @11:51 'sheet["b15"] = "Hello There"' add the statement directly to b15 in the workbook?
@LifetimeMomentsVideo 2 жыл бұрын
I am having trouble understanding at the 11:00 minute mark. Shouldn't A10 retrieve '9' instead of 'student 9'?
@StuartGreenley 8 ай бұрын
How come you didn’t use the spyder development?
@kisho2679 2 жыл бұрын
what are alternatives to Anaconda?
@SaulEduardoo2 2 жыл бұрын
Hello Paul, just a minimal doubt, I have seen that the r and the f are constantly used as (r'somehting here") and (f'things here'), but, what are those stand for?
@StefanoVerugi Жыл бұрын
r for instance allows you to avoid double backslashes in your path (at least in windows) f is to create a string with {data} inside, better than "this" + "+" + variable
@tarunprakashsingh 2 жыл бұрын
How do i string or json from vba to Python for parsing and after parsing python will write the Parsed value to excel sheet. I believe parsing json is easy on python than vba.
@scotolivera8207 2 жыл бұрын
what about VSTO?
@rgarciaf071 Жыл бұрын
Dumb question, what is the performance overhead of having this communication Excel -> VBA Python especially for large datasets and analysis? while it seems very neat, I wonder if this can scale easily also debugging could become significantly more difficult, but I have very little knowledge on VBA
@jacobstewart5120 2 жыл бұрын
I like how this came up as a suggested video while I am sitting here like an idiot that just cut and pasted a column on a filtered 100,000x55 range... I knew I messed up as CTRL+ALT+V V was rolling off my fingers. lol
@dominikyszkowski7663 2 жыл бұрын
A10 Has "9" value what od printing in my opinion is b10. Im confused. Unless there was wrong snip to show outcome ?
@YDysgwrAraf 2 жыл бұрын
So, to summarise, the main advantage to using Python is access to pre-rolled analytical libraries such as Pandas. Is there anything else I'm missing? Apart from it being quite fashionable, obviously. Also, it seems that your data needs to be pretty strict in its structure, otherwise navigating it/loading it into Pandas is going to be just as difficult as writing custom VBA. As we all know from bitter experience, allowing unstructured data is both a huge advantage and a massive disadvantage to using Excel.
@techsalesforce 2 жыл бұрын
I'm also wondering about this...
@Excelmacromastery 2 жыл бұрын
Pandas works quite well with unstructured data. Other advantages of Python are the ability to manipulate files directly without Excel(or opening them) and the general syntax of the language makes it less verbose than VBA>
@sparky191 2 жыл бұрын
You still need excel for the object model in some situations in these examples. So that's the lowest common denominator in many solutions. I hoped you could get away with the limitations of the license in terms of running it say on a server without excel. But it doesn't seem possible. Main advantage seems to be if you already know python then this is more accessible. Also VBA isnt being developed so no new functionality
Sameer Gaming
Рет қаралды 3,3 МЛН