reading multiple excel files

I have afolder with 150 excel file and I want to read all of them one after another and save them in last in another excel sheet

[Azzi Abdelmalek]

Can you give more details about your data in each Excell file

[Ahmed Khalifa]

every excel file has one sheet with 4 columns A B C D

Answer 1

[Azzi Abdelmalek]

folder='E:\fold1'
d=dir(folder)
e={d.name}
f=e(~cellfun(@isempty,regexp(e,'.+(?=\.xlsx)','match')))
for k=1:numel(f)-1
  data{k,1}=xlsread(f{k})
end
M=cell2mat(data)
xlswrite('new_file',M)

[Ahmed Khalifa]

Thanks soooo much alittle nother question ,sorry if i have many excel files with 11 columns for every one and I want to read only column 5,6,7 and write them for all files in only one single file.

Answer 2

[Image Analyst]

Don't use xlsread() for this unless you're prepared to wait a very long time. If you have Windows, you can use ActiveX and get this done in a few seconds. With xlsread(), each time you call it, it has to launch Excel, open your workbook, haul over the data (probably using ActiveX), and finally shut down Excel. With ActiveX, you launch Excel just once, and shut it down just once, so it will be about 150 times faster for 150 workbooks. I attach a demo on how to read and write Excel files with ActiveX. If I have more than 2 files, I never use xlsread or xlswrite - I use ActiveX. If I want more precise control over formatting (decimal places, alignment, coloring, borders, etc.) I use ActiveX, or use a template. It's not hard to learn so don't be afraid - it's just like any other object oriented programming you're used to.

[Ahmed Khalifa]

well, thank you first but I need it to be in matlab cause it will be asmall step in alarge program so it's just for sequence of the whole job.

[Image Analyst]

I guess you didn't run my demo and notice that it is in a MATLAB m-file and is, in fact, MATLAB code. It's just as much MATLAB code as calling xlsread() or xlswrite() is. Here is a link to MATLAB documentation on actxgetrunningserver http://www.mathworks.com/help/matlab/ref/actxgetrunningserver.html?s_tid=srchtitle You see, it's a MATLAB function and you don't have to feel guilty about using it. I mean, after all, xlsread() uses it, so why can't you ?

[Maxwell MacFarlane]

I have tried using this code but all I have been able to do is create is a blank spreadsheet. Any suggestions on how to get it to read my data in?

[Image Analyst]

Maxwell: It should have worked, and it did for me when I tried it. But anyway, since I was taking another look at it, I made some improvements to it. Please download the latest version, attached here.

If you still have trouble, then attach your m-file and I'll try it.

[naty liber]

hey, there is an error saying that excel is already open but your code opens it...

[Image Analyst]

I ran it and it ran fine. The message just suggests one possible error, not necessarily the exact reason. The reason is that the Excel variable no longer exists by the time you got to the line of code that called xlswrite1. Did you perhaps set a breakpoint somewhere and then type clear or clear all? Did you shutdown Excel after the breakpoint but before calling xlswrite1()? Again, it works fine for me. If it doesn't for you then run PSR (type that into the Windows search field and run the Steps Recorder) to do screen captures and upload your zip file so I can see what you did.

[Brooke Beier]

I got a similar warning dialog. Your version of xlswrite1.m may have "Excel=evalin('base','Excel');" by default, but the demo needs "Excel = evalin('caller', 'Excel');".

The difference between 'base' and 'caller' makes the demo run for me.

[Image Analyst]

The version I use has caller, not base. I'm attaching it.