Data from Access to Excel then a Pivot (1 Viewer)

[Rover Mann]

Is there a way to get data from access to excel and then create pivot table with a code (automatically)?

 

[June7]

Yes. Have you done any research?

Review:
https://www.access-programmers.co.u...ss-to-automate-a-pivot-table-in-excel.308144/
https://stackoverflow.com/questions...xcel-file-and-make-pivot-tables-in-excel-file

 

[Puneet Gogia]

The best way for this is to use Excel's Power Query to connect data from Access as a database. Once you do this, you can even change the data the way you want.

To connect Power Query with Access, follow these steps:

1. Open Excel and go to the "Data" tab.
2. Click on "Get Data" in the upper left corner.
3. From the drop-down menu, select "From Database" and then "From Microsoft Access Database".
4. Navigate to the location of your Access Database file, select it and click "Import".
5. Power Query Editor will open, and you can select the tables or queries you want to load into Excel.
6. Click "Load" to load the data into Excel.

After that, you can use a VBA code to create a pivot table from that data. You can use the code like below for this:

Sub InsertPivotTable()
'Macro By ExcelChamps.com

'Declare Variables
Dim PSheet As Worksheet
Dim DSheet As Worksheet
Dim PCache As PivotCache
Dim PTable As PivotTable
Dim PRange As Range
Dim LastRow As Long
Dim LastCol As Long

'Insert a New Blank Worksheet
On Error Resume Next
Application.DisplayAlerts = False
Worksheets("PivotTable").Delete
Sheets.Add Before:=ActiveSheet
ActiveSheet.Name = "PivotTable"
Application.DisplayAlerts = True
Set PSheet = Worksheets("PivotTable")
Set DSheet = Worksheets("Data")

'Define Data Range
LastRow = DSheet.Cells(Rows.Count, 1).End(xlUp).Row
LastCol = DSheet.Cells(1, Columns.Count).End(xlToLeft).Column
Set PRange = DSheet.Cells(1, 1).Resize(LastRow, LastCol)

'Define Pivot Cache
Set PCache = ActiveWorkbook.PivotCaches.Create _
(SourceType:=xlDatabase, SourceData:=PRange). _
CreatePivotTable(TableDestination:=PSheet.Cells(2, 2), _
TableName:="SalesPivotTable")

'Insert Blank Pivot Table
Set PTable = PCache.CreatePivotTable _
(TableDestination:=PSheet.Cells(1, 1), TableName:="SalesPivotTable")

'Insert Row Fields
With ActiveSheet.PivotTables("SalesPivotTable").PivotFields("Year")
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("SalesPivotTable").PivotFields("Month")
.Orientation = xlRowField
.Position = 2
End With

'Insert Column Fields
With ActiveSheet.PivotTables("SalesPivotTable").PivotFields("Zone")
.Orientation = xlColumnField
.Position = 1
End With

'Insert Data Field
With ActiveSheet.PivotTables("SalesPivotTable").PivotFields ("Amount")
.Orientation = xlDataField
.Function = xlSum
.NumberFormat = "#,##0"
.Name = "Revenue "
End With

'Format Pivot Table
ActiveSheet.PivotTables("SalesPivotTable").ShowTableStyleRowStripes = True
ActiveSheet.PivotTables("SalesPivotTable").TableStyle2 = "PivotStyleMedium9"

End Sub

excelchamps.com/vba/pivot-table/

With this method there's one benefit which you can have is Power Query is real time and can extract data from the with a single refresh. And then with the VBA code, you can create a pivot table just with a single click. That means once you set-up everything, you just need two clicks.

- Puneet

 

[arnelgp]

Is there a way to get data from access to excel and then create pivot table with a code (automatically)?

you can create a Pivot or Crosstab query withing access and you can import it to excel if that is what you need.

 

[bastanu]

Have a look at my free utility that allows you to design the pivot table in Access using a custom form then export to either a new Excel file or an existing Excel template (overwriting the old data using named ranges references).

FBA Pivot Table Designer - ForestByteApps

ForestByteApps - A place for Microsoft Access code samples and utilities to make your life easier. Free Microsoft Access tools and sample VBA code

forestbyte.com

Cheers,