Automate Excel Spreadsheet with Python

Подписаться 64 тыс.

Просмотров 57 тыс.

50% 1

In this Python tutorial, I will be covering how to automate Excel spreadsheets using win32com (pywin32) library in Python.
Things I will be covering in this tutorial:
1. Create a new Excel spreadsheet
2. Write/Read data from an Excel spreadsheet
3. Save the Excel file
Buy Me a Coffee? Your support is much appreciated!
----------------------------------------------------------------------------------------------------------------
PayPal Me: www.paypal.me/...
Venmo: @Jie-Jenn
To install win32com library: pip install pywin32
why win32com?
As a Windows user myself, the win32com library provides me with the flexibility to communicate multiple applications and integrate them with ease. Especially, I can control the applications while the applications are open. The other advantage win32com has over other Excel Python libraries is that, using win32com to link to an COM object application, I will have access to the entire object models vs other Excel Python libraries will only have a limited features available.
win32com (pywin32)
A Python extension for Microsoft Windows provides access to the Win32 API. Using Win32 API, we will be able to create and use COM objects to automate applications such as Microsoft Excel, Outlook, Word, Access, PowerPoint, or event Photoshop. On top that, we will be able to automate a wide range of features on Windows.
Support my channel so I can continue making free contents
---------------------------------------------------------------------------------------------------------------
Patreon: / jiejenn
By shopping on Amazon → amzn.to/2JkGeMD
More tutorial videos on my website → LearnDataAnaly...
Business Inquiring: RU-vid@LearnDataAnalysis.org
Tags:
#Excel #Python #win32com

Опубликовано:

2 окт 2024

Ссылка:

Скачать:

Готовим ссылку...

Добавить в:

Мой плейлист

Посмотреть позже

Комментарии : 42

@vectortonto1 2 года назад

You should add chapters to your video. Makes it more useful. Thanks

@pacerthanyalak2251 3 года назад

Thank you for preparing the awesome video. This video is very easy to understand. I have the motivation to continue learning python.

@surat-dt9sr 3 года назад

thanks Jie, this simple to understand that it is possible modify data in excel with Pyton , no need VBA

@rayo2436 Год назад

Nice, What is the difference with xlWings? which is better? Tranks!!!

@jiejenn Год назад

xlwings is just a wrapper around pywin32 which offers a simpler usage to work some of the Excel models.

@rayo2436 Год назад

@@jiejenn Thanks bro :)

@krishna838 2 года назад

Hi Jie, Can you tell if pywin32 can help with the below use case? 1.I want to import Excel and read as Dataframe 2.Then do some Spacy based text analysis. I will also use other packages. 3.Finally store the results (scores) in New columns 4.Export the Excel

@jiejenn 2 года назад

Pretty sure it is possible. Essentially you are just referencing Excel application in Python, it doesn't interpret other things you do.

@abdulaimohamed8281 2 года назад

Great video! How do I dynamically copy the range from A1 to E5 without specifying E5? like in VBA you could start from A1 and do xldown, xlright...

@jiejenn 2 года назад

In Python, you would fetch the last row number as LastRow = wsEntry.Cells(wsEntry.Rows.Count, "A").End(-4162).row where -4162 represents xlUp numerate value. docs.microsoft.com/en-us/office/vba/api/excel.xldirection

@pimalanda7893 3 года назад

Thank you thank you. Now I know how to use VBA Object modele in my Python code. I search this many years. I didnt think just to take from Microsoft doc. So simple.

@leonardrutaquio396 2 года назад

can i also put formula and return the value instead of none?

@jiejenn 2 года назад

Can you be more specific?

@TheNeilsolaris 2 года назад

Will this work with Excel 2007, do you know? Thanks.

@jiejenn 2 года назад

Because you are directly accessing Excel application, the method will work with Excel 2003 and later.

@igormaricic7093 2 года назад

thank you

@ComputerScienceSimplified 3 года назад

Awesome video, keep up the amazing work! :)

@marvynrodriguez6844 3 года назад

Thanks for this video! Could you advise which would be the best way to copy data between different books? My main goal would be to create a database by getting data from various excel books in just one. Thanks in advance!

@jiejenn 3 года назад

If you are just importing data from Excel files to a database, I would go with pandas.

@umakanthsahu8122 3 года назад

How to bring it to foreground instead just opening in the background... Please Help....

@jiejenn 3 года назад

wb.Activate

@umakanthsahu8122 3 года назад

@@jiejenn Actually I'm using a Word Document so when I wrote word.Activate its giving error

@umakanthsahu8122 3 года назад

@@jiejenn Here is my code... word = win32.Dispatch('Word.Application') print(type(word)) word.Visible = 1 print("opened") time.sleep(10) word.Quit() print("quitted") Can you help how to make it to foreground

@jiejenn 3 года назад

You didn't even create the word document object. Word Application is not a Word file.

@umakanthsahu8122 3 года назад

@@jiejenn yes sir it's enough if my word application opens in foreground but its not instead opening in background

@wolesam2445 3 года назад

How can I open another worksheet.

@jiejenn 3 года назад

use workbook.add to add a new worksheet.

@rauldempaire5330 3 года назад

Could this work with LibreOffice Calc as well? Thank you for your reply...

@jiejenn 3 года назад

I don't use LibreOffice, but looks like you can run Python script directly from the app. help.libreoffice.org/6.3/en-US/text/sbasic/python/main0000.html

@rauldempaire5330 3 года назад

@@jiejenn Merci beaucoup!

@mypalsdontcallme 3 года назад

At work I have all these different excel spreadsheets I need scan through for information. I want to create a prompt that ask for the name of the user and scans through these excel spreadsheets n then shows me predetermined useful information I would need.. is this possible with python?