I am teaching a course in Microsoft Excel, using Excel 2019. As part of an exam in this course, I ask students to create pivot tables. I will then need to grade these exams.
Each student submits an excel file that contains multiple worksheets with various formulas, pivot tables, and charts. I currently open every file and examine every worksheet to determine if they follow instructions. However, I would like to aggregate data from all the excel files in a given folder into a single file to simplify the grading process.
I am currently looking at using Power Query to pull cell contents from every file in the folder, and then use the formulatext() function and direct cell references to extract out pertinent cells from those files. I am using conditional formatting to mark if they match the expected values. This works great for general formulas, and I can use the cell() function to get information about formatting of individual cells. However, I have not found a way to determine the composition of their pivot tables, nor the properties of their charts.
For example, I will provide a blank pivot table named Pivot1 on sheet Q5 that references data from elsewhere in the workbook. I would like to determine which fields they chose to include in the rows, columns, values, and filters sections of that pivot table. I would also like to determine the calculation behind a calculated field that I ask them to create (which has a known name).
I suspect that VBA could do this, and I may resort to that, but I would prefer a non-VBA solution if one is available. In attempting to find my own solution, I've examined the getPivotData() function as well as indirect(), choose(), and index(). None of these seem to help.
If a non-VBA solution is available, what function(s) should I be looking at? If VBA is the only answer, can you show me an example of code to extract the names of the fields in each of the locations in a pivot table named "Pivot1" located at 'Sheet1'!$A$1 ?
