Exporting Data to Excel and forcing Pivot Table Columns Even if Data Doesn't Exist

[dashiellx]

Good morning.




I have a routine that is exporting data to Excel and creating a pivot table.


The column header for the pivot is "Age Group" and should always be "<5 Days", "5 - 10 Days", "11 - 20 Days", and ">20 Days" even if there is no data for the age group.


Excel obviously doesn't order those columns correctly, so I have the following code to make sure they are in the correct order

.PivotTables(spivTableName).PivotFields("AgeGroup").ShowAllItems = True
.PivotTables(spivTableName).NullString = "0"
.PivotTables(spivTableName).PivotFields("AgeGroup").PivotItems("<5 Days").Position = 1
.PivotTables(spivTableName).PivotFields("AgeGroup").PivotItems("5 - 10 Days").Position = 2
.PivotTables(spivTableName).PivotFields("AgeGroup").PivotItems("11 - 20 Days").Position = 3
.PivotTables(spivTableName).PivotFields("AgeGroup").PivotItems(">20 Days").Position = 4

Which works great as long as there is data for each of the columns; but if there is no data for one of the Age Groups, a 'Run-Time error 1004 Unable to get the PivotItem property of the PivotFields' is raised.


How can I set the column headers to show even for no data in the data source range for that column?

[Minty]

The easiest way would be to ensure the export includes a blank data record for all those groups?
I don't think you can force the Pivot to include missing data.

The other answer would be to pivot the data in Access, fix the column headers and export the pivoted (Crosstab query) results directly.

[Welshgasman]

I was wondering why they did not use a crosstab?

[dashiellx]

Exporting a crosstab as the summary loses all of the Excel pivot table functions.

I have actually done the export crosstab, make links, etc... to mimic the pivot table functionality, but that takes exponentially longer. You also loss the ability to add slicers and other filters.

[Gicu]

As Minty suggested, try to change the source query in Access to get you a 0 if no records for a certain category (using the Nz() function might be the easiest). A similar approach is used here but in Excel:
https://www.youtube.com/watch?v=mVabPX6quQ0
A have a free utility to do pivot tables from within Access, might come handy:
https://www.forestby.mywhc.ca/ms-acc...able-designer/
Cheers,

[alansidman]

You could use Power Query to easily bring the data into the PQ Editor and then close and load to an excel pivot table.

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.

[dashiellx]

The following seems to be working as expected.

intCol = 1.PivotTables(spivTableName).PivotFields("AgeGroup").ShowAllItems = True
.PivotTables(spivTableName).NullString = "0"
On Error Resume Next
.PivotTables(spivTableName).PivotFields("AgeGroup").PivotItems("<5 Days").Position = intCol
If Err.Number = 1004 Then
	intCol = intCol
Else
	intCol = intCol + 1
End If
.PivotTables(spivTableName).PivotFields("AgeGroup").PivotItems("5 - 10 Days").Position = intCol
If Err.Number = 1004 Then
	intCol = intCol
Else
	intCol = intCol + 1
End If
.PivotTables(spivTableName).PivotFields("AgeGroup").PivotItems("11 - 20 Days").Position = intCol
If Err.Number = 1004 Then
	intCol = intCol
Else
	intCol = intCol + 1
End If
.PivotTables(spivTableName).PivotFields("AgeGroup").PivotItems(">20 Days").Position = intCol
If Err.Number = 1004 Then
	intCol = intCol
Else
	intCol = intCol + 1
End If
On Error GoTo 0