Calc sheet as a data for Base Table - dynamic link

[User_eppo]

I have a data muncher which could export Excel data. There is this SystemHigh demanding data echange via Excel and they provide template file for that. It is worth nothing that fields and their formats do not pass exactly, some routine work needs to be done.
What i do is i open my muncher’s exported file, drag Sheet to the Base Table section and use data from a Query where fields are formatted/ordered in a sequence like in SH template file. This then satisfies SH demand.

In order to work on a next data exchange i would need to Delete Table from base, open a new source Excel file and drag the Sheet to the Base/Tables again.
If there was a possibility to DDE data from Excel source to the Base that would allow for to simple source file owerwrite.

Is there any? So far my G-fu fails.

Thank you.

[mariosv]

I think the mistake it’s to drag the sheet, you can create a new base file from excel file.

So keeping the name for excel file should do the job.

[User_eppo]

This approach while different yields the same static data created on the first connect. No later change in Calc sheet is reflected in the Base tables or queries. I even had switched on driver pooling for all known to the LO drivers. Thank you for looking into anyways.

[Longi]

The proposal is correct, but it seems to me that itobliges you to have only a table in your database (only data from your sheet.
you have several possibilities using macros and refreshing your database each time your sheet change.
It’s a slow procedure, but if it has not a big number of records it woks OK.
Needed to know if the origin is Excel or Calc, So, LOO version and database engine
Maybe a little example

[User_eppo]

LO 7.1.4; both files are Excel, my muncher has type of Wizard for the egress file generation yet LO does not have any problems reading/saving. As one might vision SH uses M$ latest and shiniest Excel breed. There wont be any “samples” due to data sensitivity, sry; explorations i have done use simplest LO Calc and Base files possible.
I am all my ears to how and where i would hook LO macro checking me sneakily changing data origin’s Excel file. I’ve done something similar in a pure python middleware app tho.
Not a speed addict, PC does the job faster and more accurate than a human anyways (and the data amount in a batch is quite reasonable).
It is one Table i have to deal with, however provided Excel is used as a Data Source there should not be any limits or restrictions for the macros on how many rows, columns or tables it is allowed to exchange, imho.
Maybe i should consider python instead of the LO as a workhorse to prepare csv data, then stuff it into the output xlsl template…

[parsely]

Your question only mentions the transfer of data from excel to base, do some filtering and formatting, and then back to excel. It is not possible to do the filtering and sorting in calc? optionally with a macro?
Certainly with the os module of python you can check upon opening of a LO file, Tools>customize>events>open document, whether a new Excel file has been downloaded and act subsequently.

LO + macros sounds quite a heavy load of code for the task.You might indeed consider it to do it in python all the way, I assume you are aware of the pandas library, for data handling. pandas website If conversion from excel to csv in python can not be achieved, it can be done with LO.