using actxserver

I have imported data from excel into matlab where I have several spreadsheets. I have used:

files = dir('*.xls');
%read data from excel into matlab
for i=1:length(files);
    File_Name{i}=files(i,1).name;%Removes the file names from 'files'
    [num{i},txt{i},raw{i}] = xlsread(File_Name{i},'Ble min');
end

So, each cell refers to each spreadsheet. Is there a quicker way of doing this? I attempted using actxserver, but unfortunately I dont know enough matlab to be able to do it. I tried

filenames = dir('*.xls');
%read data from excel into matlab

hExcel = actxserver('Excel.Application');
hExcel.visible = 1; % If you want Excel visible.
hExcel.DisplayAlerts = false; % Avoid excel warning popups
for i=1:length(filenames);
   Wkbk = hExcel.Workbooks.Open(fullfile(pathTofiel,...
       filenames{i})); % Opens Excel file
   Sheets = Wkbk.Sheets('Bass min'); % Get the Sheets
   Wkbk.Save; Save xls file
   Wkbk.SaveAs('out.csv', 6); 
   Wkbk.Close;
end

hExcel.Quit;
hExcel.delete;

An error appears 'Cell contents reference from a non-cell array object'. Can anyone point me in the right direction?

thanks

Answer 1

[Fangjun Jiang]

filenames returned by dir() is a structure array, not a cell array. Try it alone to understand how filenames look like. To get the file name, you will need to use filenames(i).name

Excel COM server can be much faster than xlsread() when you have multiple files to open/process. See this post for some leads.

http://www.mathworks.com/matlabcentral/answers/21554-xlsread-for-multiple-sheets

[ricco]

many thanks. Will the following line return all of the data in the sheet 'Bass min' or will I need to alter this.

Sheets = Wkbk.Sheets('Bass min'); %

I see many examples of coding for this type of problem where the user has to specify the range from the excel worksheet!

[Fangjun Jiang]

You'll probably need to use:

MyRange=Sheets.range('A1:C1') or

MyRange=Sheets.UsedRange;

Then:

Data=MyRange.Value;

Answer 2

[Image Analyst]

This is not valid syntax:

Wkbk.Save; Save xls file

You'd need a % after the semicolon.

I'd recommend that you use csvwrite() if you want to save a csv file. It should be pretty fast, probably even faster than having Excel do it. But you need to get your data into an array. Your bottom chunk of code doesn't do anything - it merely opens a file and then saves it immediately, and then tries to save who-know-what (I guess the sheet called "Bass min"?) into a csv file, which of course won't work the way you have it because you're not saving any csv data - you're trying to save a complicated workbook as a simple text file which it doesn't know how to convert.