4

I'm currently working on a project that requires me to write a (seemingly endless) series of macros to reproduce pivot tables designed by one of our Analysts. The details change, but the code is largely the same from example to example.

I would like to programmaticly generate the vba code based on a handful of options and then add the macro to the given worksheet. Is it possible to create an excel macro with python using win32com or other? Is it possible to create an excel macro from another excel macro? Any other ideas?

Project Background, I have a python script that does the following:

  • pulls Google Analytic data
  • does various analysis
  • writes the results to excel
  • triggers a pre-written macro to turn the data into a beautifully formatted pivot table
  • emails it off to people who probably don't actually read it
JMax
  • 26,109
  • 12
  • 69
  • 88
DylanHarper
  • 49
  • 1
  • 3
  • related: http://stackoverflow.com/questions/1742471/how-to-use-python-win32com-to-save-as-excel-file – JMax Mar 28 '12 at 18:37
  • Rather than taking some kind of pivot configuration settings, using those to write a VBA macro and then running that macro, wouldn't it be much easier just to take the settings and apply them directly to the relevant pivot table? – Tim Williams Mar 28 '12 at 19:50
  • Tim, probably yes and thanks for the suggestion. This work will soon expand beyond the realm of pivot tables so a more general solution would be nice. Plus, I'm not sure I'm bright/patient enough to use too much COM, but I'll keep this in mind. – DylanHarper Mar 28 '12 at 19:54

1 Answers1

1

Yes you can create an Excel macro with another Excel macro. For that to work, you need to tell Excel to Trust Access to the VBA Project Object model. (The setting is found in Macro Options in the Trust Center.) I don't know if you can do it from Python, but if you can, you probably also need to tell Excel it is ok.

For ease of coding, if you are doing this in Excel, add a reference to Micorsoft Visual Basic for Applications Extensibility.

You might also want to check out MZ-Tools 3.0 I find it very helpful for adding default\common code to a project.

On the other hand, your project sounds ripe for code reuse. If the common pivot table code is in one class/module, it is really easy to copy it from one open Excel project to another. (Just click and drag in the Project Explorer window.) You can also export it out to a text file and import it back in to another project later.

mischab1
  • 1,581
  • 12
  • 17