1

I am automating testcases for my company's addin.

In one of the testcases, after saving my workbook on my local machine when I try closing the workbook, I am required to click on 'Yes'- saves edited data in our database, 'No'- discards the edits or 'cancel'- simply closes the message box.

I have tried using sendKeys but that didn't work for me.

Eg: For clicking 'No' through my VBA code, I tried to send tab and enter keys like this:

Application.Wait (Now() + TimeValue("00:00:10"))
Call SendKeys("{TAB}", True)
Call SendKeys("{ENTER}", True)

I have tried with various time values still didn't get any success. I would be glad if someone can help me with this.

Thanks in advance! :)

UPDATE: As of now, I have been doing this:

'Some tasks...
ActiveWorkbook.Save
Call Application.Run("workbook.xlsm!Discard")
Application.DisplayAlerts = False
ActiveWorkbook.Close SaveChanges:=False
Application.DisplayAlerts = True

But is there any way to get the window object reference for the message box? Or anyway to stick to the workflow: edit workbook -> save -> try closing -> opt to discard/commit changes to database?

NOTE: By discarding/committing changes to database, what I mean is a server where the data gets updated. Excel is just an interface for the user. I don't want just the excel to be saved but the operation of calling discard/save changes on server as well...

Rishi
  • 980
  • 10
  • 21
  • 3
    Have you tried `Application.DisplayAlerts = False` ? See [Application.DisplayAlerts](https://msdn.microsoft.com/en-us/library/office/ff839782.aspx) –  Feb 22 '16 at 08:59
  • If you're determined to do it with sendkeys, i would replace the "{ENTER}" with a spacebar command " ". Enter does not always trigger a button click. spacebar does. But Jeeped's answer is the most commonly used. i have never tried Jarvis way, but assuming it works it is also a valid answer. both do the same, Jarvis's just requires less code. – DDuffy Feb 22 '16 at 09:54
  • Hi @Jeeped, Yes, I tried that and in fact, instead of closing and then selecting from the pop up, I am calling the function that is triggered from the those options and then closing my workbook. But I was curious to know if there's a way to auto-click/auto-select the pop up as it appears. – Rishi Feb 22 '16 at 10:16
  • Is there a way to atleast get the window object reference of this pop up? – Rishi Feb 22 '16 at 10:17
  • 1
    If you are serious about automating test cases against systems with pop up boxes maybe you should take a look at this: https://www.autoitscript.com/site/autoit/ – Nick.Mc Feb 22 '16 at 10:38
  • @Nick.McDermaid....That really looks impressive but we already have our own automation framework to which we are adhered to. Thanks for the knowledge anyways. :) – Rishi Feb 22 '16 at 10:46
  • @DDuffy... Nope...even " " doesn't seem to work.. – Rishi Feb 22 '16 at 12:08
  • 1
    are you sure you're tabbing to the Yes button? also, are you sure youre allowing enough time with the application.wait? if you are waiting on calculations finishing etc before the popup comes, you may want to try Do While Application.CalculationState <> xlDone DoEventsLoop instead of the wait. – DDuffy Feb 22 '16 at 13:59
  • also, as far as i remember, when a popup occurs, it causes a natural break in the code. So while the popup is in focus and awaiting user input, your code will not run. I may be completely wrong with this, (Hence the "As far as i remember" disclaimer) but its worth setting a breakpoint in your code to just before where the popup occurs and stepping through to see what happens. – DDuffy Feb 22 '16 at 14:04
  • @DDuffy....Thanks for your valuable suggestions. I'll try the "Do While Application.Calculation" thing. As far as the breaking of the code is concerned- No, it doesn't break but awaits user input (the click). Accordingly, the action occurs. Anyways, I have talked to the developers, they will be adding hot keys which they say should solve the problem. – Rishi Feb 23 '16 at 06:31
  • @Rishi: When popup occurs it waits for the user input and unless user gives any value the vba code will wait for further steps to be executed. But you dont need to save the file, so shouldn't it be ok just to close the file without making changes, so that the file wont be saved in server. If you use "Activeworkbook.close false", there wont be any prompting of file save and no need to worry on saving the changes on server – nirmalraj17 Feb 23 '16 at 07:10
  • @jarvis... No, that would cover only one case- Discard (since we are simply closing the workbook without tampering with server, it is equivalent to discard). I also need to cover the case when the user chooses Publish just after attempting to close the workbook. Anyhow, I have got some additional methods from the development team that enables me to do this... Thank you! :) – Rishi Feb 25 '16 at 04:51

1 Answers1

3

if you have already saved the workbook, you only need to close the workbook. Please try the below code 

Activeworkbook.close false

This will close your workbook and you wont be promted for selecting any options.

nirmalraj17
  • 494
  • 7
  • 20
  • Hi @jarvis, it's not only about closing the workbook but also opting to discard changes/commit changes (by clicking No/Yes). Is there a way to stick to this workflow: edit workbook -> save -> try closing -> discard/commit changes in database? As of now, this is what I am doing edit workbook -> save -> discard/commit changes -> close. – Rishi Feb 22 '16 at 10:31
  • By discarding/committing changes to database, what I mean is a server where the data gets updated. Excel is just an interface for the user. I don't want just the excel to be saved but the operation of calling discard/save changes on server as well... – Rishi Feb 22 '16 at 10:42