Excel import with linked files

1

Hi,

We have had some issues while processing excel files coming from business users. The often use linked files. For example: file A gets a cell value from file B using a link.

I have noticed that when I change the linked cell in file B, it appears to be changed in file A when I reopen file A in Excel. However, when I import the file in EasyMorph, it still shows the older value prior to the updated value. I think it is a problem in excel and I suggest that the linked cell value in file A is not persisted until file A is resaved.
When I resave file A, the right data is ingested in EasyMorph.

This poses a problem when processing the data when the business users do not touch file A while there are updates in file B.

@dgudkov: Are the developers aware of this particual scneario? Is there a way to change the import from excel action so that it first refreshes and persists the data in a file so that we are sure that we are gettting the right data ?
We have solved it for now using a powershell script to open and save alle the excel files before importing them. This solved the problem for now.

Thanks !
Nikolaas

2

If linked data is not found in an Excel spreadsheet then it’s not there.

The solution is to force Excel to update the spreadsheet before importing in EasyMorph. This can be done with PowerShell scripting. An example of triggering an Excel action with PowerShell see here: Running Excel Macro in EasyMorph Project. The script below would probably work (not tested):

$excel = new-object -comobject excel.application
$workbook = $excel.workbooks.open("C:\myfile.xls")
$excel.CalculateFullRebuild
$workbook.RefreshAll
$workbook.save()
$workbook.close()
$excel.quit()

Note that COM-automation required by Excel doesn’t work under the EasyMorph Server service. The solution is to use Accounts available in the Enterprise edition. Accounts allow running space tasks under a different Windows account in a separate Windows process, instead of the Server service. Configure a new account in Server and then create a space to use the new account, and run the PowerShell task in that space.

See the Server Admin Guide for more information about Accounts in EasyMorph Server.

3

Hi Dmitry,

Ok thanks. Seems rather complicated… Why exactly is that kind of COM-automation not supported on EM-server ? Can it be added to the features list so that we can do it more easily in the future ?

Thanks
Nikolaas

4

Any COM-automation doesn’t work under a Windows service. It’s a Windows limitation and one of the reasons why COM-automation is being deprecated by Microsoft. It’s not a Server limitation.

If you want to make it work in Server, try using Accounts as described above. It’s a feasible, rather easy workaround for the Windows limitation.

5

Thanks for the clarification !