MATLAB Answers

Using actxserver to refer cells between worksheets

19 views (last 30 days)
Sid
Sid on 4 Aug 2015
Answered: Sid on 4 Aug 2015
Hi everyone,
The question relates to the use of the use of the actxserver for an excel application.
How does one use actxserver to refer a cell from another sheet, similar to
ActiveCell.FormulaR1C1 = "=Sheet2!R[1]C"
in an excel macro?
So far, what I have is the following:
clc; close all; clear all;
excel = actxserver('excel.application');
excel.Visible = 1; % Make it visible
% Make excel not display alerts
set(excel,'DisplayAlerts',0);
% Using an excel template - not necessarily needed for the question at hand?
ExcelFile =fullfile(pwd,'helloWorld.xlsx');
%open the excel file
wkbk = excel.Workbooks.Open(ExcelFile); %This file has a sheet named "1" which has some formating
wksheet = wkbk.Worksheets.Item(1); % Choose desired sheet
for n = 2:6
%make a copy of the template sheet
wksheet.Copy(wksheet); %this will create a sheet called "1 (2)" and places it before "1"
newSheet=wkbk.Worksheets.Item(n-1); %get a handle to this copied sheet
invoke(newSheet,'Activate')
ActivesheetRange_Title = get(newSheet,'Range','B2');
ActivesheetRange_Title.Value = ['Board' ' ' num2str(n-1)];
ActivesheetRange_Date = get(newSheet,'Range','C3');
ActivesheetRange_Date.Value = datestr(now,'yyyymmdd');
ActivesheetRange_Date.Interior.ThemeColor = 5;
ActivesheetRange_Date.cells.EntireColumn.AutoFit(); % Autofit column to contents
ActivesheetRange_sessionName = get(newSheet,'Range','C4');
ActivesheetRange_sessionName.Value = datestr(now,'HH00');
ActivesheetRange_ImageName = get(newSheet,'Range','C5');
ActivesheetRange_ImageName.Value = ['Board' ' ' num2str(n-1)];
newSheet.Name= ['Board' ' ' num2str(n-1)]; %rename it with a new name
end
% Delete the template sheet
sheetToDelete = excel.ActiveWorkbook.Worksheets.Item(n);
invoke(sheetToDelete,'Delete');
% summary
summarySheet = excel.ActiveWorkbook.Worksheets.Item(n);
summarySheet_dateRange = get(summarySheet,'Range','C2');
summarySheet_dateRange.Value = datestr(now,'yyyymmdd');
summarySheet_timeRange = get(summarySheet,'Range','C3');
summarySheet_timeRange.Value = datestr(now,'HH00');
newWorkBookName = fullfile(pwd,[datestr(now,'yyyymmdd_HH00') '.xlsx']);
% Add the last bit of fomatting to the summary sheet.
workbookSheetCount = wkbk.worksheet.count;
summarySheetAddress = excel.ActiveWorkbook.Worksheets.Item(workbookSheetCount);
for m = 1:(workbookSheetCount-1)
% Worksheet to extract
worksheetToExtract = excel.ActiveWorkbook.Worksheets.Item(m);
worksheetToExtract_value = get(worksheetToExtract,'Range', 'C7');
% Summary sheet
sheetRangeToChange_contrastVal = get(summarySheetAddress,'Range',['C' num2str(m+5)]);
sheetRangeToChange_contrastVal.FormulaR1C1 = worksheetToExtract_value;
end
What I need help with is from Line 96:107, where I am trying to set the relations between the sheets.
Any help/direction would be most appreciated.
Thanks in advance.

Accepted Answer

Sid
Sid on 4 Aug 2015
Quite embarrassing, but I did manage to solve the problem, so considering the matter closed for now.
For anyone who's as poor as I am with VBA, the key is to refer to the FormulaR1C1 documentation.
sheetRangeToChange_contrastVal.FormulaR1C1 = '=Sheet2!R[1]C'
I'm guessing that there is a much more elegant way to do this, which I would love to learn. But I figured I will leave it here in case anyone else finds it useful.

More Answers (0)

Community Treasure Hunt

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

Start Hunting!