0

I am attempting to automate a monthly report that I write at work. Every month, the report from the previous month is opened in Word and the charts are updated via embedded Excel spreadsheets with the new months information.

I have a vague understanding that this can be done with VBA and win32com but I work on a Mac so I would prefer to use python-docx and openpyxl but as I understand MS native embedded elements are not supported in docx yet. I instead opted to just unzip the docx and edit the Excel file directly.

The thing I can't figure out is how to adjust the size of the table that the chart is pulling information from. This is shown by the blue line in the picture.

Excel table

I've been reading as much of the openpyxl documentation as I can but I can't figure out how to do this.

Akrugerus
  • 1
  • I think you'll find the "source range" used for a chart is an attribute of the chart object (a distinct part in the .docx Zip package). If you find occurrences of "Sheet1" in the specimen XML of this doc page, you'll see some examples of how the various ranges are specified. The various SeriesXMLRewriters in this module of the `python-pptx` is an example of how this is done: https://github.com/scanny/python-pptx/blob/master/pptx/chart/xmlwriter.py#L218. Note the values (e.g. bar heights) are cached and probably not automatically refreshed. It's definitely not a trivial job. – scanny Mar 19 '20 at 19:09
  • @scanny That's a bummer. Do you think what I'm trying to do here could be easily accomplished using win32com on a Windows machine? The time it takes to make these reports doesn't really justify writing out a whole XML rewriter for Word – Akrugerus Mar 19 '20 at 19:39
  • Yes, I expect so, although I would do some quick Google research first, like maybe "VBA Word refresh chart", just to see if someone else has succeeded with what you want to do. I've run Windows in a virtual machine on my Mac in the past and was able to do a little of that sort of thing that way. Otherwise, if you have a Windows box handy that's probably a good thing to try. I suppose I'd start with trying to record a macro there and see if that worked. – scanny Mar 20 '20 at 00:19

0 Answers0