Excel - refer to a sheet by name

Question

Naftali Herscovici 2011-3-11

0
链接

此问题的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/3012-excel-refer-to-a-sheet-by-name

编辑： Walter Roberson 2024-7-20，21:32

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 -----

1 个评论
显示 -1更早的评论隐藏 -1更早的评论

Umar 2024-7-20，4:05

编辑：Walter Roberson 2024-7-20，21:32

在 MATLAB Online 中打开

Hi Naftali,

It sounds like you are trying to solve a task that involves selecting a sheet by name and assigning a hyperlink from a cell in a named sheet to cell A1 of another sheet in Matlab. To solve this task, you need to define the sheetName variable with the name of the sheet you want to select. Use find and contains functions to find the index of the sheet by name. Activate the sheet using sheets.Activate(sheetIndex).Assign a Hyperlink: Set the hyperlinkAddress variable to the address you want to link to (e.g., 'Sheet1!A1').Define the range variable as the cell in the named sheet where the hyperlink will be placed.Use ActiveSheet.Hyperlinks.Add to create a hyperlink in the specified cell linking to the address provided.Here is a snippet code,

% Selecting a Sheet by Name
sheetName = 'Sheet2'; % Name of the sheet to select
% Find the index of the sheet by name
sheetIndex = find(contains({sheets.Name}, sheetName));
% Activate the sheet by index
sheets.Activate(sheetIndex);
% Assigning a Hyperlink
% Hyperlink address to link to
hyperlinkAddress = 'Sheet1!A1';
% Cell in the named sheet containing the hyperlink
range = 'A2';
ActiveSheet.Hyperlinks.Add(ActiveSheet.Range(range), '', hyperlinkAddress, '', 'Click here');

The above code snippet will help you accomplish your task by selecting a sheet by name and assigning a hyperlink from a cell in one sheet to cell A1 of another sheet in Matlab. I hope this answers your question.

请先登录，再进行评论。

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