File Exchange

image thumbnail

XLSAPPEND

version 1.1.0.1 (6.36 KB) by Brett Shoelson
Append data to the next-available row of an existing Excel file with a single call to ActiveX.

31 Downloads

Updated 01 Sep 2016

View Version History

View License

SYNTAX: [success,message] = xlsappend(file,data,sheet)
XLSAPPEND Stores numeric array or cell array to the end of specified Excel sheet.

REQUIRES ONLY ONE CALL TO THE EXCEL ACTXSERVER, so the overhead is less than for successive xlsread/xlswrite calls.

[SUCCESS,MESSAGE]=XLSAPPEND(FILE,ARRAY,SHEET) writes ARRAY to the Excel workbook, FILE, into the area beginning at COLUMN A and FIRST UNUSED ROW, in the worksheet specified in SHEET. FILE and ARRAY must be specified. If either FILE or ARRAY is empty, an error is thrown and XLSAPPEND terminates. The first worksheet of the workbook is the default. If SHEET does not exist, a new sheet is added at the end of the worksheet collection. If SHEET is an index larger than the number of worksheets, new sheets are appended until the number of worksheets in the workbook equals SHEET. The success of the operation is returned in SUCCESS and any accompanying message, in MESSAGE. On error, MESSAGE shall be a struct, containing the error message and message ID.

Cite As

Brett Shoelson (2021). XLSAPPEND (https://www.mathworks.com/matlabcentral/fileexchange/28600-xlsappend), MATLAB Central File Exchange. Retrieved .

Comments and Ratings (60)

v k

The OS is windows 10.
Excel version number is 1812 (Build 11126.20266).
But more than the version number, I think it is the input:
>> name{1}=input('first name \n','s');
first name
brett
>> name{1}
ans =
'brett'
whereas the remaining three (which you have written) are 1x1 cell arrays:
>> name(1)
ans =
1×1 cell array
{'brett'}
>> {name{1}}
ans =
1×1 cell array
{'brett'}
>> name
name =
1×1 cell array
{'brett'}

v k

The output is:
A B C D E F
1 B r e t t
2
Where A,B,C,D,E are col-numbers, and 1,2, are row numbers.

v k

Here is the warning message:
>> opFile='trial2.xlsx';
>> [success,message]=xlsappend(opFile,{name{1}})
Warning: Could not start Excel server for export.
XLSWRITE will attempt to write file in CSV format.
> In xlsappend (line 178)
success =
logical
1
message =
struct with fields:
message: 'Could not start Excel server for export.↵XLSWRITE will attempt to write file in CSV format.'
identifier: 'MATLAB:xlswrite:NoCOMServer'

Brett Shoelson

@VK: What OS are you using? What version of Excel?

v k

Not sure what is happening. There are three issues going on:
1). Gives warning of not being able to open excel, and opens csv instead.
2). Prints one alphabet in each col of 1st row, instead of the entire string in the 1st col of the 1st row.
3). Doesn't append, but writes in the 1st row again when the code is run again.

It is putting name{1}(1) in the 1st col, name{1}(2) in the 2nd col, name{1}(3) in the 3rd col and so on (all in the 1st row.
When the code is run again, it should put in the 2nd row, but it wipes away the record of the 1st run, and starts from scratch and again puts name{1}(1) at (R1,C1), name{1}(2) at (R1,C2), name{1}(3) at (R1,C3) etc.

Brett Shoelson

@VK: I should note: {name{1}} == name(1), so your syntax should work, and in fact works for me! (Again, make sure that your excel file exists and is closed.)
Brett

v k

opFile='trial.xlsx';
name{1}=input('first name \n','s');
[success,message]=xlsappend(opFile,{name{1}})

Even without the curly brackets, with only name{1}, its the same.
Also, instead of xlsx, it creates csv file.
And every time I run the code, the string name{1} gets written on the first line itself, with one alphabet in each column.
With the successive runs, the name{1} string should have been written on the successive rows with each run of the code.
But every time the code is run, it wipes out the previous name in the csv file, and writes name{1} in the first row.

Brett Shoelson

@VK: I just tried it and got "Brett" written to a single cell. My syntax:
xlsappend('test.xlsx', {'Brett'})

(Without the curly brackets, I get a single letter per column.)
Can you show me your calling syntax?
Brett

v k

Hello,
If the name to be printed is
name='brett';
then xlsappend prints 'b' in the 1st col (A-col), 'r' in the 2nd col (B-col), 'e' in the 3rd col and so on,
instead of 'brett' in the 1st col (A-col).
Submitting it as a a cell array, {name}, also gives exactly the same result.
How to get the entire name in a single column?
Thanks.

Brett Shoelson

@Stefano: Would you mind emailing me directly at:
char(cumsum([98 17 -11 7 -10 7 7 -4 -47 45 -12 19 -12 15 -8 3 -7 8 -69 53 12 -2]))
I'd (we'd) like to figure out why you're not seeing the file. Are you using a non-default unzipping mechanism, by chance? Or is it possible that you have some antivirus software removing the attachment from the zip file?
Thanks,
Brett

Stefano Orlando

@Brett
I downloaded it by clicking on the download button of the page and I tried with Chrome and Firefox but the result is the same.
I'm using Windows 10 Pro.
Thanks for your helping.

Stefano

Brett Shoelson

@Stefano:
We're trying to understand why you only got the license file. Can you tell me how you downloaded the submission, what OS you're using, and what browser?
Thanks,
Brett

Brett Shoelson

@Stefano: I just verified that I can see the xlsappend file, and that it is included in the zip file when I extract it. Would you try again, please, and let me know what you see?
Thanks,
Brett

Stefano Orlando

Hi,
I downloaded the zip file and only the license was inside.
I checked the old version and the *.m file is present.
I tried to use that one but some issues arises.
Can you kindly check if the *.m file is present in the zip?
Thanks in advance and for sharing.

Stefano

Brett Shoelson

@Binu:
I would need to test with your files, but I think something like this (in pseudocode) should work:

myFiles = dir('*.xls'); % or however you want to wrangle them...
for ii = 2:numel(myFiles)
myFiles(1).name = xlsappend(myFiles(1).name, xlsread(myFiles(ii).name);
end

% That should append all contents into the first file. The rest you can discard, I presume, once you've verified that everything worked as expected.

Brett

Binu

can I use this in a loop to create one excel file out of multiple excel files? if so how. Appreciate your help.

An Ke

Arunachalam Sundaram

it works perfectly. Thank you

frankenberry

Thanks Brett. I used the download button and it gave me a zip file but the content was only the license. I will contact tech support as you suggest. Very odd.

Brett Shoelson

...just downloaded the file and I got a zip file with the license agreement and the xlsappend function. If the Download button isn't working for you, I would suggest that you call tech support.
Brett

frankenberry

okay. So what's the purpose of the big download button to the upper right? Seems odd that the big button is for the license and you have to go to another tab for the download especially since the "View License" is a link to the license. JS

Brett Shoelson

@Patricia: I assure you the file is there; you appear to have made a mistake in your downloading. Click on the "Functions" tab above--you can see the file.
Brett

frankenberry

nothing in the file except a licence.

Brett Shoelson

@Jalil, Hazoor, Olivier: Thanks for the comments and ratings!
@Olivier: 2009a???? Time to update--we've come a long way since then!
Cheers,
Brett

Olivier Doyle

Used on 2009a with minimal adaptation (removed ~ assigned to output on line 282). Works fast and outputs appropriate errors when needed. Thank you!

Hazoor Ahmad

It is really an amazing function helped a lot for me

Jalil Sharafi

Excellent work. Thanks.

Brett Shoelson

@Amine: Thanks for the note and the rating! Glad it was helpful. Brett

AMINE EL MOUATAMID

this is really helpfull I spent too much time looking for a function to append new rows on excel file , thank you

Brett Shoelson

@H.K. Please try to convert to char before appending. (char(x)). Let me know if that works for you!
Cheers,
Brett

H.K. Sardana

I have a huge two column array consist of string(col 1) and numeric data(col2).... how do I append the entire array... as string is not a valid input for the function... help help!

Brett Shoelson

@Kristen,
First, that is a warning, not an error. You can suppress it (docsearch "suppress warnings") or ignore it until nargchk is truly deprecated, at which time you can replace the nargchk call with narginchk instead. XLSAPPEND will continue to work (and throw the warning) until that happens. Or you can go ahead and make that substitution now.
The problem with repeated calls failing has nothing to do with the warning. I think you need to ensure that XLSAPPEND is on your MATLAB path (docsearch "MATLAB Path"). I think you are changing directories somewhere, and MATLAB is losing the link to that file.
Regards,
Brett

Kristen Farrell

I am new to matlab and I can't get this function to work.
When I run this code, I get the following error:

Warning: NARGCHK will be removed in a future release. Use NARGINCHK or NARGOUTCHK instead.
> In xlsappend (line 138)
Warning: NARGCHK will be removed in a future release. Use NARGINCHK or NARGOUTCHK instead.
> In xlsappend (line 139)
Error using xlsappend (line 139)
Not enough input arguments.

Despite this error, when I go back to the script I have written, "xlsappend" works great and saves my variables to excel.
This is how I'm using xlsappend in my code:

[success, message] = xlsappend('fileName',excelVariables,2);

But as soon as my variables are cleared for the next piece of analysis, xlsappend is no longer recognised unless I repeat the entire process. The error reads:

Undefined function or variable 'xlsappend'.

I want to call on this function in my own code so that this can be automated, but since this function keeps throwing that same error, this stops my script from finishing.

Please help!
I'm using matlab version 2016b.

Brett Shoelson

@Maria:
To write strings to single cells, submit them in a cell array. For example:

xlsappend( 'myExcelDoc.xlsx', { 'test1' , 'test two' } )

HTH,
Brett

Maria Jaramillo

Hi Brett! Question how can I get it to group string like data into each cell? I was able to make it work except that for a value of 0.00 it take a cell for each digit.

Brett Shoelson

@Madina,
Yes, this is an ActiveX-based solution, and runs on a PC. Sorry--I don't have a version that will work on a Mac. (Not sure about Linux.)
Brett

Madina Makhmutova

Does this run on Mac?
I have 2011 and 2017 versions of Excel for Mac on my computer.
I keep getting an error:
"Warning: Could not start Excel server for export.
XLSWRITE will attempt to write file in CSV format."
I get the same error if I use xlswrite. Is it just because those functions are not formatted for Mac?

Michal Dobai

Brett Shoelson

@Vikram:
Yes, 2009a was a long time ago. You might be able to change some things to get it to work in that version, but I wrote it for a "modern" session of MATLAB. ;)
For one thing, line 282 uses the tilde for unwanted outputs:
[~, n, e] = fileparts(filePath);
That was introduced later. Try replacing the line with:
[junk, n, e] = fileparts(filePath);
Brett

Vikram Baliga

getting this error: Error: File: xlsappend.m Line: 282 Column: 11
Expression or statement is incorrect--possibly unbalanced (, {, or [.

Using MATLAB R2009a. Is it the version incompatibility?

Dulara De Zoysa

Lukas Tietz

Chetankumar Vegad

Thanks

Kyle Reagan

Jibran Shahid

great work ...

Baris

Cyrus

Working great, thank you

Abdulaziz Abutunis

TOO much time was saved. Thank you.

Roman

super!

Aditya

Andy S

The property usedrange is the problem. I suppose you would have to use the method of selecting the bottom cell (eg. A64000) and do xlup to find the next row that doesn't contain data.

If you format the entire column by selecting the header, then there is no problem since usedrange doesn't count that as being using since the entire worksheet has formatting.

Andy S

Hey man, I'm trying to figure out how to get it to ignore formatting to determine the next row to append data to. I've pre-formatted a spreadsheet and it adds it to the next row below all of that. I could reformatt the sheet by copy/paste format everytime I import data but that's kind of a pain. Any help?

Brett Shoelson

Thanks for sharing the rating, and for your modification suggestion, Ralph. Much appreciated!
Cheers,
Brett

Ralph

Very useful function. Thank you.

I added the following prior to line 160 to create file if it does not already exist (motivated by xlswrite1):

if ~exist(file,'file')
ExcelWorkbook = Excel.workbooks.Add;
switch ext
case '.xls' %xlExcel8 or xlWorkbookNormal
xlFormat = -4143;
case '.xlsb' %xlExcel12
xlFormat = 50;
case '.xlsx' %xlOpenXMLWorkbook
xlFormat = 51;
case '.xlsm' %xlOpenXMLWorkbookMacroEnabled
xlFormat = 52;
otherwise
xlFormat = -4143;
end
ExcelWorkbook.SaveAs(file,xlFormat);
ExcelWorkbook.Close(false);
end

Ralph

Very useful submission. Just make sure the file already exists.

Akansha Saxena

Brett Shoelson

Javier,
The first time you call the function, make sure that you have specified the filename properly, including the exact extension. ("XLS," "XLSX," ...). If appendToXLS doesn't find the Excel file, it will create a CSV-formatted one and operate on it. If that doesn't address your question, please feel free to contact me directly. Perhaps I could figure out specifics if I had your file to work with.
Cheers,
Brett

Javier

Hi,
thanks for sharing! I have a warning the second time I call the function:
Warning: Could not start Excel server for export.
XLSWRITE will attempt to write file in CSV format.
Do I have a problem with ActiveX. How could I solve it? Thanks a lot

Zoltan

Thanks.

Rossella Blatt Vital

Very nice! Thanks!

MATLAB Release Compatibility
Created with R2010a
Compatible with any release
Platform Compatibility
Windows macOS Linux

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!