How can I properly pass excel objects to functions?
3 views (last 30 days)
Show older comments
I am accessing an excel sheet from MATLAB to read in some data, do some processing and edit the sheet. I had an original approach that did everything in a single script. Some of the individual operations were useful so I decided to break them out into functions. Now any changes I make to the file aren't saved?
% Check inputs
p = inputParser;
addRequired(p,'optIndexList', @(x) numel(x) == numel(flagList))
addRequired(p,'flagList', @(x) numel(x) == numel(optIndexList))
addRequired(p,'svnInfo')
parse(p,optIndexList,flagList,svnInfo)
% Open excel
e = actxserver('excel.application');
eW = e.Workbooks;
eF = eW.Open(filePath);
eS = eF.Sheets.get('Item','optIndexes');
eS.Activate;
% Find columns of interest
endCol = eS.Range('A1').End('xlToRight').Column;
colNames = eS.Range(['A1:' xlscol(endCol) '1']).Value;
[~,colPos] = ismember('svnVersion',colNames);
svnCol = xlscol(colPos);
[~,colPos] = ismember('optIndex',colNames);
indexCol = xlscol(colPos);
% Get optIndex column
allOptIndexes = getAllOptIndexes('indexCol',indexCol);
% Create svn string
svnVerString = ['URL: ' svnInfo.url ', rev: ' num2str(svnInfo.revision)];
nOpts = length(optIndexList);
for iOpt = 1:nOpts
optIndex = optIndexList(iOpt);
[~,rowPos] = ismember(optIndex,allOptIndexes);
svnCellAddress = [svnCol num2str(1+rowPos)]; % svnCol for optIndex of interest we're going to edit
switch flagList(iOpt)
case 0
% SVN verison was unchanged
% Do nothing
continue
case -1
% SVN version was changed but the code is not working
% Colour red
eS.Range(svnCellAddress).Interior.Color = RGB([255, 199, 206]);
eS.Range(svnCellAddress).Font.Color = RGB([156, 0, 6]);
case 1
% SVN version was changed and the code is working
% Colour red and overwire cell contents
eS.Range(svnCellAddress).Interior.Color = RGB([198, 239, 206]);
eS.Range(svnCellAddress).Font.Color = RGB([0, 97, 0]);
eS.Range(svnCellAddress).Value = svnVerString;
end
end
% Auto-fit column
eS.Range([svnCol '1']).EntireRow.AutoFit;
% Save and close the sheet
eF.Save;
eF.Close; % close the file
e.Quit; % close Excel entirely
end
Functions thats are called.
function allOptIndexes = getAllOptIndexes(varargin)
% Manage flexible inputs
p = inputParser;
addParameter(p,'colNames',nan)
addParameter(p,'indexCol',nan)
parse(p,varargin{:})
e = actxserver('excel.application');
eW = e.Workbooks;
eF = eW.Open(filePath);
eS = eF.Sheets.get('Item','optIndexes');
eS.Activate;
if ismember('indexCol',p.UsingDefaults)
if ismember('colNames',p.UsingDefaults)
endCol = eS.Range('A1').End('xlToRight').Column;
colNames = eS.Range(['A1:' xlscol(endCol) '1']).Value;
else
colNames = p.Results.colNames;
end
[~,colPos] = ismember('optIndex',colNames);
indexCol = xlscol(colPos);
else
indexCol = p.Results.indexCol;
end
% Get optIndex column
endRow = eS.Range([indexCol '2']).End('xlDown').Row;
allOptIndexes = cell2mat(eS.Range([indexCol '2:' indexCol num2str(endRow)]).Value);
% Close excel
eF.Close; % close the file
e.Quit; % close Excel entirely
If I do lots of these operations, the result is quite slow as excel is always being opened and closed.
e = actxserver('excel.application');
eW = e.Workbooks;
eF = eW.Open('O:\Engineering\Motor_Modelling\Optimisations\optIndexRegister.xlsb');
eS = eF.Sheets.get('Item','optIndexes');
eS.Activate;
I trield to pass eF/eS to the second function e.g.
getAllOptIndexes(eF)
However, if I do this the changes I make to the excel are no longer saved? It's as if there are two instances of the file open. Can anyone explain this? How can I get around the problem of opening and closing excel?
Many thanks,
Ryan
0 Comments
Answers (0)
See Also
Categories
Find more on Spreadsheets in Help Center and File Exchange
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!