File Exchange

image thumbnail

XLSAPPEND

version 1.1.0.1 (6.36 KB) by

Append data to the next-available row of an existing Excel file with a single call to ActiveX.

45 Downloads

Updated

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.

Comments and Ratings (22)

Brett Shoelson

Brett Shoelson (view profile)

@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

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?

Lukas Tietz

Thanks

Kyle Reagan

Jibran Shahid

great work ...

Baris

Baris (view profile)

Working great, thank you

TOO much time was saved. Thank you.

Roman

Roman (view profile)

super!

Aditya

Aditya (view profile)

Andy S

Andy S (view profile)

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

Andy S (view profile)

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

Brett Shoelson (view profile)

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

Ralph

Ralph (view profile)

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

Ralph (view profile)

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

Brett Shoelson

Brett Shoelson (view profile)

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

Javier (view profile)

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

Zoltan (view profile)

Thanks.

Very nice! Thanks!

Updates

1.1.0.1

Updated license

1.1

9/7/2010 Update to fix some intro verbiage.

MATLAB Release
MATLAB 7.10 (R2010a)

Download apps, toolboxes, and other File Exchange content using Add-On Explorer in MATLAB.

» Watch video

Win prizes and improve your MATLAB skills

Play today