New Data to ADD rows to Query table

[Stan21]

I need to add a new row to a query-bound table with each update of the query. Currently, new values overwrite the current values. I would like new time-dependent values to be in an added table row.
Can this be done? I found an old post with VBA code that claims to do it, but it wouldn't run because a compiler error was reported to be in it and I can't find the forum post again to verify.
S

[alansidman]

Fast answers need visual help. Please read the yellow banner at the top of this page on how to attach a file.

[Stan21]

Alan:
"Let me know if that works for you"??
Apparently I'm missing something. I don't see any recommendation. Are you asking me to upload the workbook? If so, I don't believe I can do that,
Is there a procedure for appending data to a new table row (not replacing old data) upon query refresh?
S

[tanasedn]

Hi,
There is a way. You can use the append facilities from query editor. But, without a sample workbook with no confidential information, I'm afraid I cannot be more helpful.

[AliGW]

Yes, you can attach a sample (desensitised) workbook.

There are instructions at the top of the page explaining how to attach your sample workbook.

[Stan21]

Thank you for responding. Attached is an example workbook and here's what I'm trying to do:
The RAW DATA sheet is a table (RAW_DATA) in this example. In actual practice it is a table created from a query to a file or, in some instances, a URL.
The CURRENT VALUE sheet has the table CURRENT_VALUE containing info and some arithmetic from RAW_DATA.
I would like to create a table that adds a new row to a table each time the RAW_DATA table is updated or "refreshed" as a query. It can/should be on a new sheet (named it Data HISTORY in this example). Each time the RAW_DATA is refreshed/updated there will be an updated DATATIME.
Is the generation of a running time-based history table of the CURRENT_VALUEs feasible? If so, can the table be manually cleared and started again from a new Row 0 (after the row containing the column titles)?

[alansidman]

Am I understanding correctly, each time you add new data to your raw data table, you wish to create a new query and save it (close and load) to a new sheet. You will not be updating the original query output. If I am not understanding you correctly please advise what you want.

[tanasedn]

When table in Raw Data is changing you need a row with calculation in Change value to be add as a new row in History?
In this case:

If that's OK then you can add this action as worksheet change event to "Raw Data" worksheet.

[Stan21]

tanasden,
I'm not sure I follow your question... When the date in RAW DATA changes, I want the values in CURRENT VALUES that are calculated from RAW DATA to become and added row in DATA HISTORY.
I will try your example to see what it does.
Thank you.

[Stan21]

Each time RAW_VALUES table is updated, I want the values in CURRENT_VALUES that are calculated from RAW_DATA to become an added row in DATA _HISTORY.
RAW_DATA in actual practice is a query to a file or a URL that contains its data. CURRENT_VALUES are the sum and and average of some parameters in RAW DATA and the time the data was measured. When CURRENT_VALUES gets updated from RAW_DATA, I want a row to be added to DATA_HISTORY table.

[tanasedn]

Hi,
I added the macro as an event on change to the worksheet "Raw_Data"
I hope it meets your expectations.

[Stan21]

Thank you tanasedn!
It works with the table as is on the CURRENT VALUES sheet. I must now change that table to a Query Table that periodically gets new data. I don't believe that will change the VBA code you placed in your xlsm file since that table is buffered by RAW DATA and none of the DIM or other statements relate to the CURRENT VALUES table, but I'm not sure. I hope you don't mind if I get back to you if I have trouble.

[tanasedn]

I hope you don't mind if I get back to you if I have trouble.

No, I'm not! You're welcome!