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.
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:
Dim named_ranges(3) As String
named_ranges(0) = "CAISO_PGE"
named_ranges(1) = "CAISO_SCE"
named_ranges(2) = "CAISO_SDGE"
named_ranges(3) = "CAISO_TOTAL"
Dim named_ranges2(3) As String
named_ranges2(0) = "Combustion"
named_ranges2(1) = "Combined"
named_ranges2(2) = "OtherCombined"
named_ranges2(3) = "Wind"
ClearBadDisplay named_ranges
ClearBadDisplay named_ranges2
For i = 0 To UBound(named_ranges)
UpdateRange named_ranges(i)
Next
For i = 0 To UBound(named_ranges2)
UpdateRange named_ranges2(i)
Next
For testing purposes, hotkeying this to an ActiveX button is really useful.
Afterthoughts
NOTE 1: This code only deals with the contents of the ranges, not the formatting. In order to preserve the formating: 1. Format the data marker cells on the hidden page. 2. Change the UpdateRange() code to copy the source range and paste it into the displayed range:
source_range.Copy
updated_range.PasteSpecial xlPasteAllUsingSourceTheme
Change the ClearBadDisplay() method to clear the formatting as well as the contents:
temp_range.ClearContents
temp_range.ClearFormats
Since the PasteSpecial() method actually tells Excel to select the source range, make a copy, select the destination range, and paste it into the new range, it can look unnerving when opening the document (since these Excel actions literally flash by on the screen). The customer did not mind and was happy with the result.
NOTE 2: You can also use this workaround with charts. Just set the data source of the chart to point to the data range on the hidden sheet instead of the display sheet. Confused? Don't be. Just read the first two parts of this series!
