I'm trying to extract data from excel files in a specific way.
Essentially, I want to loop through each file, get the contents of column A (Area), rename it from "Area" to the name of the sheet, then paste the data onto a new sheet or create a struct/table/cell array of all the column A data from all the spreadsheets. I'm not sure if this is possible but am encountering some issues with my code (which is far from complete). May I please ask for some help in tackling this issue?
pathVar = [subFolders(i).folder '\' subFolders(i).name];
excelPath = [pathVar '\Results.xls'];
nameString = sheetnames(excelPath);
nameString = extractAfter(nameString, ' ');
nameCell(i) = nameString;
tempdata = readtable(excelPath, "VariableNamingRule","preserve");