Results 1 to 10 of 10
  1. #1
    Lounger
    Join Date
    Feb 2006
    Posts
    34
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Creating Pivot Tables in VBA (2003)

    I have an Access database where I have a table that I want to put in a pivot table via VBA. I have the following fields in the table:

    EmpName - Text (Employees Names)
    Months - Text (formatted example January, 01, February, 01, March, 01)
    Commission - Currency
    Dept - Text (Example DBO, BANK, etc.)
    Areas - Text (Example RET, LIFE, ANN, etc.)

    I would like to have the pivot table look like below:

    Dept EmpName Areas Months
    Commission associated with the months field under the Months column

    If anyone could help me, I would greatly appreciate it.

    Thanks,
    Chris

  2. Subscribe to our Windows Secrets Newsletter - It's Free!

    Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 15 Times in 15 Posts

    Re: Creating Pivot Tables in VBA (2003)

    You've posted this in the Excel forum. Does this mean that you want to create an Excel workbook with a pivot table based on the Access table? Your description resembles a Totals query in Access more than a pivot table.

  4. #3
    Lounger
    Join Date
    Feb 2006
    Posts
    34
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Creating Pivot Tables in VBA (2003)

    Yep. I need to create a pivot table in an Excel workbook based on the Access table. The commissions are already totaled in Access by Areas, now I need to take this and put it in an Excel pivot table and put the comissions per Areas under the Months. For Example:

    Dept EmpName Areas January, 06 February, 06
    DBO Tom RET $500.00 $200.00
    DBO Tom LIFE $200.00 $50.00
    DBO Tom ANN $0.00 $400.00

    I hope this helps.

  5. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 15 Times in 15 Posts

    Re: Creating Pivot Tables in VBA (2003)

    You could use a macro like this in Excel:

    Sub CreatePivot()
    With ActiveWorkbook.PivotCaches.Add(SourceType:=xlExter nal)
    .Connection = "ODBC;DSN=MS Access Database;" & _
    "DBQ=C:AccessTest.mdb;DriverId=25;" & _
    "FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;"
    .CommandType = xlCmdSql
    .CommandText = "SELECT * FROM MyTable"
    .CreatePivotTable TableDestination:=Range("A3"), _
    TableName:="PT1"
    End With
    With ActiveSheet.PivotTables("PT1")
    .AddFields RowFields:=Array("Dept", "EmpName", "Areas"), _
    ColumnFields:="Months"
    .PivotFields("Commission").Orientation = xlDataField
    End With
    End Sub

    Replace MyTable with the name of your table.

  6. #5
    Lounger
    Join Date
    Feb 2006
    Posts
    34
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Creating Pivot Tables in VBA (2003)

    Thanks for your help!!! One last question. I am using an Access mdw. In your code below, where do I put the mdw along with the username and password?

    With ActiveWorkbook.PivotCaches.Add(SourceType:=xlExter nal)
    .Connection = "ODBC;DSN=MS Access Database;" & _
    "DBQ=Cocuments and SettingsteqcjeDesktopHistoricGDC;DriverId=25;" & _
    "FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;"

    Again, thanks for your help.
    Chris

  7. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 15 Times in 15 Posts

    Re: Creating Pivot Tables in VBA (2003)

    You can specify the SystemDB, UID and PWD arguments in the connection string:
    <code>
    .Connection = "ODBC;DSN=MS Access Database;" & _
    "DBQ=Cocuments and SettingsteqcjeDesktopHistoricGDC;" & _
    "SystemDB=C:WorkgroupSecured.mdw;" &_
    "UID=celias;PWD=secret;DriverId=25;" & _
    "FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;"
    </code>
    with, of course, the appropriate values substituted.

  8. #7
    Lounger
    Join Date
    Feb 2006
    Posts
    34
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Creating Pivot Tables in VBA (2003)

    Thanks a lot for your help.

    Chris

  9. #8
    Lounger
    Join Date
    Feb 2006
    Posts
    34
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Creating Pivot Tables in VBA (2003)

    Is there anyway to put the month and year in order such as January, 06 then February, 06 then March, 06 and so on in a pivot table?

    Thanks,
    Chris

  10. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 15 Times in 15 Posts

    Re: Creating Pivot Tables in VBA (2003)

    By using a date/time field instead of a text field in the underlying data. If you cannot change the Months field into a date/time field, you could create a query with a calculated date field, and use that as source for the pivot table. I haven't tried it, but I'd guess that

    M: DateValue("1 " & [Months])

    would do it.

  11. #10
    Lounger
    Join Date
    Feb 2006
    Posts
    34
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Creating Pivot Tables in VBA (2003)

    It works great.

    Again. Thanks for your help.

    Thanks,
    Chris

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •