Multiple import/edit of xls files

5 views (last 30 days)
Az
Az on 19 Jul 2011
Dear All, I have around 1400 xls files with 2 sheets in each file.(with the filenamelike 50005100.xls but without a pattern) Here is what I want to do: 1) delete the first sheet; 2) insert a column with header(ID) and then fill in the column with the file name in the second sheet. then export the file to csv(I know how to do that) I need to do this to all of them one by one. Can anyone help me with this? Million thanks. Az

Accepted Answer

Chirag Gupta
Chirag Gupta on 19 Jul 2011
This should work! You might need to tweak it slightly!
hExcel = actxserver('Excel.Application');
hExcel.visible = 1; % If you want Excel visible.
hExcel.DisplayAlerts = false; % Avoid excel warning popups
for i:length(filenames) % Assuming filenames contains the 1400 xls files
Wkbk = hExcel.Workbooks.Open(fullfile(pathToFile, filenames{i})); % Opens Excel file
Sheets = Wkbk.Sheets; % Get the Sheets
Sheets.Item(1).Delete; % Deletes the first sheet
Wkbk.ActiveSheet.Cells.Item(1).EntireRow.Insert; Assuming you want to insert a row and not a column
Wkbk.ActiveSheet.Cells.Range('A1').Value = filenames{i}; % Insert Filename
Wkbk.Save; Save xls file
Wkbk.SaveAs('C:\MatlabWork\Answers\Test.csv', 6); Save as .csv (file format for csv enum is 6)
Wkbk.Close;
end
hExcel.Quit;
hExcel.delete;
  7 Comments
Walter Roberson
Walter Roberson on 19 Jul 2011
Do you want to work with all of the .xls files in the directory? If so then you can use dir('*.xls') to get file information.
Az
Az on 19 Jul 2011
yes,thanks Walter. I'll try that.

Sign in to comment.

More Answers (1)

ricco
ricco on 30 Nov 2011
I have a question which is similar to this. I have a code which loads data from several spreadsheets into excel:
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
Where the loop imports the workbook entitled 'Ble min' from different spreadsheets. How could I use actxserver to do the same i.e. to speed the process up.
thanks

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!