One Star

Refresh/update file before loading

Hi,
I have an excel-file which compares a specified date vs date of today; that means, the difference is changing at least every day. Excel is calculating or updating this formula only in the moment, when you are going to open that file.
My task is to load that excel-file with "tFileInputExcel". But you get only the last stored status of that file and not the actual calculated difference of the both dates.
Is there any function, I can let calculate excel?
My idea is: function "open excel-file" -> "save excel-file" -> "tFileInputExcel" -> ?. So I am sure that I always load an actual file.
Do you have any idea how I can realize the instructions "open file" and "save file"?
Thanks for help
3 REPLIES
Moderator

Re: Refresh/update file before loading

Hi,
I have an excel-file which compares a specified date vs date of today; that means, the difference is changing at least every day. Excel is calculating or updating this formula only in the moment, when you are going to open that file.
My task is to load that excel-file with "tFileInputExcel". But you get only the last stored status of that file and not the actual calculated difference of the both dates.

What's the expected result, the result of comparison(compares a specified date vs date of today)? Could you please elaborate your case with an example with input and expected output values?
Best regards
Sabrina
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
One Star

Re: Refresh/update file before loading

Hi,
yes, correct. In the excel file I have one column with a specified date and a second column with "specified date - datetoday()". This difference will only be calculated, if the excel file is opened. So I want to create a procedure "open excel file -> save -> close -> load data (tFileInputExcel)". The reason to do this is that I want to have every day the actual remaining days, but the excel will not be opened every day. The actual remaining days will be used by another procedure.
But I do not found any command for "open - save - close".
Best regards
Stefan
Seventeen Stars

Re: Refresh/update file before loading

What do you want cannot be delivered by the help with the build in Excel components. The problem is, you expect the behavior from Excel: recalculate values after opening - in the worst cases with the help of macros.
The Excel components from Talend Exchange tFileExcelWorkbookSave (save also close the file) has an option to evaluate all formulas but fails in case of macros.
You could open a file with tFileExcelWorkbookOpen, do anything with the content (e.g. write formulas in cells) and save it under a different name, different place and evaluate the formulas before.
Take a look at the components tFileExcel* in Talend Exchange.