1

My problem is rather simple : I have an Excel Sheet that does calculations and creates a graph based on the values of two cells in the sheet. I also have two lists of inputs in text files. I would like to loop through those text files, add the values to the excel sheet, refresh the sheet, and print the resulting graph to a pdf file or an excel file named something like 'input1 - input2.xlsx'.

My programming knowledge is limited, I am decent with Python and have looked into python libraries that work with excel such as openpyxl, however most of those don't seem to work for me for various reasons. Openpyxl deletes the graphs when opening an excel file; XlsxWriter can only write files, not read from them; and xlwings won't work for me.

Should I use python, which I'm familiar with, or would VBA work for this kind of problem? Have any of you ever done something of the sort?

Thanks in advance

Improve this question
2
  • 1
    you might consider using Python for creating graphs, ditching Office software generally makes you life easier. matplotlib.org is a good place to start. Commented May 25, 2015 at 10:40
  • The thing is, that excel sheet is really complex, getting other pieces of data from databases and making a lot of calculations, and it works as is. I could indeed reprogram the whole thing in Python but that would be quite complex, I'd be much happier with a way to control my Excel sheet via a script. Commented May 25, 2015 at 10:47

2 Answers 2

Reset to default
1

As a more transitional approach to what m. wasowski wrote above, I'd suggest you do the following.

Install the pandas package, and see how easy it is to load a file using read_excel. Then, read 10 Minutes to Pandas, and manipulate the data.

You state that the Excel sheet is complex. In general, the more complex it is, this approach will eventually make it simpler. But you don't have to switch everything immediately. You can still do parts in Excel and parts in pandas.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

1

I think you should consider win32Com for excel operation in python instead of Openpyxl,XlsxWriter.

you can read/write excel, create chart and format excel file using win32com without any limitation.

And creating chart you can consider matplotlib, in that after creating chart you can save it in pdf file also.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.