How to write and save in an excel file?

I want to copy data from a specific column of each sheet from multiple excel sheets in an excel file and write it to an output excel file. out put excel file should look like, the first column contains the data from first sheet, the second contain data from second sheet and so on. each column of out put file contain the sheet name in the first row. also I am having many excel files so the output file shout have the name as the parent file or it can be created in a folder with the name of parent excel file. My code looks like this but does write the output.

clear;
clc;
XLfile = dir();
XLfile([XLfile.isdir]) = [];  
number_of_files = length(XLfile);
for index = 1:number_of_files
    filename = XLfile(index).name;
    [status, sheetnames] = xlsfinfo(filename);
    if isempty(status)
      warning( sprintf('Skipping non-excel file: "%s"', filename));
      continue;
    end
    fig = figure();
    nsheet = min( length(sheetnames), 10 );  %ignore past 10
    if nsheet < 10
      warning( sprintf('Only %d sheets in file "%s"', nsheet, filename));
    end
    for i = 1:nsheet
      sheetname = sheetnames{i};
      y = xlsread(filename, sheetname, 'E2:E10000');

      end   
      xlswrite(filename,A);
  end

[EDITED, Jan, Code formatted]

[Jan]

I'd expect an error message, because in xlswrite(filename,A) the variable A has not been defined.

Answer 1

[Adewale Obaro]

THIS ONE LINE CODE WRITES ARRAY DATA (OR VARIABLE) INTO A SPREADSHEET AND IT WORKS LIKE MAGIC

xlswrite('myData .csv', Gc,'C1:C5','sheet1') OR xlswrite('myData .xlsx', Gc,'C1:C5','sheet1')

NOTE: myData is the name of the file which you intend to name the file

cvs or xlsx is the spreadsheet extension

Gc = [20.4 23.67 11.49 33.17 22.65] % The variable or array which you want to save

C1 is the starting cell on which you want the saving to start from

C5 is the ending cell to which you want the data saving to end

sheet1 is the sheet name you want to save onto, on the spreadsheet

Please like this TO ENCOURAGE ME if it help you because this is MY FIRST CONTRIBUTION ON MATHWORKS

Answer 2

[dpb]

I'd suggest a few changes--

XLfile = dir();

Why not use wildcard to only get .xls? files? Then can get rid of the other logic...

XLfile = dir('*.xls?');  % Or .xls or .xlsx if is specific form
number_of_files = length(XLfile);
for index = 1:number_of_files
  filename = XLfile(index).name;
    ...immaterial to problem lines elided...
   A=zeros( sscanf('E10000','E%d')-sscanf('E2','E%d')+1,nsheet);  % size of read column by nsheets
  for i = 1:nsheet
    A(:,i) = xlsread(filename, sheetnames{i}, 'E2:E10000');
  end   
  xlswrite(OutputFileName,A);
end

Need to then create the OutputFileName as desired before beginning the loop.

[Saeedullah Mandokhail]

It does not work.

[dpb]

So debug... :)

What, specifically is the symptom and error message and the precise code with same?

[Saeedullah Mandokhail]

Giving no error and also no output. Screenshot after run is attached for your information.

[Image Analyst]

Where or how did you attach a screenshot? Did you use the green and brown frame icon like you were supposed to? Also, attach one of your workbooks so we can try something. Come on, make it easy for us to help you, not hard.

[Saeedullah Mandokhail]

I have attached two of my worksheets. I have deleted number of sheets (Layer4, Layer5,..) from both the attached excel files because of size limit to upload here. I want that my code copy data in column E from each sheet (up to sheet 3) and print it in one sheet in an out put excel file which should have name (original excel file name-pwp). thanks

[dpb]

Did you implement the suggestions I outlined above? If so, where's the updated code and what's the issue?