A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Word » Mailmerge
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Create Pie Chart Whilst Mail Merging



 
 
Thread Tools Display Modes
  #1  
Old February 11th, 2009, 06:42 PM posted to microsoft.public.word.mailmerge.fields
Stormy
external usenet poster
 
Posts: 20
Default Create Pie Chart Whilst Mail Merging

Hi,

I have an Excel Sheet with 1000 lines of data. Some of the data is text
(people's names etc) and some is numbers. I'd like to run a mail merge to
Word that pulls some of the data across as it is - such as the names - but
also creates a pie chart for each line using the other data. Is this possible?

Thanks
  #2  
Old February 11th, 2009, 09:05 PM posted to microsoft.public.word.mailmerge.fields
Peter Jamieson
external usenet poster
 
Posts: 4,550
Default Create Pie Chart Whilst Mail Merging

I'd have a look at the "Merge with Chart"/"Mail Merge to a chart"
section on Condy Meister's website at

http://homepage.hispeed.ch/cindymeister/MergFram.htm

Peter Jamieson

http://tips.pjmsn.me.uk

Stormy wrote:
Hi,

I have an Excel Sheet with 1000 lines of data. Some of the data is text
(people's names etc) and some is numbers. I'd like to run a mail merge to
Word that pulls some of the data across as it is - such as the names - but
also creates a pie chart for each line using the other data. Is this possible?

Thanks

  #3  
Old February 19th, 2009, 07:46 PM posted to microsoft.public.word.mailmerge.fields
IlliniGirl
external usenet poster
 
Posts: 2
Default Create Pie Chart Whilst Mail Merging

I have tried using the fourth method that Cindy suggests, but I am having
problems with the VB code, as I am not well-versed in it. I find the
instructions hard to follow without VB knowledge. In the basic module it asks
for a filepath. I put mine in (H:\My Documents\Benefits\ChartDataDoc) and I
get a compile error message that says "Expected: line number or label or
statement or end of statement." Can you tell me what I'm doing wrong?


"Peter Jamieson" wrote:

I'd have a look at the "Merge with Chart"/"Mail Merge to a chart"
section on Condy Meister's website at

http://homepage.hispeed.ch/cindymeister/MergFram.htm

Peter Jamieson

http://tips.pjmsn.me.uk

Stormy wrote:
Hi,

I have an Excel Sheet with 1000 lines of data. Some of the data is text
(people's names etc) and some is numbers. I'd like to run a mail merge to
Word that pulls some of the data across as it is - such as the names - but
also creates a pie chart for each line using the other data. Is this possible?

Thanks


  #4  
Old February 19th, 2009, 10:49 PM posted to microsoft.public.word.mailmerge.fields
Doug Robbins - Word MVP
external usenet poster
 
Posts: 8,239
Default Create Pie Chart Whilst Mail Merging

It would be easier to help you if you copied and pasted into a message the
whole routine, indicating which line produces the error message.

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

"IlliniGirl" wrote in message
...
I have tried using the fourth method that Cindy suggests, but I am having
problems with the VB code, as I am not well-versed in it. I find the
instructions hard to follow without VB knowledge. In the basic module it
asks
for a filepath. I put mine in (H:\My Documents\Benefits\ChartDataDoc) and
I
get a compile error message that says "Expected: line number or label or
statement or end of statement." Can you tell me what I'm doing wrong?


"Peter Jamieson" wrote:

I'd have a look at the "Merge with Chart"/"Mail Merge to a chart"
section on Condy Meister's website at

http://homepage.hispeed.ch/cindymeister/MergFram.htm

Peter Jamieson

http://tips.pjmsn.me.uk

Stormy wrote:
Hi,

I have an Excel Sheet with 1000 lines of data. Some of the data is text
(people's names etc) and some is numbers. I'd like to run a mail merge
to
Word that pulls some of the data across as it is - such as the names -
but
also creates a pie chart for each line using the other data. Is this
possible?

Thanks




  #5  
Old February 19th, 2009, 11:12 PM posted to microsoft.public.word.mailmerge.fields
IlliniGirl
external usenet poster
 
Posts: 2
Default Create Pie Chart Whilst Mail Merging

Here is the code I am using. The line I'm referring to is about 35 lines
down. Thanks for any help you can offer - I haven't used VB in 10 years.


Option Explicit

Public x As New clsMergeEvents

Public BeforeMergeExecuted As Boolean
Public CancelMerge As Boolean
Public recordIndex As Long

Const ChartDataDoc As String = "PieChartData.doc"

Sub MergeWithChart()

'Preset the global variables
BeforeMergeExecuted = False
CancelMerge = False
recordIndex = 1

'The events in the class module
'clsMergeEvents will be enabled
ActivateEvents

'As each record is merged
'the MailMergeBeforeMerge
'event will be called
ActiveDocument.MailMerge.Execute Pause:=False

'Turn the events off so that they
'only execute for this document
DeactivateEvents
End Sub

Sub ActivateEvents()
Set x.WdApp = Word.Application
End Sub

Sub DeactivateEvents()
Set x.WdApp = Nothing
End Sub

Function OpenChartDataFile(LocalPath As String) _
As Word.Document
Dim FilePath As String

'Combine the path where the main merge doc
'is stored plus the specified name of the
'document containing the data for the chart
FilePath = H:\My Documents\Benefits\ChartTest

'Make sure the data file exists
'before trying to open it
If Dir(FilePath) "" Then
Set OpenChartDataFile = Documents.Open( _
FileName:=FilePath, _
ReadOnly:=True, _
AddToRecentFiles:=False, _
Visible:=False)
End If
End Function

Sub EditChart(rng As Word.Range, _
DataDoc As Word.Document)
Dim of As Word.OLEFormat
Dim oChart As Graph.Chart
Dim oDataSheet As Graph.DataSheet
Dim tbl As Word.Table
Dim chartType As Long

Set tbl = DataDoc.Tables(1)
'Activate the MS Graph object in the
'main merge document
Set of = rng.InlineShapes(1).OLEFormat
of.DoVerb wdOLEVerbInPlaceActivate

'Pick up the chart for automation
Set oChart = of.Object

'We want to know whether we have a
'pie chart or not
chartType = oChart.chartType

'We also need the data sheet
Set oDataSheet = oChart.Application.DataSheet
oChart.DisplayBlanksAs = xlNotPlotted
FillDataSheet oDataSheet, tbl, chartType
'Finish with the chart
oChart.Application.Update
oChart.Application.Quit
DoEvents
Set oChart = Nothing
End Sub

Sub FillDataSheet(ByRef ds As Graph.DataSheet, _
tbl As Word.Table, chartType As Long)
Dim nrDataCols As Long

recordIndex = recordIndex + 1
nrDataCols = tbl.Columns.Count

'Delete all entries in the datasheet
ds.Cells.ClearContents
If chartType = xlPie Then
ProcessPieChart ds, tbl, nrDataCols
Else
ProcessOtherChart ds, tbl, nrDataCols
End If
DoEvents
End Sub

Sub ProcessPieChart(ByRef ds As Graph.DataSheet, _
tbl As Word.Table, ByVal nrDataCols As Long)
Dim rwData As Word.Row
Dim datavalue As Double
Dim rwLabels As Word.Row
Dim colcounter As Long, i As Long

colcounter = 1

'Data series in rows!
ds.Application.PlotBy = xlRows

'First column contains record ID
'Following columns contain data
'One row per record
'First row contains Legend labels
Set rwLabels = tbl.Rows(1)
Set rwData = tbl.Rows(recordIndex)

'Loop through the data columns
For i = 2 To nrDataCols
With ds
datavalue = CDbl(Val( _
TrimCellText(rwData.Cells(i).Range.Text)))
'Don't carry over 0 values
'If you want to use 0 values
'comment out If and End If lines
If datavalue 0 Then
colcounter = colcounter + 1
'carry over the column header
.Cells(1, colcounter).Value _
= TrimCellText(rwLabels.Cells(i).Range.Text)
'and the data to the data sheet
.Cells(2, colcounter).Value _
= datavalue
End If
End With
Next i
End Sub

Sub ProcessOtherChart(ByRef ds As Graph.DataSheet, _
tbl As Word.Table, ByVal nrDataCols As Long)
Dim rwData As Word.Row
Dim rwLabels As Word.Row
Dim rowCounter As Long
Dim totalRows As Long
Dim ID As String
Dim datavalue As Double
Dim colcounter As Long, i As Long

colcounter = 1
rowCounter = 1
totalRows = tbl.Rows.Count

'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
Do
colcounter = 1
rowCounter = rowCounter + 1
ID = TrimCellText(rwData.Cells(1).Range.Text)

'carry over row header to datasheet
ds.Cells(rowCounter, 1).Value = _
TrimCellText(rwData.Cells(2).Range.Text)

'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 we've reached the end
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

"Doug Robbins - Word MVP" wrote:

It would be easier to help you if you copied and pasted into a message the
whole routine, indicating which line produces the error message.

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

"IlliniGirl" wrote in message
...
I have tried using the fourth method that Cindy suggests, but I am having
problems with the VB code, as I am not well-versed in it. I find the
instructions hard to follow without VB knowledge. In the basic module it
asks
for a filepath. I put mine in (H:\My Documents\Benefits\ChartDataDoc) and
I
get a compile error message that says "Expected: line number or label or
statement or end of statement." Can you tell me what I'm doing wrong?


"Peter Jamieson" wrote:

I'd have a look at the "Merge with Chart"/"Mail Merge to a chart"
section on Condy Meister's website at

http://homepage.hispeed.ch/cindymeister/MergFram.htm

Peter Jamieson

http://tips.pjmsn.me.uk

Stormy wrote:
Hi,

I have an Excel Sheet with 1000 lines of data. Some of the data is text
(people's names etc) and some is numbers. I'd like to run a mail merge
to
Word that pulls some of the data across as it is - such as the names -
but
also creates a pie chart for each line using the other data. Is this
possible?

Thanks




  #6  
Old February 20th, 2009, 12:27 AM posted to microsoft.public.word.mailmerge.fields
Doug Robbins - Word MVP
external usenet poster
 
Posts: 8,239
Default Create Pie Chart Whilst Mail Merging

FilePath = H:\My Documents\Benefits\ChartTest

should be left as:

FilePath = LocalPath & "\" & ChartDataDoc

You must read and follow the instructions provided in the zip file
precisely.
--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

"IlliniGirl" wrote in message
...
Here is the code I am using. The line I'm referring to is about 35 lines
down. Thanks for any help you can offer - I haven't used VB in 10 years.


Option Explicit

Public x As New clsMergeEvents

Public BeforeMergeExecuted As Boolean
Public CancelMerge As Boolean
Public recordIndex As Long

Const ChartDataDoc As String = "PieChartData.doc"

Sub MergeWithChart()

'Preset the global variables
BeforeMergeExecuted = False
CancelMerge = False
recordIndex = 1

'The events in the class module
'clsMergeEvents will be enabled
ActivateEvents

'As each record is merged
'the MailMergeBeforeMerge
'event will be called
ActiveDocument.MailMerge.Execute Pause:=False

'Turn the events off so that they
'only execute for this document
DeactivateEvents
End Sub

Sub ActivateEvents()
Set x.WdApp = Word.Application
End Sub

Sub DeactivateEvents()
Set x.WdApp = Nothing
End Sub

Function OpenChartDataFile(LocalPath As String) _
As Word.Document
Dim FilePath As String

'Combine the path where the main merge doc
'is stored plus the specified name of the
'document containing the data for the chart
FilePath = H:\My Documents\Benefits\ChartTest

'Make sure the data file exists
'before trying to open it
If Dir(FilePath) "" Then
Set OpenChartDataFile = Documents.Open( _
FileName:=FilePath, _
ReadOnly:=True, _
AddToRecentFiles:=False, _
Visible:=False)
End If
End Function

Sub EditChart(rng As Word.Range, _
DataDoc As Word.Document)
Dim of As Word.OLEFormat
Dim oChart As Graph.Chart
Dim oDataSheet As Graph.DataSheet
Dim tbl As Word.Table
Dim chartType As Long

Set tbl = DataDoc.Tables(1)
'Activate the MS Graph object in the
'main merge document
Set of = rng.InlineShapes(1).OLEFormat
of.DoVerb wdOLEVerbInPlaceActivate

'Pick up the chart for automation
Set oChart = of.Object

'We want to know whether we have a
'pie chart or not
chartType = oChart.chartType

'We also need the data sheet
Set oDataSheet = oChart.Application.DataSheet
oChart.DisplayBlanksAs = xlNotPlotted
FillDataSheet oDataSheet, tbl, chartType
'Finish with the chart
oChart.Application.Update
oChart.Application.Quit
DoEvents
Set oChart = Nothing
End Sub

Sub FillDataSheet(ByRef ds As Graph.DataSheet, _
tbl As Word.Table, chartType As Long)
Dim nrDataCols As Long

recordIndex = recordIndex + 1
nrDataCols = tbl.Columns.Count

'Delete all entries in the datasheet
ds.Cells.ClearContents
If chartType = xlPie Then
ProcessPieChart ds, tbl, nrDataCols
Else
ProcessOtherChart ds, tbl, nrDataCols
End If
DoEvents
End Sub

Sub ProcessPieChart(ByRef ds As Graph.DataSheet, _
tbl As Word.Table, ByVal nrDataCols As Long)
Dim rwData As Word.Row
Dim datavalue As Double
Dim rwLabels As Word.Row
Dim colcounter As Long, i As Long

colcounter = 1

'Data series in rows!
ds.Application.PlotBy = xlRows

'First column contains record ID
'Following columns contain data
'One row per record
'First row contains Legend labels
Set rwLabels = tbl.Rows(1)
Set rwData = tbl.Rows(recordIndex)

'Loop through the data columns
For i = 2 To nrDataCols
With ds
datavalue = CDbl(Val( _
TrimCellText(rwData.Cells(i).Range.Text)))
'Don't carry over 0 values
'If you want to use 0 values
'comment out If and End If lines
If datavalue 0 Then
colcounter = colcounter + 1
'carry over the column header
.Cells(1, colcounter).Value _
= TrimCellText(rwLabels.Cells(i).Range.Text)
'and the data to the data sheet
.Cells(2, colcounter).Value _
= datavalue
End If
End With
Next i
End Sub

Sub ProcessOtherChart(ByRef ds As Graph.DataSheet, _
tbl As Word.Table, ByVal nrDataCols As Long)
Dim rwData As Word.Row
Dim rwLabels As Word.Row
Dim rowCounter As Long
Dim totalRows As Long
Dim ID As String
Dim datavalue As Double
Dim colcounter As Long, i As Long

colcounter = 1
rowCounter = 1
totalRows = tbl.Rows.Count

'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
Do
colcounter = 1
rowCounter = rowCounter + 1
ID = TrimCellText(rwData.Cells(1).Range.Text)

'carry over row header to datasheet
ds.Cells(rowCounter, 1).Value = _
TrimCellText(rwData.Cells(2).Range.Text)

'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 we've reached the end
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

"Doug Robbins - Word MVP" wrote:

It would be easier to help you if you copied and pasted into a message
the
whole routine, indicating which line produces the error message.

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

"IlliniGirl" wrote in message
...
I have tried using the fourth method that Cindy suggests, but I am
having
problems with the VB code, as I am not well-versed in it. I find the
instructions hard to follow without VB knowledge. In the basic module
it
asks
for a filepath. I put mine in (H:\My Documents\Benefits\ChartDataDoc)
and
I
get a compile error message that says "Expected: line number or label
or
statement or end of statement." Can you tell me what I'm doing wrong?


"Peter Jamieson" wrote:

I'd have a look at the "Merge with Chart"/"Mail Merge to a chart"
section on Condy Meister's website at

http://homepage.hispeed.ch/cindymeister/MergFram.htm

Peter Jamieson

http://tips.pjmsn.me.uk

Stormy wrote:
Hi,

I have an Excel Sheet with 1000 lines of data. Some of the data is
text
(people's names etc) and some is numbers. I'd like to run a mail
merge
to
Word that pulls some of the data across as it is - such as the
names -
but
also creates a pie chart for each line using the other data. Is this
possible?

Thanks






  #7  
Old March 4th, 2009, 08:50 PM posted to microsoft.public.word.mailmerge.fields
Molly
external usenet poster
 
Posts: 85
Default Create Pie Chart Whilst Mail Merging



"Doug Robbins - Word MVP" wrote:

FilePath = H:\My Documents\Benefits\ChartTest

should be left as:

FilePath = LocalPath & "\" & ChartDataDoc

You must read and follow the instructions provided in the zip file
precisely.
--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

"IlliniGirl" wrote in message
...
Here is the code I am using. The line I'm referring to is about 35 lines
down. Thanks for any help you can offer - I haven't used VB in 10 years.


Option Explicit

Public x As New clsMergeEvents

Public BeforeMergeExecuted As Boolean
Public CancelMerge As Boolean
Public recordIndex As Long

Const ChartDataDoc As String = "PieChartData.doc"

Sub MergeWithChart()

'Preset the global variables
BeforeMergeExecuted = False
CancelMerge = False
recordIndex = 1

'The events in the class module
'clsMergeEvents will be enabled
ActivateEvents

'As each record is merged
'the MailMergeBeforeMerge
'event will be called
ActiveDocument.MailMerge.Execute Pause:=False

'Turn the events off so that they
'only execute for this document
DeactivateEvents
End Sub

Sub ActivateEvents()
Set x.WdApp = Word.Application
End Sub

Sub DeactivateEvents()
Set x.WdApp = Nothing
End Sub

Function OpenChartDataFile(LocalPath As String) _
As Word.Document
Dim FilePath As String

'Combine the path where the main merge doc
'is stored plus the specified name of the
'document containing the data for the chart
FilePath = H:\My Documents\Benefits\ChartTest

'Make sure the data file exists
'before trying to open it
If Dir(FilePath) "" Then
Set OpenChartDataFile = Documents.Open( _
FileName:=FilePath, _
ReadOnly:=True, _
AddToRecentFiles:=False, _
Visible:=False)
End If
End Function

Sub EditChart(rng As Word.Range, _
DataDoc As Word.Document)
Dim of As Word.OLEFormat
Dim oChart As Graph.Chart
Dim oDataSheet As Graph.DataSheet
Dim tbl As Word.Table
Dim chartType As Long

Set tbl = DataDoc.Tables(1)
'Activate the MS Graph object in the
'main merge document
Set of = rng.InlineShapes(1).OLEFormat
of.DoVerb wdOLEVerbInPlaceActivate

'Pick up the chart for automation
Set oChart = of.Object

'We want to know whether we have a
'pie chart or not
chartType = oChart.chartType

'We also need the data sheet
Set oDataSheet = oChart.Application.DataSheet
oChart.DisplayBlanksAs = xlNotPlotted
FillDataSheet oDataSheet, tbl, chartType
'Finish with the chart
oChart.Application.Update
oChart.Application.Quit
DoEvents
Set oChart = Nothing
End Sub

Sub FillDataSheet(ByRef ds As Graph.DataSheet, _
tbl As Word.Table, chartType As Long)
Dim nrDataCols As Long

recordIndex = recordIndex + 1
nrDataCols = tbl.Columns.Count

'Delete all entries in the datasheet
ds.Cells.ClearContents
If chartType = xlPie Then
ProcessPieChart ds, tbl, nrDataCols
Else
ProcessOtherChart ds, tbl, nrDataCols
End If
DoEvents
End Sub

Sub ProcessPieChart(ByRef ds As Graph.DataSheet, _
tbl As Word.Table, ByVal nrDataCols As Long)
Dim rwData As Word.Row
Dim datavalue As Double
Dim rwLabels As Word.Row
Dim colcounter As Long, i As Long

colcounter = 1

'Data series in rows!
ds.Application.PlotBy = xlRows

'First column contains record ID
'Following columns contain data
'One row per record
'First row contains Legend labels
Set rwLabels = tbl.Rows(1)
Set rwData = tbl.Rows(recordIndex)

'Loop through the data columns
For i = 2 To nrDataCols
With ds
datavalue = CDbl(Val( _
TrimCellText(rwData.Cells(i).Range.Text)))
'Don't carry over 0 values
'If you want to use 0 values
'comment out If and End If lines
If datavalue 0 Then
colcounter = colcounter + 1
'carry over the column header
.Cells(1, colcounter).Value _
= TrimCellText(rwLabels.Cells(i).Range.Text)
'and the data to the data sheet
.Cells(2, colcounter).Value _
= datavalue
End If
End With
Next i
End Sub

Sub ProcessOtherChart(ByRef ds As Graph.DataSheet, _
tbl As Word.Table, ByVal nrDataCols As Long)
Dim rwData As Word.Row
Dim rwLabels As Word.Row
Dim rowCounter As Long
Dim totalRows As Long
Dim ID As String
Dim datavalue As Double
Dim colcounter As Long, i As Long

colcounter = 1
rowCounter = 1
totalRows = tbl.Rows.Count

'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
Do
colcounter = 1
rowCounter = rowCounter + 1
ID = TrimCellText(rwData.Cells(1).Range.Text)

'carry over row header to datasheet
ds.Cells(rowCounter, 1).Value = _
TrimCellText(rwData.Cells(2).Range.Text)

'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 we've reached the end
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

"Doug Robbins - Word MVP" wrote:

It would be easier to help you if you copied and pasted into a message
the
whole routine, indicating which line produces the error message.

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

"IlliniGirl" wrote in message
...
I have tried using the fourth method that Cindy suggests, but I am
having
problems with the VB code, as I am not well-versed in it. I find the
instructions hard to follow without VB knowledge. In the basic module
it
asks
for a filepath. I put mine in (H:\My Documents\Benefits\ChartDataDoc)
and
I
get a compile error message that says "Expected: line number or label
or
statement or end of statement." Can you tell me what I'm doing wrong?


"Peter Jamieson" wrote:

I'd have a look at the "Merge with Chart"/"Mail Merge to a chart"
section on Condy Meister's website at

http://homepage.hispeed.ch/cindymeister/MergFram.htm

Peter Jamieson

http://tips.pjmsn.me.uk

Stormy wrote:
Hi,

I have an Excel Sheet with 1000 lines of data. Some of the data is
text
(people's names etc) and some is numbers. I'd like to run a mail
merge
to
Word that pulls some of the data across as it is - such as the
names -
but
also creates a pie chart for each line using the other data. Is this
possible?

Thanks





  #8  
Old February 25th, 2010, 10:50 PM posted to microsoft.public.word.mailmerge.fields
Stephanie J
external usenet poster
 
Posts: 1
Default Creating a chart after mail merge with VBA

I too used the VB code, and received an error message at this line:

Set of = rng.InlineShapes(1).OLEFormat

The error says:
"Run-Time error '5941':
The requested member of the collection does not exist"

When I click "debug" it highlights teh last line listed below.

-----
Option Explicit

Public x As New Class1

Public BeforeMergeExecuted As Boolean
Public CancelMerge As Boolean
Public recordIndex As Long

Const ChartDataDoc As String = "ChartData.doc"

Sub MergeWithChart()

'Preset the global variables
BeforeMergeExecuted = False
CancelMerge = False
recordIndex = 1

'The events in the class module
'Class1 will be enabled
ActivateEvents

'As each record is merged
'the MailMergeBeforeMerge
'event will be called
ActiveDocument.MailMerge.Execute Pause:=False

'Turn the events off so that they
'only execute for this document
DeactivateEvents
End Sub

Sub ActivateEvents()
Set x.WdApp = Word.Application
End Sub

Sub DeactivateEvents()
Set x.WdApp = Nothing
End Sub

Function OpenChartDataFile(LocalPath As String) _
As Word.Document
Dim FilePath As String

'Combine the path where the main merge doc
'is stored plus the specified name of the
'document containing the data for the chart
FilePath = LocalPath & "\" & ChartDataDoc

'Make sure the data file exists
'before trying to open it
If Dir(FilePath) "" Then
Set OpenChartDataFile = Documents.Open( _
FileName:=FilePath, _
ReadOnly:=True, _
AddToRecentFiles:=False, _
Visible:=False)
End If
End Function

Sub EditChart(rng As Word.Range, _
DataDoc As Word.Document)
Dim of As Word.OLEFormat
Dim oChart As Graph.Chart
Dim oDataSheet As Graph.DataSheet
Dim tbl As Word.Table
Dim chartType As Long

Set tbl = DataDoc.Tables(1)
'Activate the MS Graph object in the
'main merge document
Set of = rng.InlineShapes(1).OLEFormat




Moll wrote:

Create Pie Chart Whilst Mail Merging
04-Mar-09

"Doug Robbins - Word MVP" wrote:

Previous Posts In This Thread:

On Wednesday, February 11, 2009 12:42 PM
Storm wrote:

Create Pie Chart Whilst Mail Merging
Hi,

I have an Excel Sheet with 1000 lines of data. Some of the data is text
(people's names etc) and some is numbers. I'd like to run a mail merge to
Word that pulls some of the data across as it is - such as the names - but
also creates a pie chart for each line using the other data. Is this possible?

Thanks

On Wednesday, February 11, 2009 3:05 PM
Peter Jamieson wrote:

I'd have a look at the "Merge with Chart"/"Mail Merge to a chart" section on
I'd have a look at the "Merge with Chart"/"Mail Merge to a chart"
section on Condy Meister's website at

http://homepage.hispeed.ch/cindymeister/MergFram.htm

Peter Jamieson

http://tips.pjmsn.me.uk

Stormy wrote:

On Thursday, February 19, 2009 1:46 PM
IlliniGir wrote:

I have tried using the fourth method that Cindy suggests, but I am having
I have tried using the fourth method that Cindy suggests, but I am having
problems with the VB code, as I am not well-versed in it. I find the
instructions hard to follow without VB knowledge. In the basic module it asks
for a filepath. I put mine in (H:\My Documents\Benefits\ChartDataDoc) and I
get a compile error message that says "Expected: line number or label or
statement or end of statement." Can you tell me what I'm doing wrong?


"Peter Jamieson" wrote:

On Thursday, February 19, 2009 4:49 PM
Doug Robbins - Word MVP wrote:

It would be easier to help you if you copied and pasted into a message the
It would be easier to help you if you copied and pasted into a message the
whole routine, indicating which line produces the error message.

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

"IlliniGirl" wrote in message
...

On Thursday, February 19, 2009 5:12 PM
IlliniGir wrote:

Here is the code I am using. The line I'm referring to is about 35 lines down.
Here is the code I am using. The line I'm referring to is about 35 lines
down. Thanks for any help you can offer - I haven't used VB in 10 years.


Option Explicit

Public x As New clsMergeEvents

Public BeforeMergeExecuted As Boolean
Public CancelMerge As Boolean
Public recordIndex As Long

Const ChartDataDoc As String = "PieChartData.doc"

Sub MergeWithChart()

'Preset the global variables
BeforeMergeExecuted = False
CancelMerge = False
recordIndex = 1

'The events in the class module
'clsMergeEvents will be enabled
ActivateEvents

'As each record is merged
'the MailMergeBeforeMerge
'event will be called
ActiveDocument.MailMerge.Execute Pause:=False

'Turn the events off so that they
'only execute for this document
DeactivateEvents
End Sub

Sub ActivateEvents()
Set x.WdApp = Word.Application
End Sub

Sub DeactivateEvents()
Set x.WdApp = Nothing
End Sub

Function OpenChartDataFile(LocalPath As String) _
As Word.Document
Dim FilePath As String

'Combine the path where the main merge doc
'is stored plus the specified name of the
'document containing the data for the chart
FilePath = H:\My Documents\Benefits\ChartTest

'Make sure the data file exists
'before trying to open it
If Dir(FilePath) "" Then
Set OpenChartDataFile = Documents.Open( _
FileName:=FilePath, _
ReadOnly:=True, _
AddToRecentFiles:=False, _
Visible:=False)
End If
End Function

Sub EditChart(rng As Word.Range, _
DataDoc As Word.Document)
Dim of As Word.OLEFormat
Dim oChart As Graph.Chart
Dim oDataSheet As Graph.DataSheet
Dim tbl As Word.Table
Dim chartType As Long

Set tbl = DataDoc.Tables(1)
'Activate the MS Graph object in the
'main merge document
Set of = rng.InlineShapes(1).OLEFormat
of.DoVerb wdOLEVerbInPlaceActivate

'Pick up the chart for automation
Set oChart = of.Object

'We want to know whether we have a
'pie chart or not
chartType = oChart.chartType

'We also need the data sheet
Set oDataSheet = oChart.Application.DataSheet
oChart.DisplayBlanksAs = xlNotPlotted
FillDataSheet oDataSheet, tbl, chartType
'Finish with the chart
oChart.Application.Update
oChart.Application.Quit
DoEvents
Set oChart = Nothing
End Sub

Sub FillDataSheet(ByRef ds As Graph.DataSheet, _
tbl As Word.Table, chartType As Long)
Dim nrDataCols As Long

recordIndex = recordIndex + 1
nrDataCols = tbl.Columns.Count

'Delete all entries in the datasheet
ds.Cells.ClearContents
If chartType = xlPie Then
ProcessPieChart ds, tbl, nrDataCols
Else
ProcessOtherChart ds, tbl, nrDataCols
End If
DoEvents
End Sub

Sub ProcessPieChart(ByRef ds As Graph.DataSheet, _
tbl As Word.Table, ByVal nrDataCols As Long)
Dim rwData As Word.Row
Dim datavalue As Double
Dim rwLabels As Word.Row
Dim colcounter As Long, i As Long

colcounter = 1

'Data series in rows!
ds.Application.PlotBy = xlRows

'First column contains record ID
'Following columns contain data
'One row per record
'First row contains Legend labels
Set rwLabels = tbl.Rows(1)
Set rwData = tbl.Rows(recordIndex)

'Loop through the data columns
For i = 2 To nrDataCols
With ds
datavalue = CDbl(Val( _
TrimCellText(rwData.Cells(i).Range.Text)))
'Don't carry over 0 values
'If you want to use 0 values
'comment out If and End If lines
If datavalue 0 Then
colcounter = colcounter + 1
'carry over the column header
.Cells(1, colcounter).Value _
= TrimCellText(rwLabels.Cells(i).Range.Text)
'and the data to the data sheet
.Cells(2, colcounter).Value _
= datavalue
End If
End With
Next i
End Sub

Sub ProcessOtherChart(ByRef ds As Graph.DataSheet, _
tbl As Word.Table, ByVal nrDataCols As Long)
Dim rwData As Word.Row
Dim rwLabels As Word.Row
Dim rowCounter As Long
Dim totalRows As Long
Dim ID As String
Dim datavalue As Double
Dim colcounter As Long, i As Long

colcounter = 1
rowCounter = 1
totalRows = tbl.Rows.Count

'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
Do
colcounter = 1
rowCounter = rowCounter + 1
ID = TrimCellText(rwData.Cells(1).Range.Text)

'carry over row header to datasheet
ds.Cells(rowCounter, 1).Value = _
TrimCellText(rwData.Cells(2).Range.Text)

'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 we've reached the end
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

"Doug Robbins - Word MVP" wrote:

On Thursday, February 19, 2009 6:27 PM
Doug Robbins - Word MVP wrote:

Create Pie Chart Whilst Mail Merging
FilePath = H:\My Documents\Benefits\ChartTest

should be left as:

FilePath = LocalPath & "\" & ChartDataDoc

You must read and follow the instructions provided in the zip file
precisely.
--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

"IlliniGirl" wrote in message
...

On Wednesday, March 04, 2009 2:50 PM
Moll wrote:

Create Pie Chart Whilst Mail Merging
"Doug Robbins - Word MVP" wrote:


Submitted via EggHeadCafe - Software Developer Portal of Choice
Generic Feed Parsers Redux
http://www.eggheadcafe.com/tutorials...sers-redu.aspx
  #9  
Old February 26th, 2010, 12:12 AM posted to microsoft.public.word.mailmerge.fields
Peter Jamieson
external usenet poster
 
Posts: 4,550
Default Creating a chart after mail merge with VBA

The piece of code that actually invokes the EditChart sub is an event
handler in the clsMergeEvents module that does this:

If Doc.Bookmarks.Exists(BookmarkName) Then
Set rngChart = Doc.Bookmarks(BookmarkName).Range
EditChart rngChart, DataDoc

So if your code got this far:

Set of = rng.InlineShapes(1).OLEFormat


then the bookmark does exist. But for that to work
a. the bookmark must "cover" at least one InlineShape
b. the first such InlineShape would have to be a shape type that has
an OLEFormat. A Chart object inserted as per the sample mail merge main
document should be set up OK, and indeed anything that is inserted by
embedding an object, but it could be that you have inserted the chart in
some other way (e.g. as a picture rather than an OLE object, and that
may not have a .OLEObject. Or perhaps the object is contained within a
Drawing Canvas - I'm not sure whether that would cause this problem).

Peter Jamieson

http://tips.pjmsn.me.uk

On 25/02/2010 21:50, Stephanie J wrote:
I too used the VB code, and received an error message at this line:

Set of = rng.InlineShapes(1).OLEFormat

The error says:
"Run-Time error '5941':
The requested member of the collection does not exist"

When I click "debug" it highlights teh last line listed below.

-----
Option Explicit

Public x As New Class1

Public BeforeMergeExecuted As Boolean
Public CancelMerge As Boolean
Public recordIndex As Long

Const ChartDataDoc As String = "ChartData.doc"

Sub MergeWithChart()

'Preset the global variables
BeforeMergeExecuted = False
CancelMerge = False
recordIndex = 1

'The events in the class module
'Class1 will be enabled
ActivateEvents

'As each record is merged
'the MailMergeBeforeMerge
'event will be called
ActiveDocument.MailMerge.Execute Pause:=False

'Turn the events off so that they
'only execute for this document
DeactivateEvents
End Sub

Sub ActivateEvents()
Set x.WdApp = Word.Application
End Sub

Sub DeactivateEvents()
Set x.WdApp = Nothing
End Sub

Function OpenChartDataFile(LocalPath As String) _
As Word.Document
Dim FilePath As String

'Combine the path where the main merge doc
'is stored plus the specified name of the
'document containing the data for the chart
FilePath = LocalPath& "\"& ChartDataDoc

'Make sure the data file exists
'before trying to open it
If Dir(FilePath) "" Then
Set OpenChartDataFile = Documents.Open( _
FileName:=FilePath, _
ReadOnly:=True, _
AddToRecentFiles:=False, _
Visible:=False)
End If
End Function

Sub EditChart(rng As Word.Range, _
DataDoc As Word.Document)
Dim of As Word.OLEFormat
Dim oChart As Graph.Chart
Dim oDataSheet As Graph.DataSheet
Dim tbl As Word.Table
Dim chartType As Long

Set tbl = DataDoc.Tables(1)
'Activate the MS Graph object in the
'main merge document
Set of = rng.InlineShapes(1).OLEFormat




Moll wrote:

Create Pie Chart Whilst Mail Merging
04-Mar-09

"Doug Robbins - Word MVP" wrote:

Previous Posts In This Thread:

On Wednesday, February 11, 2009 12:42 PM
Storm wrote:

Create Pie Chart Whilst Mail Merging
Hi,

I have an Excel Sheet with 1000 lines of data. Some of the data is text
(people's names etc) and some is numbers. I'd like to run a mail merge to
Word that pulls some of the data across as it is - such as the names - but
also creates a pie chart for each line using the other data. Is this possible?

Thanks

On Wednesday, February 11, 2009 3:05 PM
Peter Jamieson wrote:

I'd have a look at the "Merge with Chart"/"Mail Merge to a chart" section on
I'd have a look at the "Merge with Chart"/"Mail Merge to a chart"
section on Condy Meister's website at

http://homepage.hispeed.ch/cindymeister/MergFram.htm

Peter Jamieson

http://tips.pjmsn.me.uk

Stormy wrote:

On Thursday, February 19, 2009 1:46 PM
IlliniGir wrote:

I have tried using the fourth method that Cindy suggests, but I am having
I have tried using the fourth method that Cindy suggests, but I am having
problems with the VB code, as I am not well-versed in it. I find the
instructions hard to follow without VB knowledge. In the basic module it asks
for a filepath. I put mine in (H:\My Documents\Benefits\ChartDataDoc) and I
get a compile error message that says "Expected: line number or label or
statement or end of statement." Can you tell me what I'm doing wrong?


"Peter Jamieson" wrote:

On Thursday, February 19, 2009 4:49 PM
Doug Robbins - Word MVP wrote:

It would be easier to help you if you copied and pasted into a message the
It would be easier to help you if you copied and pasted into a message the
whole routine, indicating which line produces the error message.

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 02:46 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.