I would like to apply an Excel macro to an Excel document which I have just written using FME Desktop 2020.2. Python Interpreter used is 3.7.
This can be done using the library xlwings according to this post.
According to the FME documentation I installed xlwings in the folder:
C:\Users\<name>\Documents\FME\Plugins\Python
Next step I tried to import xlwings using Python StartUp script in FME Desktop.
import sys, six, numpy, xlwings
Unfortunately I got this error:
Python Exception <ModuleNotFoundError>: No module named 'pywintypes'
Is there an easy way to solve this? In case the answer is no, I will switch to another library which can apply a macro to Excel output.
Best answer by warrendev
Yes that is the case. The macro is stored in a separate PERSONAL.xlsb. It contains basic formatting which I want to apply to different Excel outputs in different workbenches. The formatting used is not included in the current Excel writer.
I think I'm following what you are trying to do now. Have you looked at the ExcelStyler transformer? For more functionality I have used openpyxl in a python caller transformer to modify the Excel document with additional formatting after it was created with an Excel FeatureWriter. As far as running the macro, you are going down the right path with win32com, but I have had issues with this in the past, so I went the python package openpyxl route.
Here is the openpyxl documentation if you want to take a look. It may not be your best option, but wanted to pass this along.
This post is closed to further activity.
It may be an old question, an answered question, an implemented idea, or a notification-only post.
Please check post dates before relying on any information in a question or answer.
For follow-up or related questions, please post a new question or idea.
If there is a genuine update to be made, please contact us and request that the post is reopened.
Hi @lambertus , would it be possible to have your macro stored in an Excel template that can be used during the write process in your workspace? That might be an easier option for you if it will work in your use case.
Yes that is the case. The macro is stored in a separate PERSONAL.xlsb. It contains basic formatting which I want to apply to different Excel outputs in different workbenches. The formatting used is not included in the current Excel writer.
Yes that is the case. The macro is stored in a separate PERSONAL.xlsb. It contains basic formatting which I want to apply to different Excel outputs in different workbenches. The formatting used is not included in the current Excel writer.
I think I'm following what you are trying to do now. Have you looked at the ExcelStyler transformer? For more functionality I have used openpyxl in a python caller transformer to modify the Excel document with additional formatting after it was created with an Excel FeatureWriter. As far as running the macro, you are going down the right path with win32com, but I have had issues with this in the past, so I went the python package openpyxl route.
Here is the openpyxl documentation if you want to take a look. It may not be your best option, but wanted to pass this along.
Okay! Is it possible to share these python scripts with me?
This is an example of using the python caller after the feature writer. You can go with a python shut-down script instead if you want. Very basic example that only fills in header information in the Excel document. Of course you could do all of your formatting and other manipulation that you would need here as well. In my case I am using an Excel template that contains all of my formatting for this specific report, so I just use this to fill in the header information. Not that there isn't a way to do this with pure FME, as there are many ways to go about it.
import fme import fmeobjects import openpyxl
def excel_header(feature): ''' Add the project information headers. ''' # Excel path excel_document = feature.getAttribute('_dataset')
