469,601 Members | 2,039 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,601 developers. It's quick & easy.

Creating VBA Pivot Table from Query

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)
Expand|Select|Wrap|Line Numbers
  1. Public Sub CreateChartM(strSourceName As String, _
  2.       strChartLabel As String)
  3. Dim i As Integer
  4. Dim db As DAO.Database
  5. Dim rs As DAO.Recordset
  6. Set db = CurrentDb
  7. Set rs = db.OpenRecordset(strSourceName)
  8.  
  9. Dim oApp As New Excel.Application
  10. Dim oBook As Excel.Workbook
  11. Dim oSheet As Excel.Worksheet
  12. Dim oSheetMain As Excel.Worksheet
  13.  
  14. Dim FinalRow As Long
  15. Dim FinalCol As Long
  16.  
  17. Dim oChartObj As Excel.Chart
  18. Dim oColPoint As Excel.Point
  19. Dim oSourceRange As Excel.Range
  20.  
  21.  
  22. Set oBook = oApp.Workbooks.Add
  23.  
  24. 'This used to set the Worksheet to the first page of the sheet
  25. Set oSheet = oBook.Worksheets(1)
  26.  
  27.  
  28.  
  29. '
  30. ' C R E A T E   D A T A   IN   S E C O N D   P A G E
  31. '
  32.  
  33. 'OLD CREATION
  34. 'populate sheet 2 with data
  35.     Dim iNumCols As Integer
  36.     iNumCols = rs.Fields.Count
  37.     For i = 1 To iNumCols
  38.         oSheet.Cells(1, i).Value = rs.Fields(i - 1).Name
  39.     Next
  40.     'Add Data to sheet
  41.     oSheet.Range("A2").CopyFromRecordset rs
  42.     'format header as bold and autofit columns
  43.     With oSheet.Range("a1").Resize(1, iNumCols)
  44.         .Font.Bold = True
  45.         .EntireColumn.AutoFit
  46.     End With
  47.  
  48.  
  49. '
  50. ' C R E A T E   C H A R T   IN   F I R S T   P A G E
  51. '
  52.  
  53.  
  54. 'create chart in sheet 1 from sheet 2 data
  55. Set oSourceRange = oBook.Worksheets(1).Range("A2").CurrentRegion
  56.  
  57. Set oChartObj = oApp.Charts.Add
  58.  
  59. With oChartObj
  60.     .ChartType = xlLineMarkers
  61.     .SetSourceData Source:=oSourceRange, PlotBy:=xlColumns
  62.     .Location (xlLocationAsNewSheet)
  63.  
  64.  
  65.     .HasTitle = True
  66.     With .ChartTitle
  67.             .Characters.Text = strChartLabel
  68.             .Font.Size = 18
  69.     End With
  70.  
  71.     .Axes(xlCategory).CategoryType = xlCategoryScale
  72.  
  73.     .Axes(xlCategory).TickLabels.Orientation = 75
  74. End With
  75.  
  76.  
  77. 'Display
  78. oApp.Visible = True
  79. oApp.UserControl = True
  80.  
  81. 'close out everything
  82. rs.Close
  83. db.Close
  84. End Sub
Nov 29 '09 #1
1 4870
NeoPa
32,198 Expert Mod 16PB
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.
Nov 29 '09 #2

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

4 posts views Thread by Del | last post: by
1 post views Thread by jodyblau | last post: by
6 posts views Thread by tuarek | last post: by
5 posts views Thread by JayDawg | last post: by
9 posts views Thread by PeteCresswell | last post: by
3 posts views Thread by Abhinav Singh | last post: by
4 posts views Thread by guiromero | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.