2

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?

5
  • What do you mean by name the range - just want to write to A2:D6 in a sheet labelled MyData, or do you mean to automatically create the string A2:D6 based on some other input? Show an example of what you want to do. Commented Jan 8, 2014 at 21:58
  • I would like to write to 'A2' in a sheet labelled 'MyData', and name the range something like 'Number_of_Widgets' (see Excel range names in Excel under Formulas -> Name Manager). I have Word VBA code that accesses the Excel range names and matches them to Bookmarks of the same name in Word. Commented Jan 9, 2014 at 0:40
  • I don't believe that would be possible with xlswrite. Maybe look into MLGetMatrix if you have access to it. Commented Jan 9, 2014 at 4:00
  • I think that functions like that can't do what you need. Probably you need to look more in this direction. Commented Jan 9, 2014 at 10:23
  • No, it doesn't appear there are any built-in functions will actually name the Excel range from Matlab. Would it be best to use Matlab to call a short VBA macro and pass the Excel range name to it from Matlab? Commented Jan 10, 2014 at 4:02

1 Answer 1

2

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
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.