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)
Expand|Select|Wrap|Line Numbers
- 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