Update: When i go into the excel document and just press save and close, the file now reads correctly. As i need this to be an automated system across many different files, I cant be doing this for every excel file these routines produce.

The nature of Excel means that a Cell's value, and its formula, are both independent properties of each other. The cell won't get a value that corresponds to the formula unless Excel first calculates it. This means that if you update Excel outside of an Excel session, generally any formula cells won't recalculate unless Excel opens it. This is by design, to keep data and formulae (and the calculation processing overheads) separate.

There possibly are ways to launch Excel outside of manually opening the application, and call the Worksheet.Calculate method with say PythonCaller.

However, it would be surprising if the calculation could not be done in FME? VLOOKUP essentially references a Lookup table and retrieves a value from it. This is the core functionality of Transformers like FeatureJoiner and InlineQuerier.

I'm struggling to do the calc in FME as the VLOOKUP references the cell directly above the target one in the formula in the same column.

The AttributeCreator has adjacent feature handling, so you can use the value of preceding or following feature(s) in an expression

This has potential but the preceding feature is needed to generate the current feature, then that current feature is needed to generate the next, and so on to the next one, which works fine for VLOOKUPS. Is the AttributeCreator able to process this?

Yes, it can use the previously generated attribute.

And does it then process sequentially moving down the rows of data?

Yes it will. You may need to use some conditional values to handle the first feature, and possibly an additional attributecreator in advance, so that you can select the previous attribute in the console before it has actually been set

I feel like i'm getting closer, the issue is this however, the process would be, use the attribute value of the cell above as the join value, which then returns a value from somewhere in the data on a FeatureJoiner - changing the cell itself which then influences the next cell, and so on

The issue isnt using adjacent feature handling, its using the adjacent feature as a join which then calculates sequentially.

I dont think i'll be able to get around the VLOOKUPs i have put into this, and i'm unfamiliar with how to achieve the worksheet.calculate method. Would you be able to provide any guidance? Thank you

If the same Field/Attribute is both the calculated value and the value to use for the Join, then the best bet is to use InlineQuerier

InlineQuerier uses the SQLite engine that supports recursive Common Table Expressions (CTEs).   What we can do is rewrite what Excel is doing, but just using the SQL syntax rather than Excel syntax.

So some sample tables:  The Fact Table

The Lookup Table

Sending both the Fact Table and Lookup Table as Inputs to InlineQuerier, and using this as the SQL Expression in InlineQuerier to return a result:

WITH RECURSIVE Vw_Data AS
(
SELECT A.rowid AS RowNumber, A.rowid+1 AS NextRowNumber, A.ValueToLookup As RawValue, A.ValueToLookup, B.ReturnedValue FROM FactTable A INNER JOIN LookupTable B ON (A.ValueToLookup=B.LookupField) WHERE A.rowid=1
UNION ALL SELECT A.rowID AS RowNumber, A.rowid+1 AS NextRowNumber, A.ValueToLookup AS RawValue, C.ReturnedValue AS ValueToLookup, B.ReturnedValue FROM FactTable A INNER JOIN Vw_Data C ON (A.rowid = C.NextRowNumber) INNER JOIN LookupTable B ON (C.ReturnedValue = B.LookupField) 
)
SELECT RowNumber,NextRowNumber,RawValue,ValueToLookup,ReturnedValue FROM Vw_Data

Does that same logical processing steps as what Excel does by iterating down each row of data, calculating a value, and then using that value as the value to join with on the next row of data

• "WITH RECURSIVE" defines a dynamic view/table.  It is a virtual table that keeps adding rows to itself until there are no more rows to add
• "SELECT .... WHERE A.rowID=1" :  This is run first and initially sets the View to a single row of data : The first row.  It retrieves the initial row's ReturnedValue "B" from the Lookup Table by joining on the Raw LookupValue of "A"
• It also calculates an integer for what the next row identifier will be as a calculated field "NextRowNumber".   This is important for the next bit.
• "UNION ALL SELECT.....ON (A.rowID=C.NextRowNumber)..."   This joins the last row that has so far been calculated in the View to the current row in the Fact Table that is being considered.   In this way we can access the previous row's value C.ReturnedValue, and use this in the JOIN  C.ReturnedValue = B.LookupField to get a LookupValue from this.
• There will only ever be up to one row in the Fact Table that has a rowID that matches the "NextRowNumber" so each recursion/iteration of the UNION ALL SELECT statement only can add one row to the end of the Dynamic View/Table.
• This keeps processing row after row until C.NextRowNumber=5.   As there are only 4 rows, then there are no more rows in the Fact Table that can join on this value and the Recursion stops since an attempt at running "UNION ALL SELECT..." returns an Empty result set.

Thank you all for your suggestions and hints.

I tried to achieve the VLOOKUP process within FME by building a custom transformer and incorporating a loop. This I couldn't get to work and ran very slow since above 100 iterations were needed.

I achieved my solution by using FME to output a .vbs file using the text writer which called each produced excel file and simulated opening and saving it. The body of the script was achieved by inserting attribute values.

I then used a SystemCaller and WorkspaceRunners to run the first routine, call the .vbs script, then run the second routine. It seems fairly efficient if a little bit convoluted!

Thanks again.