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:
In the process of editing the file, it is likely that the UpdateRange() macro will be run while the data markers on the data sheet are still unpopulated, so the named ranges on the destination sheet will be updated to display the unpopulated data markers. This means that ExcelWriter will populate the data markers on the destination sheet when the report is run, which will result in the issue describe above.
THE SOLUTION: CLEAR OUT BAD FORMATTING BEFORE UPDATING
The solution to this issue is to clear out any bad formatting or contents on the destination sheet before updating the destination sheet ranges with the correct data from the data sheet.
The code locates the destination range and clears any values that might have already been there if the workbook was opened before the data markers were populated with data (which is highly likely, especially if working in Reporting Services).
'If the ranges are updated before ExcelWriter populates, clears out the bad display data'(i.e. tables with extra rows)Sub ClearBadDisplay(named_ranges() As String) 'Determine the maximum number of rows imported
Dim max As Integer
max = 0
Dim temp_range As Range
For i = 0 To UBound(named_ranges)
Set temp_range = ActiveWorkbook.Names(named_ranges(i)).RefersToRange
If temp_range.Rows.Count > max Then
max = temp_range.Rows.Count
End If
Next
'Select and clear the cells that have the bad display data
Dim dest_range_name As String
For j = 0 To UBound(named_ranges)
dest_range_name = named_ranges(j) & "_DEST"
Set temp_range = ActiveWorkbook.Names(dest_range_name).RefersToRange.Resize(max, 2)
temp_range.ClearContents
Next
End Sub |
Unlike the UpdateRange() method, which does not rely on knowing what the other data sets are, ClearBadDisplay() depends on knowing which populated data set in the group is largest. This is why ClearBadDisplay() takes a group of data markers that are side-by-side. For example, TOTAL_DEST and SDGE_DEST below are part of the same group of side-by-side data markers.
For how to pull it all together, see Part 3!
Follow