none
VB Script to set Pivot source Ms Access Table RRS feed

  • Question

  • Hi Folks -

    I was just wondering if anyone has had success writing a VB Script to leverage an MS Access Table as the source for a pivot.  I have the code written and working successfully if I'm using another sheet as the source, but haven't had success with the MS Access source.

    Thank you!

    Monday, March 23, 2020 11:13 PM

All replies

  • I think you are asking about using VBA form Excel. This is not a VBA forum.

    You can use the data connector of Excel to attach an Access table as the data source for a pivot table.  Post in the Excel users forum for instructions on how to do this.


    \_(ツ)_/

    Tuesday, March 24, 2020 12:00 AM
  • A very simple search would have found the answer for you:

    https://www.youtube.com/watch?v=rsRQqwYKQDc


    \_(ツ)_/

    Tuesday, March 24, 2020 12:06 AM
  • @jrv -

    Thanks for your quick reply.  And no, I am talking about VB Script, NOT vba.  I have automation setup that creates excel files and applicable pivots on the fly, so VBA is not the route I can take.

    Your example you outlined above as a link to YouTube, still doesn't work for me.  Not only is it VBA code, I need to be able to directly hook into the Access Table as the source data would exceed the excel maximum pivot table limit. Therefore, I cannot copy the results into excel and then create the pivot as that defeats the purpose.

    I tried to record a macro of me hooking into Ms Access manually (i.e. Data tab -> From Access etc etc) but have been unable to convert the results to VB script.

    For example, here is the VB SCRIPT code I leverage to create a pivot currently:

    	'::-- 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
    	'objWorkbook1.Worksheets(TOTDIV).PivotTables(TOTDIV).pivotFields("Cost Center - Division").ClearAllFilters
    
    	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
    
    	'::-- Set Formatting --::'
    
    	Call Format(TOTDIV, "D")

    I'll keep looking, thanks anyway!


    • Edited by cdtakacs1 Tuesday, March 24, 2020 10:34 AM
    Tuesday, March 24, 2020 10:33 AM
  • Then you have to use VBScript to get the Access table but your worksheet needs to have the connection predefined.  We usually create a template that has the connection specification that reads an access table.

    If you are not a VBScript master then you should be doing this in PowerShell.  No new work should be done in VBScript as it is obsolete and will be removed from Windows.  PowerShell is more powerful and can do more with Excel and Access.

    You will also find that there are very few people who now VBScript and the ones that do are not interested in encouraging new users of VBScript.

    PowerShell has been the defacto scripting system in Windows for more than 10 years.  It is an absolute requirement for all Windows techs.

    If you insist on doing this the hard way then just use the MSAccess COM object to copy the table to an Excel Sheet then use the sheet in your pivot table.


    \_(ツ)_/


    • Edited by jrv Tuesday, March 24, 2020 10:41 AM
    Tuesday, March 24, 2020 10:40 AM
  • jrv -

    I will explore powershell. Furthermore, i CANNOT copy the table to excel because its exceeds the pivot table threshold of ~62k rows. Hence the need to directly hook into the table...

    Tuesday, March 24, 2020 12:08 PM
  • Then you have to create a link to the table. Create an Excel Workbook and add the link to the Access table then create teh pivot table.  NOw save the WB as a template and just add it to your new workbook and you will have the parts.   We would not do this in code.


    \_(ツ)_/

    Tuesday, March 24, 2020 12:42 PM
  • Just to be sure Excel still works correctly I created a workbook linked it to an access database and set up a simple pivot table.  I then save the WB as an Excel template and created a new WB with the template.  

    To be sure it was dynamic I changed the table values in Access and then refreshed the Excel WB from PowerShell.  It updated the pivot table with the new data.


    \_(ツ)_/

    Tuesday, March 24, 2020 1:04 PM
  • Oh I understand now.  Okay, yes!  That works, just tried it.

    Thank you so much jrv!!!

    Wednesday, March 25, 2020 10:57 AM
  • Just to be sure Excel still works correctly I created a workbook linked it to an access database and set up a simple pivot table.  I then save the WB as an Excel template and created a new WB with the template.  

    To be sure it was dynamic I changed the table values in Access and then refreshed the Excel WB from PowerShell.  It updated the pivot table with the new data.


    \_(ツ)_/

    Oddly enough, doing that manually works.  But if i try to use my current VB code to build the pivot based on the new template (sheet linked to my access table), it still recognizes it as data in a sheet and NOT the database, and bombs because it exceed 62k row... :(
    Wednesday, March 25, 2020 1:12 PM
  • We don't build a pivot we just use the template.  THe pivot already exisits in teh template and is refreshed when a new document is created from teh template.  You can get the pivot table and modify it as needed but start with the most elements you may need.

    If you would stop using VB and use PowerShell all of this would be easier and you would find many examples of ways to do this.  VBScript is obsolete and almost no one uses it anymore.  It also cannot access the Net extensions to Excel.  Excel becomes a Net object in PowerShell. Autocomplete and Intellisence work with Excel in PowerShell but not in VBScript.


    \_(ツ)_/

    Wednesday, March 25, 2020 1:21 PM