Using actxserver to refer cells between worksheets

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.

Answer 1

[Sid]

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.