BACKGOUND:
The ExcelTemplate method of creating Excel files is to design a template in Excel that contains data markers. Data markers are cell values that begin with %%= or %%=$ that specifies a database column, variable, or array to insert into the spreadsheet column. ExcelWriter does this by inserting a new row into the worksheet for each row of data being imported. This means that anything below the data marker in the template will be pushed down as the new rows of data are imported.
Data markers from the same data set can be placed next to each other, but placing data markers from different data sets side-by-side in an ExcelTemplate can cause extra rows to be inserted into the smaller data sets.
This series of posts explains how to get rid of the extra rows in output files that result from placing data markers side by side. If you’re not a current ExcelWriter user, you can download a free evaluation and follow along!
PART 1: Updating displayed ranges from hidden ranges
PART 2: Clearing out bad content
Part 3: Putting it together
For each named range in the workbook, UpdateRange() will need to be called. For each group of side-by-side data markers, ClearBadDisplay() will need to be called. I put UpdateRange() and ClearBadDisplay() in a module and called the following code in the WorkbookOpen() event: Continue reading Side-by-Side Data Markers (3 of 3)