cancel
Showing results for 
Search instead for 
Did you mean: 

How to save and close one excel file when multiple are open

SOLVED
Highlighted
Contributor

How to save and close one excel file when multiple are open

Hi,

Help  Java script code on how to  save and close one of the  excel file when multiple are open.

My current code is working fine if only one excel is open.

function Excelclose()

{

let app = Sys.OleObject("Excel.Application");

if(app.Visible== true){

app.ActiveWorkbook.Save();

app.Quit()

Log.Message("Excel closed successfully");

}

else

{

Log.Message(" Excel is not running");

}

 

when myltiple excels files are open, due to App. quit,  microsoft excel save popup is getting displayed  ( refer the screen shot).I tried to giving detail of specific file name to save and close, some how  not working.

 

function Excelclose()

{

let app = Sys.OleObject("Excel.Application");

let filepath: C:\\users\\test.xlsx";

if(app.Visible== true)

{

app.ActiveWorkbook.Save(filepath);

app.Quit()

Log.Message("Excel closed successfully");

}

can some one take a look at it and let me know where I am missing ?

 

Thank you

 

 

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Hero

Re: How to save and close one excel file when multiple are open

You need to set the active workbook to the one that you want to close.  Then, in your call to "Close", there is a parameter to indicate whether or not to save changes.  App.Quit is NOT how you want to go because that will Excel, not just the individual workbooks.  The only time you should call app.Quit is at the very end of your execution when you're completely done with Excel.


Robert Martin
[Hall of Fame]
Please consider giving a Kudo if I write good stuff
----

Why automate?  I do automated testing because there's only so much a human being can do and remain healthy.  Sleep is a requirement.  So, while people sleep, automation that I create does what I've described above in order to make sure that nothing gets past the final defense of the testing group.
I love good food, good books, good friends, and good fun.

Mysterious Gremlin Master
Vegas Thrill Rider
Extensions available

View solution in original post

6 REPLIES 6
Highlighted
New Contributor

Re: How to save and close one excel file when multiple are open

I would suggest you to Create a workbook instead of using "app.ActiveWorkbook" and then try to save the Workbook & close it.

let excelObject= Sys.OleObject("Excel.Application");
let filepath: C:\\users\\test.xlsx";

excelWorkbookObj = excelObject.Workbooks.Open(filepath);
excelWorkbookObj.SaveAs(strexcelFileName);
excelWorkbookObj.Close();
excelWorkbookObj = null;
Highlighted
Contributor

Re: How to save and close one excel file when multiple are open

Thank you for reply and suggestion.But the excel has test data which are using for DD Loop and updating the transaction ids  to the same excel.so create work book  is not an option.

If  the scripts gets error out at the middle, we are stoping the scripts. in that cases, the excel is alreay open and unable to open again.

 

Madhu

Highlighted
Community Hero

Re: How to save and close one excel file when multiple are open

You need to set the active workbook to the one that you want to close.  Then, in your call to "Close", there is a parameter to indicate whether or not to save changes.  App.Quit is NOT how you want to go because that will Excel, not just the individual workbooks.  The only time you should call app.Quit is at the very end of your execution when you're completely done with Excel.


Robert Martin
[Hall of Fame]
Please consider giving a Kudo if I write good stuff
----

Why automate?  I do automated testing because there's only so much a human being can do and remain healthy.  Sleep is a requirement.  So, while people sleep, automation that I create does what I've described above in order to make sure that nothing gets past the final defense of the testing group.
I love good food, good books, good friends, and good fun.

Mysterious Gremlin Master
Vegas Thrill Rider
Extensions available

View solution in original post

Contributor

Re: How to save and close one excel file when multiple are open

Thanks Robert for the guidance. Did you mean I need to create two functions - one will active work book and other to save?

would u  help me with logic?

 

Thank you

 

Highlighted
Community Manager

Re: How to save and close one excel file when multiple are open

Hi @techgirlbb,

 

I think Robert suggested to activate the needed worksheet and close it instead of closing of the application.

I've found that you can use the Activate method:

https://docs.microsoft.com/en-us/office/vba/api/excel.worksheet.activate(method)

 

example:
app.Workbooks("Name").Worksheets("Sheet1").Activate
 

 

---------
Tanya Gorbunova
SmartBear Community Manager

Learn SmartBear products in a fun and easy way and prove your knowledge!
>>Participate in the TechCorner Challenge today
Highlighted
Community Hero

Re: How to save and close one excel file when multiple are open

Sorry, I intended to reply to this.

 

Yes, @TanyaGorbunova has the correct code.  Use the "Activate" method on a workbook and make it the active one then close that.  This will close the workbook without exiting the Excel application. Additionally, the "Close" method has a parameter to indicate to save changes on close without needing the prompt.


Robert Martin
[Hall of Fame]
Please consider giving a Kudo if I write good stuff
----

Why automate?  I do automated testing because there's only so much a human being can do and remain healthy.  Sleep is a requirement.  So, while people sleep, automation that I create does what I've described above in order to make sure that nothing gets past the final defense of the testing group.
I love good food, good books, good friends, and good fun.

Mysterious Gremlin Master
Vegas Thrill Rider
Extensions available
New Here?
Join us and watch the welcome video:
Announcements
Join TechCorner Challenge!
Want a fun and easy way to learn TestComplete? Try solving weekly TechCorner challenges and get into the Leaderboard!

Challenge Status

Changing options from outside of TestComplete

Participate!

How to execute remote test and obtain results via Test Runner REST API

Participate!

Comparing content of HTML table with Excel file data

Participate!

Compare HTML table with Excel file and correct data in the file

Participate!
Top Kudoed Authors