The best way to append data to an existing worksheet

Question

I have a workbook which is used to summerize invoicing data. Each month new data from my database needs to be added to to a new column on the end of the worksheet.

Additionally additional information is added by hand to this worksheet so complete regeneration of the workbook every month is not an option.

I assume that to avoid complete regeneration of the workbook I would need to open the exising file as my opentbs template; is it possible to have opentbs to merge the new data in the next available column and if so where then would i put my tag? Could I pre-populate month names and then merge below the appropriate name?

Would a better / more appropriate approach be to merge data onto a completely seperate (and possibly hidden) worksheet and the write a VB macro which fires on document open to move the merged data to it's final resting place?

score 0 · Answer 1 · answered Aug 29 '19 at 11:14

Sub AddDataToEnd()
dim wb as workbook
set wb = workbooks.open("c:\test\myfile.xls") 'name and path of file
dim target as range
with wb.worksheets(1) 'assume data is to be added to first sheet
    set target = .cells(1,.usedrange.columns.count +1)
    'target is now pointing to the first unused column to the right, row 1
    target = format(date(),"mmm") 'write today's month into that cell
   'etc..
end with

score 0 · Accepted Answer · answered Sep 02 '19 at 12:01

You can merge data in columns in a spreadsheet with OpenTBS but only on one row. Using block=tbs:cell. But in fact it is not possible for now to truly merge data in columns in a spreadsheet with OpenTBS, because columns are not explicitly defined in the XML.

A workaround is to have a sheet with data in rows, and use formulas to display those data in columns in another other sheet.

Since you have data in rows. Here is (below) an example of how to make sequential inserts of rows by merging block.

This example assume that you have a block 'b' defined with block=tbs:row at the bottom of a data set.

// select the sheet with data in rows
$TBS->PlugIn(OPENTBS_SELECT_SHEET, 'the_sheet');

// insert copy of block 'b' just before it and named 'bz'
$def = $TBS->GetBlockSource('b', false, true);
$def = st_replace('[b.', '[bz.', $def);
$TBS->GetBlockSource('b', false, true, $def);

// Merge bz
$TBS->MergeBlock('bz', $data);

The best way to append data to an existing worksheet

2 Answers2