How do I change the save file format in excel using activeX in Matlab

I currently have the following code.

file = 'C:\example\somefile.xlsx';

sheet = 'Sheet1'; %can be name or numeric index

row = 1;

excel = actxserver('Excel.Application');

workbook = excel.Workbooks.Open(file);

worksheet = workbook.Worksheets.Item(sheet);

worksheet.Rows.Item(row).Delete;

workbook.Save;

excel.Quit;

%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%

I want to change the default

"Save files in this format" : Excel Workbook (*.xlsx)

to

"Save files in this format" : CSV (Comma delimited)(*.csv)

Answer 1

[Fangjun Jiang]

Worked like a charm, in R2018b, MS Office 2016.

file =  'Book1.xlsx';
sheet = 'Sheet1'; 
excel = actxserver('Excel.Application');
workbook = excel.Workbooks.Open(fullfile(pwd,file));
worksheet = workbook.Worksheets.Item(sheet);

xlCSV=6; %this constant is defined by MS Excel
ExcelApp.DisplayAlerts=false;% ignore prompt
workbook.SaveAs(fullfile(pwd,'Book2'),xlCSV);
excel.Quit;

[Matthew Isaman]

The save did work but that wasnt my original problem. When I run the code in Matlab there is a pop up that states if I want to save the file and if I want to keep the same format. I dont want those to pop up and to just save it. So when I run my code everything will be changed and saved without any pop ups.

Thats why when I change the options in excel manually it works fine without any pop ups. But I wanted the default option on there and to be able to change those options through Matlab.

[Fangjun Jiang]

I don't know if you can change the default save option. But the statement ExcelApp.DisplayAlerts=false will get rid of that pop dialog.

[Matthew Isaman]

I used that line of code to remove the pop ups and they still showed up. I think I am just going to resort back to just changing the save option manually in the beginning before I run the code. Thanks for the help anyway!

Answer 2

[Matthew Isaman]

No it didnt work. I'm getting an error stating

Undefined function or variable 'filename'

Its not liking my filename or something. My original file is already a csv file but when I go to save it is asks is I want to save the file and if I want to keep the same formatting. I went through and change the save file options to save as CSV delimited and the pop up went away. I just couldnt figure out how to perform that task in matlab. To be able to change the save option.

Answer 3

[Matthew Isaman]

I also tried it using ".csv" thinking that might be the issue.

But I still would get and error saying

"Underfined variable "somefile" or class "somefile.csv"

[Fangjun Jiang]

You need to do some experiments. See the updated answer.