By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,617 Members | 1,166 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,617 IT Pros & Developers. It's quick & easy.

Creating VBA Pivot Table from Query

P: 29
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
Share this Question
Share on Google+
1 Reply


NeoPa
Expert Mod 15k+
P: 31,561
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.