Creating VBA Pivot Table from Query

[CoreyReynolds]

I can't find any information on this.

If I have an existing query and I want to dump it into a page on a spread sheet and modify it as a pivot table all in VBA, can I do that? I only have the code so far (that works) to dump in the data and create a chart based off it... except I have to manually alter the data I dumped in into a pivot table for the chart to make sense every time.... it sure would be nice if VBA would do it for me!

Heres my existing code: (strSourceName is the query passed in)

Public Sub CreateChartM(strSourceName As String, _
      strChartLabel As String)
Dim i As Integer
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset(strSourceName)

Dim oApp As New Excel.Application
Dim oBook As Excel.Workbook
Dim oSheet As Excel.Worksheet
Dim oSheetMain As Excel.Worksheet

Dim FinalRow As Long
Dim FinalCol As Long

Dim oChartObj As Excel.Chart
Dim oColPoint As Excel.Point
Dim oSourceRange As Excel.Range


Set oBook = oApp.Workbooks.Add

'This used to set the Worksheet to the first page of the sheet
Set oSheet = oBook.Worksheets(1)



'
' C R E A T E   D A T A   IN   S E C O N D   P A G E
'

'OLD CREATION
'populate sheet 2 with data
    Dim iNumCols As Integer
    iNumCols = rs.Fields.Count
    For i = 1 To iNumCols
        oSheet.Cells(1, i).Value = rs.Fields(i - 1).Name
    Next
    'Add Data to sheet
    oSheet.Range("A2").CopyFromRecordset rs
    'format header as bold and autofit columns
    With oSheet.Range("a1").Resize(1, iNumCols)
        .Font.Bold = True
        .EntireColumn.AutoFit
    End With


'
' C R E A T E   C H A R T   IN   F I R S T   P A G E
'


'create chart in sheet 1 from sheet 2 data
Set oSourceRange = oBook.Worksheets(1).Range("A2").CurrentRegion

Set oChartObj = oApp.Charts.Add

With oChartObj
    .ChartType = xlLineMarkers
    .SetSourceData Source:=oSourceRange, PlotBy:=xlColumns
    .Location (xlLocationAsNewSheet)

    
    .HasTitle = True
    With .ChartTitle
            .Characters.Text = strChartLabel
            .Font.Size = 18
    End With
    
    .Axes(xlCategory).CategoryType = xlCategoryScale
    
    .Axes(xlCategory).TickLabels.Orientation = 75
End With


'Display
oApp.Visible = True
oApp.UserControl = True

'close out everything
rs.Close
db.Close
End Sub

[NeoPa]

Are you doing this from within Access or Excel?

I would consider developing the code anyway in Excel. That way you can record the macro (into some fairly kludgy VBA code) while you make the changes manually, then tart up the VBA later with some better understanding from what Excel has already recorded for you.

I'm afraid I don't play much with charts, but getting the data in from Excel to start with is something I do quite a lot.