excel charts into powerpoint

[Forgemaster66]

Hi

I am currently using XL2003 and have the following problem.

I have a workbook called AA130201. It has seven worksheets, six of which are charts. The chart worksheets are called Chart1 to Chart6.
(The workbook is created by some vba code which uses a DBF file for data).

What I would like to do is once the charts are created in the workbook, open powerpoint and copy the individual worksheet charts into individual powerpoint slides. Thsi could be as either a continuation of the vba code to create the charts or by a macro assigned to a button. I have found code which will open powerpoint but I cannot get the charts from the worksheets to the slides.

Can anyone help?

Thanks

Martin

[efernandes67]

Look at this url: http://chandoo.org/wp/2011/08/03/cre...ing-excel-vba/

[Forgemaster66]

Hi

I tried the information in the link but unfortunately, it didn't work. It would open powerpoint ok, but would stop at the 'click to add first slide' screen. I tried stepping through the code in debug, but when it get s to the 'for each' loop to go through the charts, it just seems to skip over them and drop out of the sub. Any ideas whats going on?

Martin

[efernandes67]

Can you post your file?

Elio Fernandes

[Forgemaster66]

Hi Elio

The code I am using is quite long and I fear possibly not the best programming standard! I should also give a little background information.

I have a number of dbf files which represent 'plant data' at my place of work. The excel vba, via a userform, prompts for a date and works out which dbf file to open to view the data. Once the dbf file is open, the user is prompted by another form to enter start and end times and then either specifc data points, or request a set of standard reports. The vbs then creates either a single chart with the individual data points or six standard charts to produce the standard reports. All of this works correctly up to this point.

What I need to do now is for the six charts only, copy them onto six slides in powerpoint. The subroutine to create the standard reports is as follows. (I have written in capitals where I inserted the code form the link you gave which opens powerpoint ok but then just drops out of the subroutine.

-------------------- start of code --------------------

' subroutine to create standard reports from just input times
Sub create_report()

' local variables for the two loops to find the start and end time cell numbers
Dim looper1 As Double
Dim looper2 As Double
Dim count1 As Double
Dim count2 As Double

' local varaibales to make up the statement to pass to the plot section
Dim data_statement1 As String
Dim data_statement2 As String
Dim data_statement3 As String
Dim data_statement4 As String
Dim data_statement5 As String
Dim data_statement6 As String

Dim var1 As String
Dim var2 As String
Dim var3 As String
Dim var4 As String
Dim var5 As String
Dim var6 As String
Dim var7 As String
Dim var8 As String
Dim var9 As String
Dim var10 As String
Dim var11 As String
Dim var12 As String
Dim var14 As String
Dim var16 As String
Dim var21 As String
Dim var22 As String
Dim var23 As String
Dim var24 As String
Dim var25 As String
Dim var26 As String
Dim var27 As String
Dim var28 As String
Dim var29 As String
Dim var30 As String
Dim var33 As String
Dim var34 As String
Dim var69 As String

Call start_end_check

' set start time and end time variables
starttimevar = FrmSelection.StartTimeListBox.Value
endtimevar = FrmSelection.EndTimeListBox.Value
'
' set looper1 and count1 to 2 ie second row where data starts
looper1 = 2
count1 = 2

' find the row number of start time to pass to the graph
For looper1 = 2 To cell_count
If starttimevar > Sheets("AA" & yearvar & monthvar & dayvar & "").Cells(looper1, 2).Value Then
count1 = count1 + 1
End If
Next looper1

' set looper2 and count2 to 2 ie second row where data starts
looper2 = 2
count2 = 2

' find the row number of end time to pass to the graph
For looper2 = 2 To cell_count
If endtimevar > Sheets("AA" & yearvar & monthvar & dayvar & "").Cells(looper2, 2).Value Then
count2 = count2 + 1
End If
Next looper2

' set the variables for the standard reports

'graph1
'comreqsuc
var29 = "AE1,AE" & count1 & ":AE" & count2 & ","
'comducsuc
var30 = "AF1,AF" & count1 & ":AF" & count2 & ","
'northdust
var33 = "AI1,AI" & count1 & ":AI" & count2 & ","
'southdust
var34 = "AJ1,AJ" & count1 & ":AJ" & count2 & ","
'c5:0,acc
var69 = "BS1,BS" & count1 & ":BS" & count2 & ""

'graph2
'bagbkpress
var1 = "C1,C" & count1 & ":C" & count2 & ","
'bagpltemp
var2 = "D1,D" & count1 & ":D" & count2 & ""

'graph3
'fan1dampos
var3 = "E1,E" & count1 & ":E" & count2 & ","
'fan2dampos
var4 = "F1,F" & count1 & ":F" & count2 & ","
'fan3dampos
var5 = "G1,G" & count1 & ":G" & count2 & ","
'fan4dampos
var6 = "H1,H" & count1 & ":H" & count2 & ","
'fan5dampos
var7 = "I1,I" & count1 & ":I" & count2 & ","
'fan1contrl
var8 = "J1,J" & count1 & ":J" & count2 & ","
'fan2contrl
var9 = "K1,K" & count1 & ":K" & count2 & ","
'fan3contrl
var10 = "L1,L" & count1 & ":L" & count2 & ","
'fan4contrl
var11 = "M1,M" & count1 & ":M" & count2 & ","
'fan5contrl
var12 = "N1,N" & count1 & ":N" & count2 & ""

'graph4
'nrfrqsuc
var21 = "W1,W" & count1 & ":W" & count2 & ","
'ndirreqsuc
var22 = "X1,X" & count1 & ":X" & count2 & ","
'nroofsucn
var23 = "Y1,Y" & count1 & ":Y" & count2 & ","
'ndirsucn
var24 = "Z1,Z" & count1 & ":Z" & count2 & ""

'graph5
'trfreqsucn
var25 = "AA1,AA" & count1 & ":AA" & count2 & ","
'tdirreqsuc
var26 = "AB1,AB" & count1 & ":AB" & count2 & ","
'troofsucn
var27 = "AC1,AC" & count1 & ":AC" & count2 & ","
'tdirsucn
var28 = "AD1,AD" & count1 & ":AD" & count2 & ""

'graph6
'ndircontrl
var14 = "P1,P" & count1 & ":P" & count2 & ","
'ndirdposn
var16 = "R1,R" & count1 & ":R" & count2 & ""

' construct the data statements for the 6 graphs based on the items selected
data_statement1 = "" & var29 & var30 & var33 & var34 & var69 & ""
data_statement2 = "" & var1 & var2 & ""
data_statement3 = "" & var3 & var4 & var5 & var6 & var7 & var8 & var9 & var10 & var11 & var12 & ""
data_statement4 = "" & var21 & var22 & var23 & var24 & ""
data_statement5 = "" & var25 & var26 & var27 & var28 & ""
data_statement6 = "" & var14 & var16 & ""

'draw graph1
Charts.Add
'ActiveChart.ChartType = xlLineMarkers
ActiveChart.ChartType = xlLine
ActiveChart.SetSourceData Source:=Sheets("AA" & yearvar & monthvar & dayvar & "").Range( _
"B1,B" & count1 & ":B" & count2 & _
"," & data_statement1 & ""), PlotBy:=xlColumns
ActiveChart.Location Where:=xlLocationAsNewSheet
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = "Data taken from RSTrend file AA" & yearvar & monthvar & dayvar & ""
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "TIME"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "VALUE"
.Axes(xlCategory).TickLabelPosition = xlTickLabelPositionLow
End With

'use the same code to draw five more graphs using the different data_statements

' close frmselection
Unload FrmSelection

THIS IS WHERE I PUT THE CODE FROM THE LINK. POWERPOINT OPENS BUT DOESN'T DO ANYTHING ELSE.

'stop the program at this point as do not need to go any further
End

End Sub

---------------- end of code ---------------------

I hope I have given enough information. If you need anything else, please let me know.

Martin