Cancel
Showing results for 
Search instead for 
Did you mean: 

ActiveX + Excel - Problem when running with Excel sheet open

Solution Partner Genius Solution Partner Genius
Solution Partner Genius

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

 

 

Oliver MacFarlane
Simulation Engineer
web: www.simsol.co.uk
e-mail: oliver.macfarlane@simsol.co.uk
5 REPLIES

Re: ActiveX + Excel - Problem when running with Excel sheet open

Siemens Phenom Siemens Phenom
Siemens Phenom

Hello Oliver,

 

add the following:

xl.DisplayAlerts := false

This will prevent Excel from asking any questions.

Regards,

Ralf
------------------------------------------------------------------------------------------------------
Did you like the answer? Then click the Thumbs Up button.
Did the answer solve your problem? Then accept the answer as solution.

Re: ActiveX + Excel - Problem when running with Excel sheet open

Solution Partner Genius Solution Partner Genius
Solution Partner Genius

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

Oliver MacFarlane
Simulation Engineer
web: www.simsol.co.uk
e-mail: oliver.macfarlane@simsol.co.uk

Re: ActiveX + Excel - Problem when running with Excel sheet open

Siemens Phenom Siemens Phenom
Siemens Phenom

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.

Regards,

Ralf
------------------------------------------------------------------------------------------------------
Did you like the answer? Then click the Thumbs Up button.
Did the answer solve your problem? Then accept the answer as solution.

Re: ActiveX + Excel - Problem when running with Excel sheet open

Solution Partner Genius Solution Partner Genius
Solution Partner Genius

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?

Oliver MacFarlane
Simulation Engineer
web: www.simsol.co.uk
e-mail: oliver.macfarlane@simsol.co.uk

Re: ActiveX + Excel - Problem when running with Excel sheet open

Siemens Phenom Siemens Phenom
Siemens Phenom

Hi Oliver,

 

the running Excel probably locks the file.

Regards,

Ralf
------------------------------------------------------------------------------------------------------
Did you like the answer? Then click the Thumbs Up button.
Did the answer solve your problem? Then accept the answer as solution.