xlswrite1

increases speed of xlswrite function when used in loops or multiple times.

Description

This code increases the speed of the xlswrite function when used in loops or multiple times. The problem with the original function is that it opens and closes the Excel server every time the function is used. To increase the speed I have just edited the original function by removing the server open and close function from the xlswrite function and moved them outside of the function. To use this first run the following code which opens the activex server and checks to see if the file already exists (creates if it doesnt):

Excel = actxserver ('Excel.Application');
File='C:\YourFileFolder\FileName';
if ~exist(File,'file')
    ExcelWorkbook = Excel.workbooks.Add;
    ExcelWorkbook.SaveAs(File,1);
    ExcelWorkbook.Close(false);
end
invoke(Excel.Workbooks,'Open',File);

Then run the new xlswrite1 function as many times as needed or in a loop (for example xlswrite1(File,data,location). Then run the following code to close the activex server:

invoke(Excel.ActiveWorkbook,'Save');
Excel.Quit
Excel.delete
clear Excel

This works exactly like the original xlswrite function, only many many times faster.

• Acknowledgements: This submission has inspired the following:; OfficeDoc - read/write/format MS Office docs (XLS/DOC/PPT), xlswrite_mod, xlswrite2007, saveppt2, Function for faster data transfer Matlab <--> Excel
• MATLAB release: MATLAB 7.1.0 (R14SP3)

[Stacey Chang]

cool stuff!! I appreciated ur code so much! But I couldn't run my loop over 65535 times (I need to run the loop for a million times), hope that you could help me with this...big thanks!!

[Unai Cornes]

THANKS A LOT!!!! It's amazing how fast the program goes now!!

Thank you, Matt.

[Peter Suh]

Very very excellent!!

The best part about this is that did eliminate the xlswrite bug which would leave the excel com server open each time it ran xlswrite. This did save me lots and lots of time and energy..I was rewriting my own xlswrite function.

[Alan L]

Excellent. Works perfect.

[Rahul Bagdia]

Thanks a lot Matt. It works awesomely fast.

[Andrej Skraba]

This is a very good solution. However, there should be some attention paid on closing the Excel session.

A great addition.

[Tal Raviv]

This works remarkably quickly. Thanks for solving the problem for all of us!

[Angie Blue]

Perfect :) Thanks a million, you saved a lot of time and effort.

[Martin Rouse]

Excellent solution to an annoying problem! Works very quickly!

1 point i would like to add, if executing xlswrite1 within a function or GUI, change line 2;

Excel=evalin('base','Excel');

to;

Excel=evalin('caller','Excel');

Many thanks

[Cristi Pedotto]

This just saved me so much time! My code processes 10x faster! Thanks so much!

[N Rolfes]

worked like a godsend, turned a several hour job into a 10 minute task. Thank you!

Also, thanks for the tip Martin as I used this as well.

[magda anusca]

 

[Mark Hayworth]

Note: If you call this from within your own function instead of the MATLAB command line, you will need to change the second line to
   Excel = evalin('caller', 'Excel');
(instead of Excel = evalin('base', 'Excel'); like he has it)

Plus all the error messages arguments need to be changed to "MATLAB:xlswrite1" instead of "MATLAB:xlswrite"

He also needs to add the description at the top of the m-file. As it is, there is no description in the file itself and one would have to return here to the file exchange to get it. It should be also in the first few lines of the m-file.

[Alessandro Sacco]

Thank you for your good job; I just want to ask you if there is anything similar for xlsread. It's so bad leaving the excel com server open each time we run xlsread.

Thanks a lot!

[katie]

awesome. I was wondering if this could be modified to write to multiple files within a single loop?

[David Gómez Jiménez]

Great job¡ thank you, its very usefull

[Travis]

Is there any way to adjust this so that it writes to office 2007 (.xlsx)?

[Aviator]

I'm getting the following error. It seems Matlab cannot save/close the spreadsheet.

??? Invoke Error, Dispatch Exception:
Source: Microsoft Office Excel
Description: 'My_File.xls' is read-only. To save a copy, click OK,
then give the workbook a new name in the Save As dialog box.
Help File: C:\Program Files\Microsoft Office\Office12\1033\XLMAIN11.CHM
Help Context ID: 0

Error in ==> Line 280
    invoke(Excel.ActiveWorkbook,'Save');

Anybody know what can be done to fix this? (by the way, I'm using Office 2007, in compatibility mode) ; Thanks

[alei]

I've got an error message saying that 'Excel' is not defined.

??? Error using ==> evalin
Undefined function or variable 'Excel'.

Error in ==> xlswrite1 at 2
Excel=evalin('base','Excel');

Anyone know how to fix it? Thx

[Travis]

Ana, I am getting the same result, I have used this code for a few months now with no issue until this.

[Michele Colombo]

 

[Oleg Komarov]

I would leave in the function:
<pre class = "code">

Excel = actxserver ('Excel.Application');
Excel.Workbooks.Open('C:\YourAddInFolder\AddInNameWithExtension');
Excel.Workbooks.Item('AddInNameWithExtension').RunAutoMacros(1);
File='C:\YourFileFolder\FileName';
if ~exist(File,'file')
    ExcelWorkbook = Excel.Workbooks.Add;
    ExcelWorkbook.SaveAs(File,1);
    ExcelWorkbook.Close(false);
end
Excel.Workbooks.Open(File);
</pre>

Instead when it checks:
if nargin < 1 || isempty(file)
    error('MATLAB:xlsread:FileName','Filename must be specified.');
end

I would substitute the error with:
Excel.ActiveWorkbook.Save;
Excel.Quit
Excel.delete

This way all u have to do is:
xlsread("firstfile"......)
xlsread("secondfile"......)
...and so on until
xlsread() % close teh connection!

That would code saving and clean.

[lh ‹]

When I run the program, the result:??? Error using ==> evalin
Undefined function or variable 'Excel'.

So how to make excel com available? thx!

[Anping]

It reduces my computing time from 20s to 2s.

[Harry]

Matt,

you are my main man... Big ups..

Cheers

[Rachel]

 

[Rachel]

Does need something in the actual script rather than in the description on this page to avoid the "Error using ==> evalin " error. Not immediately obvious that it requires some lines before and after you call it otherwise.
Time saved by this is blissful!

[TideMan]

Worked like a charm once I changed 'base' to 'caller' in Excel=evalin('base','Excel');
as suggested by Martin Rouse.

[Artik Crazy]

Thank you a lot!
This function helped me very much.
Overall run time of my simulation now is almost 10 times faster!
Block that was build of loop iterations on xlswrite now works 20 times faster.

[Geoff]

Thank you Matt! I had about 400 calls to xlswrite which took about 11mins, now takes 9sec! Massive improvement!

Thanks also to Martin Rouse for solving the issue of calling xlswrite1 within a function.

[Mike]

For those still getting an error after changing line 2 to

Excel = evalin('caller','Excel');

Go to Task Manager, see if the Excel process is still running. Errors stop the program from getting to the Close line. So change line 2, end the Excel process in Task Manager. I did this and it now works great!

[Christopher]

Hello all,

I'm still getting an error after changing line 2 to Excel=evalin('caller,'Excel'); I've gone to my task manager and closed all excel processes. What do I need to change to get this code to work??? Thank you in advance.

Chris

[olivier calcoen]

It is very great
Thank you very much

[Ngai-Hang]

I found that this didn't work with Office 2007 files, i.e. with .xlsm. I managed to get xlswrite2007 to work though with some modifications.

[Bilen Oytun Peksel]

 

[Luis Andres]

 

[Abdull]

I still get error message even though, I have changed line 2 from base to caller.
Please help me.

---Error message start---
??? Error using ==> evalin
Undefined function or variable 'Excel'.

Error in ==> xlswrite1 at 2
Excel=evalin('caller','Excel');
---Error message end---

[Abdull]

Matt,

You are my man. Brilliant!