MATLAB Answers

Matlab, Excel and validation of data

15 views (last 30 days)
Ortinomax
Ortinomax on 13 May 2015
Answered: Enes Uk on 21 Jan 2019
Hello, I wanted to create some rule of validation on some cells in Excel documents. I used the macro-recording to see which methods, parameters are used. i get that :
Sub Macro1()
'
' Macro1 Macro
'
'
Columns("C4:C8").Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=Liste_choix!$C4:$C12"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
End Sub
I tried to do the same on Matlab :
xslx = actxserver('Excel.application');
xslx.Visible = false;
myTab = xslx.Workbooks.Open(fullfile(pwd,'Liste simu Synthèse'));
sheet = xslx.Worksheets.Item(nameTab{1});
r(1)=sheet.Range('C4');
r(2)=sheet.Range('C8');
Range = sheet.get('Range', r(1), r(2));
myRule=Range.Validation;
myRule.Delete();
myRule.Add(3);
But it won't work :
??? Error: Object returned error code: 0x800A03EC
Error in ==> trash at 27
myRule.Add(3);
The arguments doesn't seem to be the problem here, I teted this :
>> myRule.methods
Methods for class Interface.Microsoft_Excel_15.0_Object_Library.Validation:
Add Modify delete events invoke release set
Delete addproperty deleteproperty get loadobj saveobj
>> myRule.Add()
??? No method 'Add' with matching signature found for class 'Interface.Microsoft_Excel_15.0_Object_Library.Validation'.
I don't understand, is Add a method or not ?

  0 Comments

Sign in to comment.

Answers (3)

Enes Uk
Enes Uk on 21 Jan 2019
% Create an Excel object
e = actxserver('Excel.Application');
% Add a workbook
eWorkbook = e.Workbooks.Add;
e.Visible = 1;
% Make the first sheet active.
eSheets = e.ActiveWorkbook.Sheets;
eSheet1 = eSheets.get('Item',1);
eSheet1.Activate;
% Put MATLAB data into the worksheet.
activeSheet = e.Activesheet;
eActivesheetRange = e.Activesheet.get('Range','A1:A4');
eActivesheetRange.Value = A;
% % Read the data back into MATLAB, where array B is a cell array.
% eRange = e.Activesheet.get('Range','A1:B2');
% B = eRange.Value;
% Copy data list and create data validation
activeSheet.Range('A1:A4').Copy;
activeSheet.Range('A5').Validation.Add('xlValidateList',1,1,'=A1:A4')
% Save the workbook in a file.
eWorkbook.SaveAs('myfile.xlsx');
% If the Excel program displays a dialog box about saving the file, select the appropriate response to continue.
% If you saved the file, then close the workbook.
eWorkbook.Saved = 1;
Close(eWorkbook);
% Quit the Excel program and delete the server object.
Quit(e);
delete(e);

  0 Comments

Sign in to comment.


chaymaa slimani
chaymaa slimani on 10 Sep 2018
Hello ! I have the same problem as you, I wonder if you could solve it. If yes, can you share with me the solution please? Thank you in advance

  0 Comments

Sign in to comment.


Ranjith Kumar
Ranjith Kumar on 7 Jan 2019
Hai, try the below one.
xslx.Range("C4:C8").Select;
xslx.Selection.Validation.Delete; % if xslx not works, then use the sheet identifier
xslx.Selection.Validation.Add('xlValidateList',1,1,'=Liste_choix!$C4:$C12')

  0 Comments

Sign in to comment.

Sign in to answer this question.


Translated by