Using actxserver to refer cells between worksheets

10 次查看（过去 30 天）
Sid
Sid 2015-8-4
回答： Sid 2015-8-4
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.

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

采纳的回答

Sid
Sid 2015-8-4
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.

更多回答（0 个）

类别

在 Help Center 和 File Exchange 中查找有关 Spreadsheets 的更多信息

标签

Community Treasure Hunt

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

Start Hunting!

Translated by