Automate Excel Spreadsheet with Python

[@surat-dt9sr]

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

[@pacerthanyalak2251]

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

[@pimalanda7893]

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.

[@krasimirangelov8319]

Thank you. It has been of great help.

[@jiejenn]

Glad the video helped.

[@ComputerScienceSimplified]

Awesome video, keep up the amazing work! :)

[@igormaricic7093]

thank you

[@vectortonto1]

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

[@krishna838]

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]

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

[@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 :)

[@marvynrodriguez6844]

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]

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

[@abdulaimohamed8281]

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]

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

[@leonardrutaquio396]

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

[@jiejenn]

Can you be more specific?

[@zaferaslan912]

Thanks

[@jiejenn]

Glad the video helped.

[@rauldempaire5330]

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

[@jiejenn]

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]

@@jiejenn Merci beaucoup!

[@TheNeilsolaris]

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

[@jiejenn]

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

[@mypalsdontcallme]

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?

[@tkprotich]

Yes

[@wolesam2445]

How can I open another worksheet.

[@jiejenn]

use workbook.add to add a new worksheet.

[@umakanthsahu8122]

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

[@jiejenn]

wb.Activate

[@umakanthsahu8122]

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

[@umakanthsahu8122]

@@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]

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

[@umakanthsahu8122]

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

[@soniathomase7757]

Could you pls tell how do you add custom calculation on pivot table via win32com? i mean the usage of win32com.client.constants.xlPercentOfParentRow?

[@jiejenn]

Your question is quite specific, it is difficult to explain in a few words. I would recommend you post your question on Stack Overflow for answers.