Solved: Pivot table vba

[SeanJ]

I need help with creating a pivot table in excel from Access vba after I created an Excel worksheet from access. I created a sub for it aready.

Help

Please see zip files for everything.

On the form you must enter a server name and dates before getting some queries.

Thanks,

Sean

[OBP]

Is there any reason why you are not using Access's Pivot Table?

[SeanJ]

Yep, the way the combo calendar boxes are setup does not allow for a pivot table in access. If someone has a better way of doing it then I am all ears.

[OBP]

SeanJ, I have your Access database, can you tell me what you want to pivot table?
Do you want to graph it also?
Can I ask why you are using 2 combos and a calendar rather than 2 hidden date fields and a calendar for each field?

[SeanJ]

I need to graph the worksheet("Day's Average") so that the days will be on the x Axis and the AvgTime on the y Axis. I thought I needed to be put into a pivot table to display the info correctly.

For the combo obx I did not think of that, but that is a good idea.

[SeanJ]

UPDATE:

Maybe if I set a Excel Reference in Access, then maybe I may be come a fair programmer :banghead: :banghead: :banghead: :banghead: :banghead: :banghead: :banghead:

:doh: :doh: :doh: :doh: :doh: :doh: :doh: :doh:

[SeanJ]

I got the graph to work, but again if anyone got a better idea let me know.

Private Sub GraphData()

Dim counti As Long
Dim wsChart As Object
counti = ws.Range("C" & Rows.Count).End(xlUp).Row
Set wsChart = xlApp.Charts.Add()


xlApp.ActiveChart.SetSourceData Source:=xlApp.Sheets("Day's Average").Range(ws.Cells(1, 2), ws.Cells(counti, 3)), PlotBy:=xlRows
With wsChart

.ChartType = xlColumnClustered
.SetSourceData Source:=xlApp.Sheets("Day's Average").Range(ws.Cells(1, 2), ws.Cells(counti, 3)), PlotBy:=xlColumns
.SeriesCollection(1).Delete
.SeriesCollection(1).XValues = xlApp.Sheets("Day's Average").Range(ws.Cells(2, 2), ws.Cells(counti, 2)).Value

End With

End Sub

[OBP]

Do you mean like the one in the form called graph in this database.

[SeanJ]

Sorry, the graph is for WorkSheets("Day's Average").

I modified the code for the graph again.

Private Sub GraphData()

Dim counti As Long
Dim wsChart As Object
Dim strName As String
Set ws = xlApp.ActiveSheet
strName = ws.Cells(2, 1).Value
counti = ws.Range("A" & Rows.Count).End(xlUp).Row
Set wsChart = xlApp.Charts.Add()
wsChart.Name = strName

xlApp.ActiveChart.SetSourceData Source:=xlApp.Sheets("Day's Average").Range(ws.Cells(1, 2), ws.Cells(counti, 3)), PlotBy:=xlRows
With wsChart

.ChartType = xlColumnClustered
.SetSourceData Source:=xlApp.Sheets("Day's Average").Range(ws.Cells(1, 2), ws.Cells(counti, 3)), PlotBy:=xlColumns
.SeriesCollection(1).Delete
.SeriesCollection(1).XValues = xlApp.Sheets("Day's Average").Range(ws.Cells(2, 2), ws.Cells(counti, 2)).Value
.HasTitle = True
.ChartTitle.Characters.Text = strName
.ChartTitle.Font.Bold = True
.ChartTitle.Font.Size = 12
.Deselect

End With

End Sub

[OBP]

SeanJ, in the database that I posted did you set your form's values and then open the form called Graph?

[SeanJ]

My sponsor want all information to be presented in Excel and then some stuff later to be displayed in PowerPoint when this project gets lifted off the ground. Thank for helping with the display in Access, but my sponsor will not go for it.