ActiveX + Excel - Problem when running with Excel sheet open

[macfaro]

Hello All,

I have a model where I get data from an Excel sheet on reset and then send results to Excel at the end of the model run. I use ActiveX and a filelink to run it.

It works perfectly if I close the Excel sheet before I run the model. However if I leave it open, at the end of the run it asks If I want to save over an existing file, I then click yes and it saves it in MyDocuments (a completely new excel file, not my current excel file which is open). I would like this model to work well if the Excel File is open or not.

The code on finishing is the following:

Activex.active:=True;
xl:= activeX;
xl.workbooks.open(FileLink.filename);
xl.visible:=False;

--Code inputting data into Excel

xl.activeworkbook.save;
xl.quit;
ActiveX.Active:=False;

Thanks

Ollie

[RalfTobel]

Hello Oliver,

add the following:

xl.DisplayAlerts := false

This will prevent Excel from asking any questions.

[macfaro]

Hi Ralf,

That does indeed stop Excel asking any questions. However I am still having the problem where Plant/Excel decides to create a new version of my Excel sheet in MyDocuments folder instead of just inputting the data into the Excel file I have open (located on Desktop and filelink is pointed that way).

Cheers

Ollie

[RalfTobel]

Hi Oliver,

I'm not sure how this can be solved.

When you create an Excel COM object you start a new instance of Excel.

I don't know if there is a possibility to connect to an existing running instance of Excel using COM.

[macfaro]

Hi Ralf,

I thought that might be the case. However for my understanding, why when the Excel file is not open does it save it all correctly in my Desktop Excel file, yet when open saves it as a new file in MyDocuments?

[RalfTobel]

Hi Oliver,

the running Excel probably locks the file.