Error when accessing an Excel workbook from within a parallel loop

2
votes

I am using MATLAB to write to an Excel file with Macros in them, and then reevaluate the file and extract the Macro results using xlsread. The code works fine with only one worker. However, if I use parfor and use a second worker, MATLAB gives the error message as below. To be rigorous, I have created a second Excel file for the second worker and use t = getCurrentTask(); t.ID; to instruct the workers to work on their arranged excel file.

Error using xlswrite (line 226)

Invoke Error, Dispatch Exception: Source: Microsoft Excel Description: Microsoft Excel cannot access the file 'C:\Users-----\D0481000'. There are several possible reasons: • The file name or path does not exist. • The file is being used by another program. • The workbook you are trying to save has the same name as a currently open workbook. Help File: xlmain11.chm Help Context ID: 0

Error in extractFromExcelExpress (line 249) xlswrite(filename,writtenIn,sheet,'B23');

This happens randomly. I did try to change the folder of the excel files and it starts to work for hours but ultimately stops. I did clear all the add-ins in Excel and I did put the corresponding folder to the excel trust locations.

  • The Excel version: 2016

  • The MATLAB version: R2017a

1
excelmatlabparallel-processingactivexfile-access
You're trying to open (and write to) the same file simultaneously from multiple workers... this will cause errors for the same reason that trying to do this manually would - Wolfie
Thank you Wolfie! However, I am sure I am using 't = getCurrentTask(); t.ID;' to make sure that each worker just works on the same file. - Sheng
That's exactly my point, if all workers are writing to a single file then you get conflicts, as elaborated on in Dev-iL's answer... The real solution here is to rewrite the Macro in MATLAB - this will speed things up many many times since xlsread and xlswrite are terribly slow, throwing a parfor at the problem is not the answer. - Wolfie
Thank you Wolfie again. I think I made a typo in my last comment. In each process inside the parfor, I use t = getCurrentTask(); t.ID; to find out which worker is working on the process. Say t.ID==2, then it is worker 2 working on it and I then let worker 2 to use file #2. - Sheng
I think your advice on changing the Macro is a good one. It is a bit beyond my experience but I will try when I got some time for it! Thanks a lot again! - Sheng

1 Answers

0
votes

This sounds like a typical race condition issue caused by multiple processes requiring exclusive access to the same file. The basic idea is that one process still hasn't closed the file when another tries to open it, and you only get errors sometimes because these issues don't always occur (see also: Heisenbug).

Here are several suggestions regarding what you can do (in decreasing order of what makes sense to me):

  • Try redesigning the code such that only one process ever accesses (read OR write) the file at a time. This is called a "critical section". The easiest way to achieve this in MATLAB is by having the xlswrite code outside the parfor. If you're having a hard time implementing this while keeping the parfor, simply save all intermediate result to a workspace variable, and write it to disk when the computations are done.
  • You might be able to share the ActiveX object between the processes (I've never tried this, and can't say for sure that it's possible), using the same approach as in xlswrite1. Essentially it doesn't try to open and close the file between writes, so the situation where one process still hasn't closed it where another tries to open it cannot happen.
  • Find a way to use the Workbook.MultiUserEditing property. This is possibly related to the previous suggestion.
  • Use the fastest storage available. You can use a RAM drive, and put your Excel file there, so that you could at least avoid any slowness caused by using a network drive, an HDD, or even an SSD.
  • If all else fails, surround the xlswrite statements with a while true, try, xlswrite(...); break; catch, pause(0.01); end, end. This will slow the code down but at least it will finish eventually.