473,326 Members | 2,125 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

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 5550
NeoPa
32,556 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

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

Similar topics

4
by: Del | last post by:
I need to create Pivot table in Excel from Access. Currently I run a query and output the data to an excel worksheet and create the pivots via automation. The issue I face is that the query may...
4
by: Alan Lane | last post by:
Hello world: I'm including both code and examples of query output. I appologize if that makes this message longer than it should be. Anyway, I need to change the query below into a pivot table...
8
by: Jerome Ranch | last post by:
Okay So I've got pivot tables setup in Access 2003. Only about 30K records in the current 2005 databases...the pivots summarize the info in a number of nice ways. I need to get the pivot tables...
1
by: jodyblau | last post by:
I am attempting to learn to use pivot tables. I have noticed that if I have used the Lookup Wizard when creating my tables so allow the user to select from a list of choices when entering data,...
6
by: tuarek | last post by:
Hi all, Is there any article / white paper that you can suggest to me to create a pivot table? I found Alexander Kuznetsov's article on IBM's web site. But i need something deeper than that. ...
5
by: JayDawg | last post by:
Is there any way to create a pivot table in a report? I have a query with the fields Date, Name, and Data, and I am trying to create a report that has the dates running across the top (like...
1
by: peter | last post by:
Dear all, I have an existing query called 'A', but I want it to view in Pivot Table. What I do is : - Double click the query and choose Pivot Table view. - I make some changes by adding some...
9
by: PeteCresswell | last post by:
I've got something called "Reference Rates". The idea is that on a given day, we have various rates of return for various entities. e.g. Libor 3-month return, Libor 6-month return, US Treasury...
3
by: Abhinav Singh | last post by:
Hi, Earlier i use to make reports in excel but now i got a task of displaying it on a web page and in a pivot table format.I use SQL 2008 as backend and visual studio to create the web page .I...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.