Could you use a Python shutdown script to open and save the XLS file?
A Python library is available at:
https://openpyxl.readthedocs.org/en/2.3.3
Could you use a Python shutdown script to open and save the XLS file?
A Python library is available at:
https://openpyxl.readthedocs.org/en/2.3.3
Thanks Erik, we had a go at this initially but failed to get it to work. I'm not sure if that was the library we used though, so we'll have a look at that one. Cheers.
Hello! Did you solved this? I have the same issue here. FME 2017.1 Thanks!
I think you'd need a shutdown python script in FME (or a SystemCaller after a feature writer) that would spin off a PowerShell script. I see some clues here https://support.jamsscheduler.com/hc/en-us/articles/206191918-How-to-Automate-the-opening-of-an-Excel-Spreadsheet-in-Powershell and more than you need here https://stackoverflow.com/questions/27798567/powershell-excel-save-and-close-after-run . If someone gets this working do let us know.
The issue is that the library we use (and I suspect all libraries) don't themselves contain an execution engine that evaluates Excel expressions. Normally it doesn't matter because as soon as someone opens the file in Excel all the values are calculated. But cases such as the one here then become an issue. The only way I can imagine you can win is to "remote control" Excel to open and save the file.
I think you'd need a shutdown python script in FME (or a SystemCaller after a feature writer) that would spin off a PowerShell script. I see some clues here https://support.jamsscheduler.com/hc/en-us/articles/206191918-How-to-Automate-the-opening-of-an-Excel-Spreadsheet-in-Powershell and more than you need here https://stackoverflow.com/questions/27798567/powershell-excel-save-and-close-after-run . If someone gets this working do let us know.
The issue is that the library we use (and I suspect all libraries) don't themselves contain an execution engine that evaluates Excel expressions. Normally it doesn't matter because as soon as someone opens the file in Excel all the values are calculated. But cases such as the one here then become an issue. The only way I can imagine you can win is to "remote control" Excel to open and save the file.
Thank you for your answer! I've decided to integrate a Python script in the FME Workspace parameters/Scripting/Startup Python Script
from win32com.client import Dispatch
xl = Dispatch('Excel.Application')
wb = xl.Workbooks.Open(r'...FILE PATH\name.xlsx...')
wb.RefreshAll()
wb.Close(True)
Then, in the Python Compatibility, I put Esri ArcGIS Desktop (Python 2.7).
On the machine running the script, I need
pywin32 (depends on which FME version you're running : 32 vs 64 bits) and I needed to modify a parameter in Excel (it has to be installed on the machine running FME of course).In Excel, go to Options/Advanced Options/General and uncheck Update automatic links at open
It works like a charm! Finally.
I think you'd need a shutdown python script in FME (or a SystemCaller after a feature writer) that would spin off a PowerShell script. I see some clues here https://support.jamsscheduler.com/hc/en-us/articles/206191918-How-to-Automate-the-opening-of-an-Excel-Spreadsheet-in-Powershell and more than you need here https://stackoverflow.com/questions/27798567/powershell-excel-save-and-close-after-run . If someone gets this working do let us know.
The issue is that the library we use (and I suspect all libraries) don't themselves contain an execution engine that evaluates Excel expressions. Normally it doesn't matter because as soon as someone opens the file in Excel all the values are calculated. But cases such as the one here then become an issue. The only way I can imagine you can win is to "remote control" Excel to open and save the file.
Oh and by the way, as nothing is simple in this world, if you want to run the FME script in a scheduled task, you'll have to create on the machine running FME, a desktop folder here (depends if you use 32 or 64 bits I suppose) : C:\\Windows\\System32\\config\\systemprofile\\desktop
I found this very strange/amazing solution on
M$ forum
Just to add another piece to this puzzle, it was recently discovered that the PowerShell script solution offered above does work wonderfully in FME Desktop, but does not transfer over to FME Server well. Running such a workspace Server-side will throw the following error:
HRESULT: 0x80080005
This Microsoft forum post explains that "This serror] usually indicates that you run Office applications on the server side or any unattended application...Microsoft does not currently recommend, and does not support, Automation of Microsoft Office applications from any unattended, non-interactive client application or component".
This second Microsoft article dives deeper into server-side use of MS Office, stating that "Office applications do not take into consideration the security problems that distributed components face. Office does not authenticate incoming requests...Office uses many client-side components...that can cache client authentication information."
Additionally, this second article mentions licensing concerns, where "Current licensing guidelines prevent Office applications from being used on a server to service client requests, unless those clients themselves have licensed copies of Office. Using server-side Automation to provide Office functionality to unlicensed workstations is not covered by the End User License Agreement (EULA)."
The first linked article above provides a suggested workaround to this issue: "...consider using Open XML SDK, see Welcome to the Open XML SDK 2.5 for Office for more information".
