Matlab, Excel and validation of data

9 次查看（过去 30 天）
Ortinomax
Ortinomax 2015-5-13
回答： Enes Uk 2019-1-21
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 ?

请先登录，再回答此问题。

回答（3 个）

Enes Uk
Enes Uk 2019-1-21
% 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);
chaymaa slimani
chaymaa slimani 2018-9-10
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
Ranjith Kumar
Ranjith Kumar 2019-1-7
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')

类别

在 Help Center 和 File Exchange 中查找有关 Use COM Objects in MATLAB 的更多信息

标签

Community Treasure Hunt

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

Start Hunting!

Translated by