Creating Pivot charts (charts and tables) with VBA 1

copy commands?

loop through data.. copy onto table which controls graphs..

this type of thing:

Do Until IsEmpty(Worksheets("VISA_data(Chain_Num)").Cells(row, col))
tableval = Worksheets("worksheet").Cells(row, col)
Worksheets("worksheets").Cells(printrow, 14) = tableval
row = row + 1
printrow = printrow + 1
Loop

with ur own sheet names of course.. sorry

Ik think this is not the answer to my problem


This is how I programmed it:


Private Sub TABLE_Click()

'if exists delete the form
Dim strFormName As String
strFormName = "testchart"
Dim acc1 As AccessObject
For Each acc1 In CurrentProject.AllForms
If acc1.Name = strFormName Then
DoCmd.DeleteObject acForm, strFormName
'hierna mag loop stoppen
End If
Next acc1

'make new form
Const acFormPivotTable = 3
Dim frm1 As Access.Form
Set frm1 = CreateForm
frm1.DefaultView = acFormPivotTable
frm1.RecordSource = "pvt"
strCreateName = frm1.Name
DoCmd.Close acForm, frm1.Name, acSaveYes
DoCmd.Rename strFormName, acForm, strCreateName
DoCmd.OpenForm strFormName, acFormPivotTable

>>>>> Here I need to insert the fields into the form <<<<<

'Set PivotTable fieldsets
Set frm1 = Forms.Item(strFormName)
With frm1.PivotTable.ActiveView
Set fst1 = .FieldSets("Id")
.DataAxis.InsertFieldSet fst1
Set fst1 = .FieldSets("Ur")
.FilterAxis.InsertFieldSet fst1
Set fst1 = .FieldSets("IdBalie")
.RowAxis.InsertFieldSet fst1
Set fst1 = .FieldSets("Leeftijdscategorie")
.DataAxis.InsertFieldSet fst1
End With

'Close form with its PivotTable view
DoCmd.Close acForm, strFormName, acSaveYes
End Sub

Hi Pietersberg,

Ik kan je in het Nederlands gaan helpen, maar dan kan de rest het denk ik niet meer volgen...

I'm also exploring on the techique of coding a chart in VBA and I think I can help you a bit.

First, did you set the right reference? Add this file to your references:

Program Files\Common Files\Microsoft Shared\Web Components\10\1033\OWCVBA10.CHM

Else it won't work.

I have a form with a subform with PivotChart as default view. I prefer this because it looks nicer. Also the record source is already set. This can be dynamic with code of course.

With that I use this code to start with:
-----------
Dim sChartspace As ChChart
Dim sCataxis As ChAxis
Dim sValAxis As ChAxis

Set sChart = Me.subformChart.Form.ChartSpace.Charts(0)
Set sCataxis = Me.subformChart.Form.ChartSpace.Charts(0).Axes(0)
Set sValAxis = Me.subformChart.Form.ChartSpace.Charts(0).Axes(1)

'Assign sets of column values to chart
'categories and chart values
With Me.subformChart.Form.ChartSpace
.SetData chDimCategories, chDataBound, _
"Tijd"
.SetData chDimValues, chDataBound, _
"Aanpasser"
End With

From this point you should be abled to help yourself.
--------------
Here's two articles that might help you too:



But now, I'm stuck al well.
Maybe if you want, we can help eachother a bit more if we exchange mail adresses. I'm having problems changing the timescale of the x-axis, and can't find a solution anywhere...

Hi Pietersberg,

did this help you? And if so, did you try manipulating the time axis?

MrSiezen