07-20-2015 05:15 AM
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
07-20-2015 06:05 AM - edited 07-20-2015 06:05 AM
Hello Oliver,
add the following:
xl.DisplayAlerts := false
This will prevent Excel from asking any questions.
07-20-2015 06:17 AM
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
07-20-2015 07:20 AM
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.
07-20-2015 07:45 AM
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?
07-20-2015 07:52 AM
Hi Oliver,
the running Excel probably locks the file.