Extract data from specific cells across multiple Excel sheets

Hi, I have a xlsx file and am looking to extract data from multiple specific cells across multiple spreadsheets in order to then run some stats on it.

What I want to do:

1. Read the same specific cells containing numetrical data across multiple sheets (i.e A1,B1,C1 in sheet 1, A1,B1,C1 in sheet 2)
2. Assign this data to a new variable (i.e all A1 cells = var1, all B1 cells = B2 etc.)
3. Each line of the array to be assigned an ID
4. Export this to a new excel spreadsheet

There are also some specific text cells where that ideally I would like to read in text also, but this isn't essential.

If anyone could assist in helping with this I would be really grateful

Thanks

Answer 1

[drummer]

Dear @Liam Jones, could you please share some of what you have worked on so far?

You can guide yourself through this link.

Let us know if you nailed it (or not).

[Liam]

Hi @drummer thanks for responding, I was looking at that documentation but just not getting it first time round. I managed to get what I was after digesting everything a bit more. Leaving it here in case it helps anyone

clear, close all, clc

data =  table();
tempData = table();
nSheets = 3;
sheetNames = {'Sheet 1','Sheet 2','Sheet 3'};
file = 'MyFile.xlsx';

for i = 1:nSheets
    name = sheetNames{i};
    tempData.ID = i;
    tempData.Var1 = readtable(file,'Range','A1','Sheet',name,'ReadVariableNames',false);
    tempData.Var2 = readtable(file,'Range','A2','Sheet',name,'ReadVariableNames',false);
    tempData.Var3 = sum(table2array((readtable(file,'Range','D12:D15','Sheet',Name,'ReadVariableNames',false))));
    Data = [data;tempData];
end

Data = splitvars(Data) 
writetable(Data,'Data.xls')