MATLAB: Multiple import/edit of xls files

importing excel data

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

Best Answer

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)