I am using xlswrite to write data from Matlab to Excel. I would like to name the range that the data is written to. I have googled this and searched the help file and cannot find any information on this. There are Matlab scripts to access the range names, but none to actually create names in Matlab for the Excel range. Does anyone have any tips or insight into this issue?
1 Answer
This MATLAB code solved the issue. It took some digging and experimentation so I figured I would share it:
function NameRange(r2,c2,b2,RangeName,SummaryFileName,SheetName)
% --------------------
% Function: NameRange
% Description:
% This script names a specific range in Excel
%
% input:
% r2 = rows in data being written to Excel i.e. size(matrix, 2)
% c2 = columns in data being written to Excel i.e. size(matrix,1)
% b2 = the starting cell you are writing to i.e. whatever cell you put into xlswrite i.e. 'A2'
% Rangename = What you want to name the range
% SummaryFileName is the address to the file, '**FILENAME**.xls'
% Sheet Name is the worksheet name i.e. 'Doc Details'
%
% Example: NameRange(5,6,'A3','Tester1234','**FILENAME**.xls','Doc Details')
%
% Output - named range
% If you named a sheet incorrectly, this is one of the Possible Errors
% Description: The name that you entered is not valid.
%
% Reasons for this can include:
% -The name does not begin with a letter or an underscore
% -The name contains a space or other invalid characters
% -The name conflicts with an Excel built-in name or the
% name of another object in the workbook
% Help File: xlmain11.chm
% Help Context ID: 0
% ----------------------
% Create object.
ExcelApp = actxserver('Excel.Application');
% Show window (optional).
ExcelApp.Visible = 1;
% Open file located in the current folder.
ExcelApp.Workbooks.Open(SummaryFileName);
% create handle for parsing sheets
Sheets = ExcelApp.ActiveWorkBook.Sheets;
% grab the specified sheet
cursheet = get(Sheets, 'Item', SheetName);
% calculate start and end
[cr1, cr2]=nn2an2(r2,c2,b2);
A1Notation = [cr1 ':' cr2];
cursheet.Range(A1Notation).Name = RangeName;
% Pull the shortname from the path to close the file
[pathstr, name, ext] = fileparts(SummaryFileName);
ShortName = [name ext];
Excel = actxGetRunningServer('Excel.Application');
Excel.WorkBooks.Item(ShortName).Save;
Excel.WorkBooks.Item(ShortName).Close;
Excel.Quit;
function [cr1 ,cr2] = nn2an2(r2, c2, b2)
% r1 and c1 is the size of the data you are writing to matlab, and b2 is
% the starting cell
% convert alpha, number format to number, number format
% this is the starting cell
t = find(isletter(b2));
t2 = abs(upper(b2(t))) - 64;
if(length(t2) == 2), t2(1) = t2(1) * 26; end
c1 = sum(t2); r1 = str2num(b2(max(t) + 1:length(b2)));
% find the other corner of the matrix in A1 notation by adding the two
% together
c3 = c1+c2-1;
r3 = r1+r2-1;
% convert number, number format to alpha, number format
t3 = [floor((c3 - 1)/26) + 64 rem(c3 - 1, 26) + 65];
if(t3(1)<65), t3(1) = []; end
% other corner found
cr2 = [char(t3) num2str(r3)];
cr1 = b2;
end
end
name the range- just want to write toA2:D6in a sheet labelledMyData, or do you mean to automatically create the stringA2:D6based on some other input? Show an example of what you want to do.xlswrite. Maybe look intoMLGetMatrixif you have access to it.