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??
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...
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!!!
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!
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.
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!
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!
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
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.
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
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
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???
@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?
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..
@ 27:48 I can see you Intellisense working, but it doesn't work for me :/ (xlwings is ticked in the References) - Figured it out, used Ctrl+Space In the video you Imported Xlwings @ 20:04, but for me it doesn't work. I get the error "Import "xlwings" could not be resolved"
I have a question. I tried runnign the line import openpyxl, and an error popped up that says "Import "openpyxl" could not be resolved from sourcePylancereportMissingModuleSource" What can I do to solve this issue? EDIT: I could move one from this issue, but now i have another one. The problem comes when trying to run the xlwings code. here is my code: import xlwings as xw wk = xw.books.open(r'C:\Users\Juan Pablo Horn\Dropbox\Excel Macro Mastery\How to Replace VBA with Python(Step-By-Step Tutorial)\marks.xlsx') sheet = wk.sheets("Sheet1") rg = sheet.range("A1:C2") print(rg.value) The error says as follows:AttributeError: 'NoneType' object has no attribute 'books'. EDIT 2: I have solved the prior issue as well. Now the issue is when i try to run the VBA Macro to Run Python From VBA. This is what the error says: --------------------------- Error --------------------------- Traceback (most recent call last): File "", line 1, in File "C:\Users\Juan Pablo Horn\Dropbox\Excel Macro Mastery\How to Replace VBA with Python(Step-By-Step Tutorial)\PythonCalledFromVBA.py", line 5, in main wb=xw.Book.caller() File "C:\Users\Juan Pablo Horn\AppData\Local\Programs\Python\Python39\lib\site-packages\xlwings\main.py", line 763, in caller raise Exception('Book.caller() must not be called directly. Call through Excel or set a mock caller ' Exception: Book.caller() must not be called directly. Call through Excel or set a mock caller first with Book.set_mock_caller(). Press Ctrl+C to copy this message to the clipboard. --------------------------- OK ---------------------------
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
Followed this tutorial fine testing code in Vscode up until using xlwings where I'm getting an error; Exception has occurred: XlwingsError x Couldn't find any active App! Am I missing something?
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 :)
when I press the button it say : Error --------------------------- Traceback (most recent call last): File "", line 1, in NameError: name 'fromvba' is not defined -
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?
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!
When i did the “import pandas as pd” it leaves a red mark under pandas and says “import pandas could not be resolved from source”??? I cant use it. Even though I downloaded anaconda where the pandas is supposed to be part of it right? Any help for a curious college student?
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?
Hey Paul @Excelmacromastery ! I have a question and I will be grateful if you will answer :) I'm working with Excel but I don't know how to use VBA, I have some programming basics, but I can barely read VBA code intuitively (maybe 5% of some easy code), now I need to automatize excel files, something like: filter by column AJ values [90%:150%) and copy all the data to the new sheet .. and so on and so on. Also I'll need some UI with options and values input.. what is the best option for me - to start learning VBA or to start learning Python? Thank You in advance!
Thank you for sharing knowledge which is rarely available in TH-cam 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.
When I push the button to run the "PythonCalledFromVBA" python script (which happens @28:25 in the video) I get this error window: --------------------------- Error --------------------------- Traceback (most recent call last): File "", line 1, in AttributeError: module 'xlwings.utils' has no attribute 'prepare_sys_path' Press Ctrl+C to copy this message to the clipboard. --------------------------- OK --------------------------- Not sure what I am missing...
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)]
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.
When I run the code at 21:06, I am getting the following message: AttributeError: 'NoneType' object has no attribute 'books' Does anyone know what this means?
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)
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
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 🙏
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?
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
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).
yes but it's difficult, get it into a chart then "take photos" - it'd be a bit of a project though, you might be able to get started by finding Ron De Buin's site though.
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.
I'm having trouble running the Openpyxl ReadRanges example. Although the openpyxl is in a subdirectory in the Anaconda Site-Packages Directory it cannot find it when I try to run that example. I get this message : "ModuleNotFoundError: No module named 'openpyxl'". Can you tell me what your directory structure should be and also where you put the marks.xslx please
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.
I try to download the source code from the description but I never got a link emailed to me, is the email link service down? I've tried for a couple of your videos but the download link is never sent.
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.
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.
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
I hope you enjoy this video. Let me know in the comments if you plan to use Python with Excel.
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??
Your tutorials are great! Keep it up mate! Hoping to see more python in excel samples! Thanks bud!
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...
thanks for this report I see you from Colombia nice work. you have the new subscriber
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?
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!!!
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!
Opened the video, jumped to an area of interest, within 5 seconds I learned something new... Thank you.
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.
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!
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!
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
You're welcome Michael.
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.
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
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.
@@Excelmacromastery Cheers - I thought so
Glad I read the comments and I'm glad you asked this question.
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
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???
@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?
Hey Paul. We am really looking forward to your take on the new Python inclusion in Excel :)
OMg this video is such much beter than the others. very clear and concise Such a good instructor. great work thank you!
This kind of teaching is addictive
Cracking video. Really useful and informative. Thanks, mate. Best wishes from Melbourne australia
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?
Is possibly because his first column was interpreted by pandas as an index
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?
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.
Yes, you are right.
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?
Thank you - this is awesome. First step taken today to learn Python thanks to you
I have to postpone the babysitting job for 30 minutes just to watch and like this lol. Awesome!
Tom from Danang.
Hi Tom. Video was made in Danang😀.
If .main is changes with fun1 will it call def fun1 from python code ? 28:38
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.
This is really a very concise and powerful mini-tutorial. Take your time and revise every detail. Thanks Paul K.
Very welcome
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..
Thanks Sanjay.
Wow, I do use python and Xls separately, sometimes with Pandas. But here the combination with other libraries open infinite possibilities.
Thanks 🙏👍
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?
@ 27:48 I can see you Intellisense working, but it doesn't work for me :/
(xlwings is ticked in the References)
- Figured it out, used Ctrl+Space
In the video you Imported Xlwings @ 20:04, but for me it doesn't work. I get the error "Import "xlwings" could not be resolved"
I have a question. I tried runnign the line import openpyxl, and an error popped up that says "Import "openpyxl" could not be resolved from sourcePylancereportMissingModuleSource"
What can I do to solve this issue?
EDIT: I could move one from this issue, but now i have another one. The problem comes when trying to run the xlwings code.
here is my code:
import xlwings as xw
wk = xw.books.open(r'C:\Users\Juan Pablo Horn\Dropbox\Excel Macro Mastery\How to Replace VBA with Python(Step-By-Step Tutorial)\marks.xlsx')
sheet = wk.sheets("Sheet1")
rg = sheet.range("A1:C2")
print(rg.value)
The error says as follows:AttributeError: 'NoneType' object has no attribute 'books'.
EDIT 2: I have solved the prior issue as well. Now the issue is when i try to run the VBA Macro to Run Python From VBA. This is what the error says:
---------------------------
Error
---------------------------
Traceback (most recent call last):
File "", line 1, in
File "C:\Users\Juan Pablo Horn\Dropbox\Excel Macro Mastery\How to Replace VBA with Python(Step-By-Step Tutorial)\PythonCalledFromVBA.py", line 5, in main
wb=xw.Book.caller()
File "C:\Users\Juan Pablo Horn\AppData\Local\Programs\Python\Python39\lib\site-packages\xlwings\main.py", line 763, in caller
raise Exception('Book.caller() must not be called directly. Call through Excel or set a mock caller '
Exception: Book.caller() must not be called directly. Call through Excel or set a mock caller first with Book.set_mock_caller().
Press Ctrl+C to copy this message to the clipboard.
---------------------------
OK
---------------------------
Thanks so much. I'm newbie . Your explanation quick but we got the point
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
thank you very much good sir. you have shared some valuable knowledge to the world of excel users.
Followed this tutorial fine testing code in Vscode up until using xlwings where I'm getting an error; Exception has occurred: XlwingsError x Couldn't find any active App! Am I missing something?
A10 Has "9" value what od printing in my opinion is b10. Im confused. Unless there was wrong snip to show outcome ?
Great thanks for this tutorial sir Paul. This is very interesting and very informative.
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 ?
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 :)
Same goes for myself! Very useful video! Thanks Paul!
How can I get first row number after applying filter in Excel sheet using xlwings?
Great video!
Random question: Where are you from?
"Poitin for excel" - Good video
when I press the button it say :
Error
---------------------------
Traceback (most recent call last):
File "", line 1, in
NameError: name 'fromvba' is not defined
-
At 16:42, the first two rows do not print out. I am getting an Empty Data Frame message. Does anyone know how to deal with this issue?
Sir I want to know if we must have Anaconda installed on our system before we can get python work this way?
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?
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!
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!
When i did the “import pandas as pd” it leaves a red mark under pandas and says “import pandas could not be resolved from source”??? I cant use it.
Even though I downloaded anaconda where the pandas is supposed to be part of it right?
Any help for a curious college student?
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?
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!!
Great Video ,,,,
Check the pictures of the excel sheet it has ID col which is wrong
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...
Glad you liked it.
can python do OLE ActiveX? GUI programming, Windows API?
At the 10:54 mark, I received the following error: ModuleNotFoundError: No module named 'openpyxl'
What does this mean?
I figures it out. I basically had to install an upgrade by running a variation of the following line of code in the command line: pip install openpyxl
Hey Paul @Excelmacromastery ! I have a question and I will be grateful if you will answer :) I'm working with Excel but I don't know how to use VBA, I have some programming basics, but I can barely read VBA code intuitively (maybe 5% of some easy code), now I need to automatize excel files, something like: filter by column AJ values [90%:150%) and copy all the data to the new sheet .. and so on and so on. Also I'll need some UI with options and values input.. what is the best option for me - to start learning VBA or to start learning Python? Thank You in advance!
You can record a vba macro
Thank you for sharing knowledge which is rarely available in TH-cam 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.
Give me your contact I can make automation?
Did you get a reply to your question? I can't access the reply.
@@varunaggarwal3958 no
Thank You~~As Always~~!!!
Well done, nice video Paul!👍👍👍 that's pretty helpful!🤟😎
Thanks John.
When I push the button to run the "PythonCalledFromVBA" python script (which happens @28:25 in the video) I get this error window:
---------------------------
Error
---------------------------
Traceback (most recent call last):
File "", line 1, in
AttributeError: module 'xlwings.utils' has no attribute 'prepare_sys_path'
Press Ctrl+C to copy this message to the clipboard.
---------------------------
OK
---------------------------
Not sure what I am missing...
Thanks Paul, that will help me in start xl automation through python
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)]
I'm getting a permissions error when I try to save the workbook with the same code we have in the first part of the video?
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.
Thank you for the best tutorial sir.
Looking one more video on python ! no video since last one month ! desperately waiting one more video. thanks
Can the examples in this video be performed in Intelli J
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?
11:11 cell A10 value is "9" from the screenshot? Cell B10 value is "Student 9"
When I run the code at 21:06, I am getting the following message: AttributeError: 'NoneType' object has no attribute 'books'
Does anyone know what this means?
I have the same error
This is advanced... Is there any source to learn Python from very basics
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)
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
Excellent info!
Incredible tutorial. 👍
This means that we could run excel macros on python?
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 🙏
Glad it was helpful!
Thank you very much Paul.
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?
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
Excellent tutorial
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).
Could you please share a comparision video on efficiency of these tools.
Sir
Is there any way to convert multiple pdf files (a folder contains) in to TIFF using VBA?
NO, but you can use PDF software to merge sheets into one pdf then save as .TIFF
You could try to merge it with Python (through VBA) and then save it as TIFF
yes but it's difficult, get it into a chart then "take photos" - it'd be a bit of a project though, you might be able to get started by finding Ron De Buin's site though.
Awesome. Many possibilities : )
Unique tutorial with a great contirbution!
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.
Awesome video..... how to use msgbox in python, to interact with user. error handling etc.
I'm having trouble running the Openpyxl ReadRanges example. Although the openpyxl is in a subdirectory in the Anaconda Site-Packages Directory it cannot find it when I try to run that example. I get this message : "ModuleNotFoundError: No module named 'openpyxl'".
Can you tell me what your directory structure should be and also where you put the marks.xslx please
OK have sorted this out, no one said you had to install openpyxl and where to put it so Python can find it.
OpenPyxl is installed as part of the Anaconda Installation.
I tried the save command on a useless .xlsm file, after executing it, file became corrupted and can't be accessed.
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.
This is awesome. More stuff like this please.
Glad you like it.
I try to download the source code from the description but I never got a link emailed to me, is the email link service down? I've tried for a couple of your videos but the download link is never sent.
Where am I supposed to save my python files to be able to run then in Excel?
Hi Paul, what is your color theme for VS code ? Thank you.
I picked the default dark one.
i like the vedio and knowledge most.....
How come you didn’t use the spyder development?
Any idea how to add data to table in worksheet with python?
Excellent video!
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.
Thanks for the feedback Seamus. You raised some interesting points.
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.
@@Dr.Acula787 try power query ;-) It does great work in automation and it ships with excel.
@@SeamusHarper1234 Thanks, I will give it a try. 🙂
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
Best way for me (and 1:1 VBA commands) is with the Python win32 functionality
help
partially initialized module 'xlwings' has no attribute 'books' (most likely due to a circular import)
I had exactly the same problem. It stated : AttributeError: 'NoneType' object has no attribute 'books'. I have not worked out why as yet.
This happens if excel is closed. You can use the line *xw.books* to Activate Excel first.