I'm working on setting up a script that reads in an Excel file. I'm merging the spreadsheet with another based on a unique column and the owner doesn't want the formulas in one column to be disturbed. They still want access to view the formula. Is it possible to write to that sheet but still preserve the formula?
Question
Is it possible to write to excel and preserve the formulas in one column that's in excel?
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.
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.
@rocky12 Yes you can preserve the formula. On your Excel Reader Parameters, select Read Formulas. Say you have an attribute "Sum", this will add a secondary attribute, Sum.formula. On your writer, ensure you include the Sum.formula & Sum on the writer feature type. This will preserve the formula.
Example workspace (FME 2020): excelformula.fmwt
@rocky12 Yes you can preserve the formula. On your Excel Reader Parameters, select Read Formulas. Say you have an attribute "Sum", this will add a secondary attribute, Sum.formula. On your writer, ensure you include the Sum.formula & Sum on the writer feature type. This will preserve the formula.
Example workspace (FME 2020): excelformula.fmwt
@markatsafe where do you see read formulas. I'm working with 2019.2, is this compatible?
+33- Influencer
@markatsafe where do you see read formulas. I'm working with 2019.2, is this compatible?
hi @rocky12, it's in the readers parameters, right under the attributes window:
@rocky12 Yes you can preserve the formula. On your Excel Reader Parameters, select Read Formulas. Say you have an attribute "Sum", this will add a secondary attribute, Sum.formula. On your writer, ensure you include the Sum.formula & Sum on the writer feature type. This will preserve the formula.
Example workspace (FME 2020): excelformula.fmwt
That works, and that is awesome. Thank you. Now, do you know how to move the formula around? I'm shifting the amount of columns that I have so now it's referencing different cells when it writes out...