I'm working on a data project integrating python in Google Colab and Excel 365 on Win 8.1. My python code collects new data updates on a regimented schedule and then exports/writes (e.g. overwrites, not appends the data) like to a report on an Excel spreadsheet.
I have no issue getting this to work going to a standalone spreadsheet.
I know I could potentially do all this in Python and not use Excel at all, but I prefer not to reinvent the wheel and not spend hours hardcoding all the formulas and links already existing in Excel.
My goal is to:
Use new data from my Colab export to populate/overwrite a data table on Sheet A in an existing Excel workbook.
Then I have a separate Sheet B in the same Excel workbook performing calculations via pre-existing links connecting to the original data table on Sheet A. I then want the links to auto update each time my python export updates the data table on the first sheet.
The issues I am running into are that if I use the df.to_excel function to export the data and even if I use the spreadsheet name parameter, the export overrides the data table and names the tab okay, but wipes out any other pre-existing sheets within the same workbook.
So I attempted a work around by exporting to an external workbook and then trying to update the links in the second workbook automatically. Problem is the links don't appear to update without the source data file and the second workbook with the links both being manually opened and then the updated file manually saved.
Does anybody know a way to use python in Google Colab to:
Overwrite a specific sheet within an Excel workbook without wiping out the other existing sheets? And then have the links on another sheet automatically update which are connected to the new data?
Auto update external links between separate Excel workbooks while the files are unopened?
Control an instance of excel that can open both files to allow the links to auto update and then save and close the files automatically?
I found a post from some years ago that identified a win32 package hat appeared to be able to control instances of excel. When I try doing a pip install in Colab I got an error that the package was unrecognized or doesn't exist.
Ideally, I would prefer not to use VB if at all possible to solve this.
Any solutions are much appreciated.
Thanks in advance.
Best answer by malaminView original
First of all, I want you a warm welcome in C2C. Then, Thank you for the question. C2C is a google customer community platform. It would be ideal if you introduce yourself to the following C2C template. It would be helpful for the community members to know about you.
What about your question:
As so far I understood your questions scenario:
You want to create a new excel sheet while new data push from python code. But the difficulty is your data is pulled sucessfully append into excel sheet but old data is wiped from the sheet.
Basically, I don’t know about your development environment setup and dependence. So, I will just focus on the programming pseudocode scenario and give you some hints so that you will be able to debug your code and write the appropriate solution on your require python xls package.
Also, you didn’t mention here which python package or library are you using in colab to read and write xls sheet.
!pip install xlrd
However, there are other package libraries that you may use. you should follow the instructions on which one is best supported in the collab platform.
I'm describing in pseudocode since class and method or function names will change depending on the Python Xls package and library.
However, you must ensure that the file name and sheets are distinct.
Also you need to consider following function issue. but i mention here based on pandas.
Click on following links that might be help you to overcome your issue.:
Otherwise, You need to clarify which python class and library are you using in your case.
Thank you very much for the warm welcome to the community. That is very kind of you. I am glad to find the community.
Also, thank you for the links. There are some very thoughtful questions in the introduction template that I will need to complete to introduce myself to the community.
Meanwhile, I really appreciate the prompt response and the excellent in-depth information. I will need to carefully review the information in your kind response and determine which would be a good route for my particular project.
To answer your questions, the package I was trying to pip install was “Win32”. This is the package that was generating the error in Colab.
To answer your second question, the main challenge I am trying to figure out is how to automatically update links in one Excel Sheet that connect to data in another Excel Sheet without having to manually open and save the sheets.
For example, Sheet A contains a data table that is frequently updated by a python data push.
Excel Sheet B contains links and performs calculations based on data in Sheet A.
I am trying to figure out how to use python to automatically update the links in Sheet B (after each data push) and make them immediately effective without having to manually open and re-save the file.
Tomorrow I will review your recommendations in detail and see if I can get one of them to work.
Will let you know if I encounter further issues.
Thank you again for the warm welcome, prompt response and excellent potential solution(s).
With Much Respect,
@Osos ! I wanted to welcome you to the community and to get to ask you to go to the C2C Lounge, to present yourself using the classic template and everything, but it looks like @malamin was superfast again! 😅
In any case, please let us know if malamin’s answer helped you, when you get around it tomorrow, as you said. And it would be great if we could get to read more about you in the Lounge! 😉