VBA to set MS Access as Source for Pivot Table RRS feed

  • Question

  • Hi Folks -

    I have a VB that creates a Pivot Table using an excel data source from 1 tab and places the PIvot on another - easy
    '::-- Pivot Table - Actuals with Name --::'
    	Set ws = objWorkbook1.Sheets.Add(, objWorkbook1.Sheets(objWorkbook1.Sheets.Count))
    	ws.Name = TOTDIV
    	Set rngData = objWorkbook1.Sheets(FILE1TAB).Range("A1:BB" & LastCell1.Row)
    	Set rngReport = objWorkbook1.Sheets(TOTDIV).Range("A1")
    	Set pvtCache = objWorkbook1.pivotCaches.add(xlDatabase, rngData.address(true, true, xlA1, true))
    	Set pvtTable = pvtCache.createPivotTable(rngReport, TOTDIV)
    	pvtTable.pivotFields("L-Organization").orientation =xlRowField
    	pvtTable.pivotFields("Cost Center - SubFunction").orientation =xlRowField
    	pvtTable.pivotFields("Legal Name - Last Name").orientation =xlRowField
    	pvtTable.pivotFields("Legal Name - First Name").orientation =xlRowField
    	pvtTable.pivotFields("Worker Type Rpt").orientation = xlColumnField
    	pvtTable.pivotFields("Cost Center - Function").orientation =  xlFilterField
    	pvtTable.pivotFields("Cost Center - Division").orientation =  xlFilterField
    	objexcel.DisplayAlerts = False
    	'::-- Set Filters on Pivot Table - Division --::'
    	objWorkbook1.Worksheets(TOTDIV).Range("B1") = Division
    	With objWorkbook1.Worksheets(TOTDIV).PivotTables(TOTDIV).pivotFields("Worker Type Rpt")
    		.PivotItems("Intern").Visible = False
    		.PivotItems("Consultant").Visible = False
    	End With
    	With pvtTable
    		.InGridDropZones = True
    		.RowAxisLayout 1
    	End With
    	For Each pf In pvtTable.PivotFields
    		If pf.Orientation = xlRowField Then
    			pf.RepeatLabels = True
    			pf.Subtotals(1) = False
    		End If
    	pvtTable.pivotFields("FTE").orientation = 4
    	For Each pf In pvtTable.DataFields
    		With pf
    			.Function = xlSum
    		End With

    However, I was wondering if there was a way to set my source as an Ms Access Table so I can bypass the 65k row limit? Has anyone has success doing this?

    Thank you!

    Thursday, February 27, 2020 9:11 AM

All replies

  • You can create a connection to an Access database and use this to create a pivot table based directly on an Access table or query, without having to import the data into a worksheet.

    The easiest way to generate the code for this is to record a macro. While recording the macro, do the following:

    • On the Insert tab of the ribbon, click PivotTable.
    • Select 'Use an external data source'.
    • Click 'Choose Connection...'.
    • Click 'Browse for More...' at the bottom of the dialog.
    • Navigate to the folder containing the database.
    • Select the database, then click Open.
    • Select the table (or query) that you want to use, then click OK.
    • Specify where you want the pivot table, then click OK.
    • Set up the pivot table the way you want.
    • Stop recording.

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Thursday, February 27, 2020 10:04 AM