Microsoft Office Tutorials and References
In Depth Information
Mail Merge: Merging with a Chart
'Data series in columns!¶
ds.Application.PlotBy = xlColumns¶
'First column contains record ID¶
'Second column contains legend labels¶
'Following columns contain data¶
'First row contains x-axis labels¶
Set rwLabels = tbl.Rows(1)¶
Set rwData = tbl.Rows(recordIndex)¶
'There can be multiple rows / merge record¶
'therefore loop through table rows until¶
'ID (value in col 1) changes¶
colcounter = 1¶
rowCounter = rowCounter + 1¶
ID = TrimCellText(rwData.Cells(1).Range.Text)¶
'carry over row header to datasheet¶
ds.Cells(rowCounter, 1).Value = _¶
'loop through the columns¶
For i = 3 To nrDataCols¶
colcounter = colcounter + 1¶
With ds¶
'carry over column header only on first pass¶
If rowCounter = 2 Then¶
.Cells(1, colcounter).Value _¶
= TrimCellText(rwLabels.Cells(i).Range.Text)¶
End If¶
'and the data to the data sheet¶
.Cells(rowCounter, colcounter).Value _¶
= TrimCellText(rwData.Cells(i).Range.Text)¶
End With¶
Next i¶
recordIndex = recordIndex + 1¶
'Stop if the end has been reached¶
If totalRows < recordIndex Then Exit Do¶
'Otherwise, move to the next row¶
'Then perform the ID check before looping back¶
Set rwData = tbl.Rows(recordIndex)¶
Loop While ID = TrimCellText(rwData.Cells(1).Range.Text)¶
'Reset in order to start with correct row for next record¶
recordIndex = recordIndex - 1¶
End Sub¶
' * * * * *¶
Function TrimCellText(s As String) As String¶
'Remove end-of-cell markers¶
TrimCellText = Left(s, Len(s) - 2)¶
End Function¶
View the Appendix to learn how to store this procedure
in a Class module.
Search JabSto ::

Custom Search