Hi Jie Jenn. This is great tutorial introduction for python xlwings. Appreciate if you can create more excel automation project please so that I can have an example to follow: I have a project as below: I need to copy and paste to ranges of cells value from different excel workbook to master workbook. This is on weekly updating for week 39 1) WB_Source_1: sheet name "raw" column B range B2:B28 copy and paste to WB_Master: sheet name 2G CSSR Column AN range AN2:AN28 2) WB_Source_1: sheet name "raw" column C range C2:C28 copy and paste to WB_Master: sheet name 2G CDR Column AN range AN2:AN28 3) WB_Source_1: sheet name "raw" column F range F2:F28 copy and paste to WB_Master: sheet name 3G CSSR Column AN range AN2:AN28 4) WB_Source_1: sheet name "raw" column G range G2:G28 copy and paste to WB_Master: sheet name 3G CDR Column AN range AN2:AN28 5) WB_Source_1: sheet name "PSR" column C range C17:C57 copy and paste to WB_Master: sheet name 2G PSR Column AN range AN2:AN28 6) WB_Source_2: sheet name "Weekly FDD" column D range D5:D26 copy and paste to WB_Master: sheet name FDD Accessability Column AM range AM2:AM28 7) WB_Source_2: sheet name "Weekly TDD" column F range F5:D26 copy and paste to WB_Master: sheet name TDD Accessability Column AM range AM2:AM28 8) WB_Source_2: sheet name "Weekly TDD" column F range F5:D26 copy and paste to WB_Master: sheet name TDD Accessability Column AM range AM2:AM28 9) WB_Source_3: sheet name "Weekly" column D range D6:D25 copy and paste to WB_Master: sheet name VoLTE Access Column AM range AM2:AM28 10) WB_Source_3: sheet name "Weekly" column D range D6:D25 copy and paste to WB_Master: sheet name VoLTE Retain Column AM range AM2:AM25 Every weekly same source to be copy and paste to the same Master file but on the succeeding weekly 40 column AN 2:AN25 and soon..... Appreciate to please create an python program on this using xl wings or openpyxl. This will help a lot in automating my weekly tedious task
Thanks. I am using Visual Studio Code (Free editor) by Microsoft. I have a video showing how to install the software ua-cam.com/video/T0O07hELcJc/v-deo.html
Hi. Thanks for sharing this video. I‘m curious: How did you set up VS Code to run the code line by line directly into the python interpreter? And with which shortcut did you start the program to run? That‘s unfortunately not shown in the video. Thanks in advance.
How to read a file using xlwings having some blank rows. After reading the excel using xlwings, only the data that exists before the blank row is displayed as output. The data that exists after the blank row is not displayed at all. Could you please help on that.
xlwings is built on top of the win32com API. Every time you access a workbook, an Excel App (instance) will be created. You can set the visibility to 'false' to now show the open workbook. Yet, if you want to read cell values without creating the Excel App, I recommend using 'openpyxl'. Hope this helps.
Thanks, Jie. Very useful! I have one question. In your opinion, which library is more "convenient, or "mature", or "powerful": xlwings OR openpyxl? Thanks again for your advise.
xlwings is based on the pywin32 library and openpxl relies on access the underlying XML files, two completely different approaches to manipulate Excel files. For Mac users, I would recommend openpyxl or xlsxwriter. For Windows users, I would recommend pywin32 for experienced developers and xlwin for inexperienced.
From their documentation, there is a password parameter when you reference the open method, but seems to be broken. I would recommend use win32com (pywin32) instead of xlwings since xlwings is based on win32com. Anyway, using xlwings, and to open a worokbook with a password, wb = xw.books.open(fullname='test.xlsx', password='abcd')
@@jiejenn Thanks Jie, I tried wb = xw.books.open(fullname='test.xlsx', password='abcd') but found argument password unexpected error. I'm going to try pywin32 to open file now, because my work related excel much, so I be dependent on xlwings
@@nhaninh5046 if you work with Excel a lot, I would recommend VBA instead of Python. Use Python if you need to integrate with 3rd party APIs or doing more complex analytics tasks.
@@nhaninh5046 if that's the case, to open a password protected Excel File, import os import win32com.client as win32 xlApp = win32.GetActiveObject('Excel.Application') xlApp.visible = True wb = xlApp.Workbooks.open(os.path.join(os.getcwd(), 'test.xlsx'), None, None, None, 'abcd')
I need some help. If someone can help me. I run a python script ,after some minutes Excel crashed with this error "we are sorry, but excel has run into an error that is preventing it from working correctly., Excel will need to be closed as a result. Would you like to repair now?" I have python 3,9 and I reinstalled office python and the libraries. I don't know where I can find information to resolve it?
Hello, Could you help me please. I did follow the same steps but i've an error when i try to to run this code : C:\Users\achraf\Downloads\Nouveau dossier\xlwings>pip install xlwings Could you plz put the folder with the xlwings script in the beginning ?
Unfortunately, I have a full-time job, so not sure if I will have time to put together a documentation. Looks like your issue is outside my knowledge, your best bet is probably ask on StackOverflow or Goggling the issue.
a great tutorial. More people should watch this.
That makes my work very easy, thank you Jie
Jie 杰, great package intro series, it will be awesome to do some automation projects like automate sending email from outlook. Thank you 3000!
Thank you for sharing the information. It is helpful.
thank you so much, it cleared so many doubts reagrding xlwings
Thanks you Jie Jenn very much. Great Video
Glad my video helped.
Nice job. Can I ask you the name of the package you suggest at min 2:06 for developers who come from a VBA background? Thanks
xlwings
Thank you very much!
xlwings is very useful
Can you tell how did you set vscode to suggest intellisense on xlwings ? I don't get suggestions from editor
Hi Jie Jenn. This is great tutorial introduction for python xlwings. Appreciate if you can create more excel automation project please so that I can have an example to follow: I have a project as below:
I need to copy and paste to ranges of cells value from different excel workbook to master workbook. This is on weekly updating for week 39
1) WB_Source_1: sheet name "raw" column B range B2:B28 copy and paste to WB_Master: sheet name 2G CSSR Column AN range AN2:AN28
2) WB_Source_1: sheet name "raw" column C range C2:C28 copy and paste to WB_Master: sheet name 2G CDR Column AN range AN2:AN28
3) WB_Source_1: sheet name "raw" column F range F2:F28 copy and paste to WB_Master: sheet name 3G CSSR Column AN range AN2:AN28
4) WB_Source_1: sheet name "raw" column G range G2:G28 copy and paste to WB_Master: sheet name 3G CDR Column AN range AN2:AN28
5) WB_Source_1: sheet name "PSR" column C range C17:C57 copy and paste to WB_Master: sheet name 2G PSR Column AN range AN2:AN28
6) WB_Source_2: sheet name "Weekly FDD" column D range D5:D26 copy and paste to WB_Master: sheet name FDD Accessability Column AM range AM2:AM28
7) WB_Source_2: sheet name "Weekly TDD" column F range F5:D26 copy and paste to WB_Master: sheet name TDD Accessability Column AM range AM2:AM28
8) WB_Source_2: sheet name "Weekly TDD" column F range F5:D26 copy and paste to WB_Master: sheet name TDD Accessability Column AM range AM2:AM28
9) WB_Source_3: sheet name "Weekly" column D range D6:D25 copy and paste to WB_Master: sheet name VoLTE Access Column AM range AM2:AM28
10) WB_Source_3: sheet name "Weekly" column D range D6:D25 copy and paste to WB_Master: sheet name VoLTE Retain Column AM range AM2:AM25
Every weekly same source to be copy and paste to the same Master file but on the succeeding weekly 40 column AN 2:AN25
and soon.....
Appreciate to please create an python program on this using xl wings or openpyxl.
This will help a lot in automating my weekly tedious task
How are you getting the syntex recommendations
Hello, Great video, What is the IDE that you are using? it looks really useful
Thanks. I am using Visual Studio Code (Free editor) by Microsoft. I have a video showing how to install the software
ua-cam.com/video/T0O07hELcJc/v-deo.html
Right on! thank you for the answer ^^
Is it possible to not open the workbook with wb.Book somehow?
Hi. Thanks for sharing this video.
I‘m curious: How did you set up VS Code to run the code line by line directly into the python interpreter?
And with which shortcut did you start the program to run?
That‘s unfortunately not shown in the video.
Thanks in advance.
By default, you can execute a line (or multiple lines) of code by selecting the lines, hold the Shift key then press Enter.
Hi, at 17:30 , what's the shotcuts made the range autofilled?
Highlight your range with the item you want to autofill, then use shortcut CTRL + D to perform autofill.
Jie Jenn You rocked me！
Glad it worked.
How to read a file using xlwings having some blank rows. After reading the excel using xlwings, only the data that exists before the blank row is displayed as output. The data that exists after the blank row is not displayed at all. Could you please help on that.
Is there a way to read cell values, without opening the Excel App?
I dont want an excel app window to open and still read data
xlwings is built on top of the win32com API. Every time you access a workbook, an Excel App (instance) will be created. You can set the visibility to 'false' to now show the open workbook. Yet, if you want to read cell values without creating the Excel App, I recommend using 'openpyxl'. Hope this helps.
Any help on the "The object invoked has disconnected from its clients2" error? Thanks!
Hello, how to use xlwings server in wsl system step by step instraction
Thanks, Jie. Very useful! I have one question.
In your opinion, which library is more "convenient, or "mature", or "powerful": xlwings OR openpyxl?
Thanks again for your advise.
xlwings is based on the pywin32 library and openpxl relies on access the underlying XML files, two completely different approaches to manipulate Excel files. For Mac users, I would recommend openpyxl or xlsxwriter. For Windows users, I would recommend pywin32 for experienced developers and xlwin for inexperienced.
Im new to python and am wondering which environment you are using. Is there a way to get Jupiter notebooks to provide a formula list as you type?
I'm using Visual Studio Code as my editor. Have never used Jupyter before, not sure I can answer your questions.
How are you running the python script while your Excel is open? I always get a Permission error.
need answer on this
which python are u using im running 3 and when i set the tab1,tab2,tab3, etc and try to enter the hello there into range not working comes back error?
I'm using Python 3.7. If you're getting the range is invalid error, check if you have any typos in your code.
@@jiejenn thanks man I figured it out it was the tab1 mine has to be sheet1 ty
Any way we can save the file, without it opening in the end? I mean is there a way?
Not sure if I understand your question.
Hi Jie, how can I open file with a password protected .xlsx file?
From their documentation, there is a password parameter when you reference the open method, but seems to be broken. I would recommend use win32com (pywin32) instead of xlwings since xlwings is based on win32com.
Anyway, using xlwings, and to open a worokbook with a password,
wb = xw.books.open(fullname='test.xlsx', password='abcd')
@@jiejenn Thanks Jie, I tried wb = xw.books.open(fullname='test.xlsx', password='abcd')
but found argument password unexpected error. I'm going to try pywin32 to open file now, because my work related excel much, so I be dependent on xlwings
@@nhaninh5046 if you work with Excel a lot, I would recommend VBA instead of Python. Use Python if you need to integrate with 3rd party APIs or doing more complex analytics tasks.
@@jiejenn Yes Jie, I worked with python parallelly VBA, my work require interacting with 3rd party APIs also
@@nhaninh5046 if that's the case, to open a password protected Excel File,
import os
import win32com.client as win32
xlApp = win32.GetActiveObject('Excel.Application')
xlApp.visible = True
wb = xlApp.Workbooks.open(os.path.join(os.getcwd(), 'test.xlsx'), None, None, None, 'abcd')
Excelente!!
I need some help. If someone can help me.
I run a python script ,after some minutes Excel crashed with this error "we are sorry, but excel has run into an error that is preventing it from working correctly., Excel will need to be closed as a result. Would you like to repair now?"
I have python 3,9 and I reinstalled office python and the libraries.
I don't know where I can find information to resolve it?
Hello,
Could you help me please.
I did follow the same steps but i've an error when i try to to run this code : C:\Users\achraf\Downloads\Nouveau dossier\xlwings>pip install xlwings
Could you plz put the folder with the xlwings script in the beginning ?
What does the error message say?
@@jiejenn He can't find the pattern, could you please send me the folder with the script
I really appreciate your help
@@jiejenn He cannot find xlwings
Could you please upload a file with all the instructions, scripts, documents....
I really appreciate your help
Unfortunately, I have a full-time job, so not sure if I will have time to put together a documentation. Looks like your issue is outside my knowledge, your best bet is probably ask on StackOverflow or Goggling the issue.
@@jiejenn Is there any requirements before i can follow your tutorial ?
Like what i need to have in my computer ?
Best regards
How to write pandas dataframe into excel of type xlsb.
Ugh I wish someone would show how to install and use Python...no guides show it..and I have never used it.