Excel – refer to a sheet by name
Hello,
I need to:
(1) select a sheet by name not by Item number
(2) assign a hyperlink from a cell in a named sheet to A1 of another sheet.
for (1),I tried line 61. anything below line 61 is in progress. just trying to ‘translate’ the VBA into matlab. Any suggestions would be welcomed.
Thanks
Tuli
— code start —–
function h = excel_test_2
% OPEN EXCEL APPLICATION
excellapp = actxserver(‘Excel.Application’);
% Show the Excel window
set(excellapp, ‘Visible’, 1);
% INSERT NEW WORKBOOK
W = excellapp.Workbooks.Add;
% WORKBOOKS CONTAIN WORKSHEETS
Sheets = excellapp.ActiveWorkBook.Sheets;
% Leave just one
n = Sheets.Count;
for ii=n:-1:2
Sheets.Item(ii).Delete;
end
for ii=1:20
n = Sheets.Count;
if ii>1
Sheets.Add( [], Sheets.Item(n) );
end
Sheets.Item(ii).Name = [‘test_’ num2str(ii)];
end
% Add a fourth sheet (by default, a workbook contains
% three worksheets – add a new one before [], after #3)
Sheets.Add( [], Sheets.Item(3) );
% Rename
Sheets.Item(1).Name = ‘History Long’;
Sheets.Item(2).Name = ‘History Short’;
% Set active sheet #1
Sheets.Item(1).Activate;
ActiveSheet = excellapp.Activesheet;
% Title
ActiveSheetRange = get(ActiveSheet,’Range’,’A1′);
set(ActiveSheetRange,’Value’,’Date&Time’,…
‘ColumnWidth’,15);
set(ActiveSheetRange.Font,’FontStyle’,’Bold’,’Size’,13,’Color’,128);
% Title2
ActiveSheetRange = get(ActiveSheet,’Range’,’A2′);
set(ActiveSheetRange,’Value’,’222133013′,…
‘ColumnWidth’,15);
set(ActiveSheetRange.Font,’FontStyle’,’Bold’,’Size’,13,’Color’,128);
%———— add color change and hyperlink ———————
T10 = ‘ValueJet4’
M10 = ‘test_9’
%Range(‘A2’).Select %select cell A2 in ‘Hystory Long’
%Sheets.Item(1).Activate;
Sheets(M10).Activate;
% ActiveSheetRange = get(ActiveSheet,’Range’,’A2′);
%
% Selection.Copy % copy content of A2
%
% Sheets(M10).Select %select target sheet name
%
% Sheets(M10).Name = T10 % assign new name
%
% Sheets(T10).Select
% ActiveWorkbook.Sheets(T10).Tab.ColorIndex = 3
%
% Sheets(‘History Long’).Select
% Range(‘A2’).Select
% S10 = T10 & ‘!A1’
% ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:=”, SubAddress:=S10
return
—– code end —–Hello,
I need to:
(1) select a sheet by name not by Item number
(2) assign a hyperlink from a cell in a named sheet to A1 of another sheet.
for (1),I tried line 61. anything below line 61 is in progress. just trying to ‘translate’ the VBA into matlab. Any suggestions would be welcomed.
Thanks
Tuli
— code start —–
function h = excel_test_2
% OPEN EXCEL APPLICATION
excellapp = actxserver(‘Excel.Application’);
% Show the Excel window
set(excellapp, ‘Visible’, 1);
% INSERT NEW WORKBOOK
W = excellapp.Workbooks.Add;
% WORKBOOKS CONTAIN WORKSHEETS
Sheets = excellapp.ActiveWorkBook.Sheets;
% Leave just one
n = Sheets.Count;
for ii=n:-1:2
Sheets.Item(ii).Delete;
end
for ii=1:20
n = Sheets.Count;
if ii>1
Sheets.Add( [], Sheets.Item(n) );
end
Sheets.Item(ii).Name = [‘test_’ num2str(ii)];
end
% Add a fourth sheet (by default, a workbook contains
% three worksheets – add a new one before [], after #3)
Sheets.Add( [], Sheets.Item(3) );
% Rename
Sheets.Item(1).Name = ‘History Long’;
Sheets.Item(2).Name = ‘History Short’;
% Set active sheet #1
Sheets.Item(1).Activate;
ActiveSheet = excellapp.Activesheet;
% Title
ActiveSheetRange = get(ActiveSheet,’Range’,’A1′);
set(ActiveSheetRange,’Value’,’Date&Time’,…
‘ColumnWidth’,15);
set(ActiveSheetRange.Font,’FontStyle’,’Bold’,’Size’,13,’Color’,128);
% Title2
ActiveSheetRange = get(ActiveSheet,’Range’,’A2′);
set(ActiveSheetRange,’Value’,’222133013′,…
‘ColumnWidth’,15);
set(ActiveSheetRange.Font,’FontStyle’,’Bold’,’Size’,13,’Color’,128);
%———— add color change and hyperlink ———————
T10 = ‘ValueJet4’
M10 = ‘test_9’
%Range(‘A2’).Select %select cell A2 in ‘Hystory Long’
%Sheets.Item(1).Activate;
Sheets(M10).Activate;
% ActiveSheetRange = get(ActiveSheet,’Range’,’A2′);
%
% Selection.Copy % copy content of A2
%
% Sheets(M10).Select %select target sheet name
%
% Sheets(M10).Name = T10 % assign new name
%
% Sheets(T10).Select
% ActiveWorkbook.Sheets(T10).Tab.ColorIndex = 3
%
% Sheets(‘History Long’).Select
% Range(‘A2’).Select
% S10 = T10 & ‘!A1’
% ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:=”, SubAddress:=S10
return
—– code end —– Hello,
I need to:
(1) select a sheet by name not by Item number
(2) assign a hyperlink from a cell in a named sheet to A1 of another sheet.
for (1),I tried line 61. anything below line 61 is in progress. just trying to ‘translate’ the VBA into matlab. Any suggestions would be welcomed.
Thanks
Tuli
— code start —–
function h = excel_test_2
% OPEN EXCEL APPLICATION
excellapp = actxserver(‘Excel.Application’);
% Show the Excel window
set(excellapp, ‘Visible’, 1);
% INSERT NEW WORKBOOK
W = excellapp.Workbooks.Add;
% WORKBOOKS CONTAIN WORKSHEETS
Sheets = excellapp.ActiveWorkBook.Sheets;
% Leave just one
n = Sheets.Count;
for ii=n:-1:2
Sheets.Item(ii).Delete;
end
for ii=1:20
n = Sheets.Count;
if ii>1
Sheets.Add( [], Sheets.Item(n) );
end
Sheets.Item(ii).Name = [‘test_’ num2str(ii)];
end
% Add a fourth sheet (by default, a workbook contains
% three worksheets – add a new one before [], after #3)
Sheets.Add( [], Sheets.Item(3) );
% Rename
Sheets.Item(1).Name = ‘History Long’;
Sheets.Item(2).Name = ‘History Short’;
% Set active sheet #1
Sheets.Item(1).Activate;
ActiveSheet = excellapp.Activesheet;
% Title
ActiveSheetRange = get(ActiveSheet,’Range’,’A1′);
set(ActiveSheetRange,’Value’,’Date&Time’,…
‘ColumnWidth’,15);
set(ActiveSheetRange.Font,’FontStyle’,’Bold’,’Size’,13,’Color’,128);
% Title2
ActiveSheetRange = get(ActiveSheet,’Range’,’A2′);
set(ActiveSheetRange,’Value’,’222133013′,…
‘ColumnWidth’,15);
set(ActiveSheetRange.Font,’FontStyle’,’Bold’,’Size’,13,’Color’,128);
%———— add color change and hyperlink ———————
T10 = ‘ValueJet4’
M10 = ‘test_9’
%Range(‘A2’).Select %select cell A2 in ‘Hystory Long’
%Sheets.Item(1).Activate;
Sheets(M10).Activate;
% ActiveSheetRange = get(ActiveSheet,’Range’,’A2′);
%
% Selection.Copy % copy content of A2
%
% Sheets(M10).Select %select target sheet name
%
% Sheets(M10).Name = T10 % assign new name
%
% Sheets(T10).Select
% ActiveWorkbook.Sheets(T10).Tab.ColorIndex = 3
%
% Sheets(‘History Long’).Select
% Range(‘A2’).Select
% S10 = T10 & ‘!A1’
% ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:=”, SubAddress:=S10
return
—– code end —– control excel hyperlink MATLAB Answers — New Questions
