I'm attempting now to try and automate the reports that we generate on
a monthly basis.

We simply record the number of activities by month, and chart each
one.

What I'd like to be able to do is automatically create the charts each
month based on the lines of activites that we have. E.g. if we decide
to add on a new line (another activity) one month the user won't need
to go and create a new chart as this will be automatically generated
when they run the appropriate macro to generate these reports.

Is this possible? I need to ensure that the charts fit two to a
landscape page, 3 down (altogether six charts).

Appreciate any help that you may be able to offer on this - or
alternatively a better? way of generating these stats.

Some example data below:

Title: Jan Feb Mar April May June
Activity 1 137
Activity 2 45 57 117 107 74 105
Activity 3 309

This then needs to be saved in a HTML format? in order that this can
be published to the intranet site ( I can do this part but it still
needs to read the source data for the excel file - is it possible to
save as a static HTML file)???

Cheers all for your help in advance.

Thanks - Al Mackay ( )

Al -

It's not clear what your six charts are showing. You have six months
data, so it might be one chart per month. But then you say your user
has to add a chart if a new activity is added. Details, whatever.

The thing to do is have your code loop through some counter variable,
generate and position a chart, and assign the values to the series.
Something like:

Sub MakeCharts()
Dim i As Integer
Dim co As ChartObject
Dim ns As Series
Dim rng As Range
Dim ht As Double
Dim wd As Double
ht = 250 ' chart height
wd = 350 ' chart width
Set rng = ActiveSheet.Range(ActiveSheet.Cells(2, 1), _
ActiveSheet.Cells(2, 1).End(xlDown))
For i = 1 To 6
Set co = ActiveSheet.ChartObjects.Add _
(((i - 1) Mod 2) * wd, Int((i - 1) / 2) * ht + 100, wd, ht)
' (left, top, width, height)
Set ns = co.Chart.SeriesCollection.NewSeries
With ns
.Name = "=" & ActiveSheet.Cells(1, 1).Offset(0, i) _
.Address(ReferenceStyle:=xlR1C1, external:=True)
.XValues = rng
.Values = rng.Offset(0, i)
End With
Next
End Sub

- Jon
-------
Jon Peltier, Microsoft Excel MVP
http://www.geocities.com/jonpeltier/Excel/index.html
_______


Al Mackay wrote:

I'm attempting now to try and automate the reports that we generate on
a monthly basis.

We simply record the number of activities by month, and chart each
one.

What I'd like to be able to do is automatically create the charts each
month based on the lines of activites that we have. E.g. if we decide
to add on a new line (another activity) one month the user won't need
to go and create a new chart as this will be automatically generated
when they run the appropriate macro to generate these reports.

Is this possible? I need to ensure that the charts fit two to a
landscape page, 3 down (altogether six charts).

Appreciate any help that you may be able to offer on this - or
alternatively a better? way of generating these stats.

Some example data below:

Title: Jan Feb Mar April May June
Activity 1 137
Activity 2 45 57 117 107 74 105
Activity 3 309

This then needs to be saved in a HTML format? in order that this can
be published to the intranet site ( I can do this part but it still
needs to read the source data for the excel file - is it possible to
save as a static HTML file)???

Cheers all for your help in advance.

Thanks - Al Mackay ( )

Jon

Thanks loads for your help on this - will try this shortly.

Only included a sample originally (it would normally be data for 12
months - just that it wouldn't fit on the page properly within the
google window).

Thanks again for your time on this.

Cheers - Al.

Jon Peltier wrote in message ...
Al -

It's not clear what your six charts are showing. You have six months
data, so it might be one chart per month. But then you say your user
has to add a chart if a new activity is added. Details, whatever.

The thing to do is have your code loop through some counter variable,
generate and position a chart, and assign the values to the series.
Something like:

Sub MakeCharts()
Dim i As Integer
Dim co As ChartObject
Dim ns As Series
Dim rng As Range
Dim ht As Double
Dim wd As Double
ht = 250 ' chart height
wd = 350 ' chart width
Set rng = ActiveSheet.Range(ActiveSheet.Cells(2, 1), _
ActiveSheet.Cells(2, 1).End(xlDown))
For i = 1 To 6
Set co = ActiveSheet.ChartObjects.Add _
(((i - 1) Mod 2) * wd, Int((i - 1) / 2) * ht + 100, wd, ht)
' (left, top, width, height)
Set ns = co.Chart.SeriesCollection.NewSeries
With ns
.Name = "=" & ActiveSheet.Cells(1, 1).Offset(0, i) _
.Address(ReferenceStyle:=xlR1C1, external:=True)
.XValues = rng
.Values = rng.Offset(0, i)
End With
Next
End Sub

- Jon
-------
Jon Peltier, Microsoft Excel MVP
http://www.geocities.com/jonpeltier/Excel/index.html
_______


Al Mackay wrote:

I'm attempting now to try and automate the reports that we generate on
a monthly basis.

We simply record the number of activities by month, and chart each
one.

What I'd like to be able to do is automatically create the charts each
month based on the lines of activites that we have. E.g. if we decide
to add on a new line (another activity) one month the user won't need
to go and create a new chart as this will be automatically generated
when they run the appropriate macro to generate these reports.

Is this possible? I need to ensure that the charts fit two to a
landscape page, 3 down (altogether six charts).

Appreciate any help that you may be able to offer on this - or
alternatively a better? way of generating these stats.

Some example data below:

Title: Jan Feb Mar April May June
Activity 1 137
Activity 2 45 57 117 107 74 105
Activity 3 309

This then needs to be saved in a HTML format? in order that this can
be published to the intranet site ( I can do this part but it still
needs to read the source data for the excel file - is it possible to
save as a static HTML file)???

Cheers all for your help in advance.

Thanks - Al Mackay ( )

Jon

Tried this and although this does create the charts - wondered if you
could advise how to actually have them based on each row within the
data.

Title: Jan Feb Mar April May June
Activity 1 137
Activity 2 45 57 117 107 74 105
Activity 3 309

for example the charts need to actually be displayed based on below:

Each Activity needs a chart which shows the process over the 12 months
(only six months are shown).

Really appreciate your help on this many thanks,

Al Mackay. ( ).

(Al Mackay) wrote in message . com...
I'm attempting now to try and automate the reports that we generate on
a monthly basis.

We simply record the number of activities by month, and chart each
one.

What I'd like to be able to do is automatically create the charts each
month based on the lines of activites that we have. E.g. if we decide
to add on a new line (another activity) one month the user won't need
to go and create a new chart as this will be automatically generated
when they run the appropriate macro to generate these reports.

Is this possible? I need to ensure that the charts fit two to a
landscape page, 3 down (altogether six charts).

Appreciate any help that you may be able to offer on this - or
alternatively a better? way of generating these stats.

Some example data below:

Title: Jan Feb Mar April May June
Activity 1 137
Activity 2 45 57 117 107 74 105
Activity 3 309

This then needs to be saved in a HTML format? in order that this can
be published to the intranet site ( I can do this part but it still
needs to read the source data for the excel file - is it possible to
save as a static HTML file)???

Cheers all for your help in advance.

Thanks - Al Mackay ( )

Hi Al -

Now I see how you want your charts to go. I've changed the code to work
by rows.

Sub MakeChartsByRow()
Dim i As Integer
Dim co As ChartObject
Dim ns As Series
Dim rng As Range
Dim iMax As Integer
Dim top1 As Double
Dim ht As Double
Dim wd As Double
ht = 250 ' chart height
wd = 350 ' chart width
Set rng = ActiveSheet.Range(ActiveSheet.Cells(1, 2), _
ActiveSheet.Cells(1, 2).End(xlToRight))
iMax = ActiveSheet.Cells(1, 1).CurrentRegion.Rows.Count
top1 = ActiveSheet.Cells(1, 1).Offset(iMax, 0).Top
For i = 1 To iMax - 1
Set co = ActiveSheet.ChartObjects.Add _
(((i - 1) Mod 2) * wd, Int((i - 1) / 2) * ht + top1, wd, ht)
' (left, top, width, height)
Set ns = co.Chart.SeriesCollection.NewSeries
With ns
.Name = "=" & ActiveSheet.Cells(1, 1).Offset(i, 0) _
.Address(ReferenceStyle:=xlR1C1, external:=True)
.XValues = rng
.Values = rng.Offset(i, 0)
End With
Next
End Sub

- Jon
-------
Jon Peltier, Microsoft Excel MVP
http://www.geocities.com/jonpeltier/Excel/index.html
_______

Al Mackay wrote:
Jon

Tried this and although this does create the charts - wondered if you
could advise how to actually have them based on each row within the
data.

Title: Jan Feb Mar April May June
Activity 1 137
Activity 2 45 57 117 107 74 105
Activity 3 309

for example the charts need to actually be displayed based on below:

Each Activity needs a chart which shows the process over the 12 months
(only six months are shown).

Really appreciate your help on this many thanks,

Al Mackay. ( ).

(Al Mackay) wrote in message . com...

I'm attempting now to try and automate the reports that we generate on
a monthly basis.

We simply record the number of activities by month, and chart each
one.

What I'd like to be able to do is automatically create the charts each
month based on the lines of activites that we have. E.g. if we decide
to add on a new line (another activity) one month the user won't need
to go and create a new chart as this will be automatically generated
when they run the appropriate macro to generate these reports.

Is this possible? I need to ensure that the charts fit two to a
landscape page, 3 down (altogether six charts).

Appreciate any help that you may be able to offer on this - or
alternatively a better? way of generating these stats.

Some example data below:

Title: Jan Feb Mar April May June
Activity 1 137
Activity 2 45 57 117 107 74 105
Activity 3 309

This then needs to be saved in a HTML format? in order that this can
be published to the intranet site ( I can do this part but it still
needs to read the source data for the excel file - is it possible to
save as a static HTML file)???

Cheers all for your help in advance.

Thanks - Al Mackay ( )