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.
Best Answer