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) →

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 3: Putting it together

Part 2: Clearing out bad content

THE PROBLEM:

ExcelWriter will try to populate any data markers that it finds in the template. This means that if the macro updates the ranges on the destination worksheet before the data markers are populated, the data markers will be displayed on the destination sheet and ExcelWriter will try to populate those data markers as well. The macro will still update the ranges, but it will just write over the bad formatting, so the end result still has extra rows: Continue reading Side-by-Side Data Markers (2 of 3) →

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 2: Clearing out bad content

Part 3: Putting it altogether

Part 1: Updating Displayed Ranges from Hidden Ranges

THE PROBLEM:

Putting data markers from different data sets next to each other can be tricky, especially when the data sets are different sizes. ExcelWriter will automatically insert enough rows to accommodate the largest set of data. This means that sometimes, it ends up looking okay. Continue reading Side-by-Side Data Markers (1 of 3) →