locked
VB Script to set MS Access as Source for Pivot Table RRS feed

  • Question

  • Hi Folks -

    I have a VB script 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
    	Next
    
    	pvtTable.pivotFields("FTE").orientation = 4
    	For Each pf In pvtTable.DataFields
    		With pf
    			.Function = xlSum
    		End With
    	Next

    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! 

    Tuesday, February 25, 2020 5:48 PM