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.
采纳的回答
更多回答（0 个）
类别
在 帮助中心 和 File Exchange 中查找有关 Spreadsheets 的更多信息
Translated by 
