Saying No to Message Box prompt through VBA
 

Hi all,

I've created a VBA program which opens another Excel workbook and
copies data from two different tabs and pastes into my current
workbook. After all that is done, I try to close the source workbook,
but get a message box stating if I want to save changes to the
workbook. I want the VBA script to say no. I used application.sendkeys
("n") to accomplish this, but it doesn't work. I want to run this
program on a daily basis unattended, but as of now, the program stops
and waits for me to click No on the message box before proceeding. Any
and all help will be greatly appreciated!

Thanks!

Saying No to Message Box prompt through VBA
 

Couple of methods.

1...............In Thisworkbook module of source workbook to close
without saving.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
ThisWorkbook.Saved = True
End Sub

2................In macro which closes the source workbook

Application.displayalerts = false

close the workbook

Application.displayalerts = true


Gord Dibben Microsoft Excel MVP

On Tue, 16 Aug 2011 14:43:43 -0700 (PDT), Haas C
wrote:

Hi all,

I've created a VBA program which opens another Excel workbook and
copies data from two different tabs and pastes into my current
workbook. After all that is done, I try to close the source workbook,
but get a message box stating if I want to save changes to the
workbook. I want the VBA script to say no. I used application.sendkeys
("n") to accomplish this, but it doesn't work. I want to run this
program on a daily basis unattended, but as of now, the program stops
and waits for me to click No on the message box before proceeding. Any
and all help will be greatly appreciated!

Thanks!

Saying No to Message Box prompt through VBA
 

Just a comment on Gord's option 1: With this code, if you open the
workbook manually, enter any changes and close the workbook (without
doing a manual save) the workbook will obediently close without saving
the changes.

Clif

"Gord" wrote in message
...
Couple of methods.

1...............In Thisworkbook module of source workbook to close
without saving.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
ThisWorkbook.Saved = True
End Sub

2................In macro which closes the source workbook

Application.displayalerts = false

close the workbook

Application.displayalerts = true


Gord Dibben Microsoft Excel MVP

On Tue, 16 Aug 2011 14:43:43 -0700 (PDT), Haas C
wrote:

Hi all,

I've created a VBA program which opens another Excel workbook and
copies data from two different tabs and pastes into my current
workbook. After all that is done, I try to close the source workbook,
but get a message box stating if I want to save changes to the
workbook. I want the VBA script to say no. I used application.sendkeys
("n") to accomplish this, but it doesn't work. I want to run this
program on a daily basis unattended, but as of now, the program stops
and waits for me to click No on the message box before proceeding. Any
and all help will be greatly appreciated!

Thanks!



--
Clif McIrvin

(clare reads his mail with moe, nomail feeds the bit bucket :-)

Saying No to Message Box prompt through VBA
 

Thank you very much - I used the second method and it worked
perfectly! Thanks!

On Aug 16, 5:59*pm, Gord wrote:
Couple of methods.

1...............In Thisworkbook module of source workbook to close
without saving.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
* * * * * ThisWorkbook.Saved = True
End Sub

2................In macro which closes the source workbook

Application.displayalerts = false

* * * * *close the workbook

Application.displayalerts = true

Gord Dibben * *Microsoft Excel MVP

On Tue, 16 Aug 2011 14:43:43 -0700 (PDT), Haas C



wrote:
Hi all,

I've created a VBA program which opens another Excel workbook and
copies data from two different tabs and pastes into my current
workbook. After all that is done, I try to close the source workbook,
but get a message box stating if I want to save changes to the
workbook. I want the VBA script to say no. I used application.sendkeys
("n") to accomplish this, but it doesn't work. I want to run this
program on a daily basis unattended, but as of now, the program stops
and waits for me to click No on the message box before proceeding. Any
and all help will be greatly appreciated!

Thanks!- Hide quoted text -

- Show quoted text -

T

Saying No to Message Box prompt through VBA
 

Haas C formulated on Tuesday :
Hi all,

I've created a VBA program which opens another Excel workbook and
copies data from two different tabs and pastes into my current
workbook. After all that is done, I try to close the source workbook,
but get a message box stating if I want to save changes to the
workbook. I want the VBA script to say no. I used application.sendkeys
("n") to accomplish this, but it doesn't work. I want to run this
program on a daily basis unattended, but as of now, the program stops
and waits for me to click No on the message box before proceeding. Any
and all help will be greatly appreciated!

Thanks!

Workbooks("NameGoesHere.xls").Close SaveChanges:=False

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

Saying No to Message Box prompt through VBA
 

Cliff

OP stated he wanted to close the source workbook and say NO to saving.

I try to close the source workbook,
but get a message box stating if I want to save changes to the
workbook. I want the VBA script to say no.

Just complying with his wishes.


Gord

On Tue, 16 Aug 2011 17:15:03 -0500, "Clif McIrvin"
wrote:

Just a comment on Gord's option 1: With this code, if you open the
workbook manually, enter any changes and close the workbook (without
doing a manual save) the workbook will obediently close without saving
the changes.

Clif

"Gord" wrote in message
.. .
Couple of methods.

1...............In Thisworkbook module of source workbook to close
without saving.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
ThisWorkbook.Saved = True
End Sub

2................In macro which closes the source workbook

Application.displayalerts = false

close the workbook

Application.displayalerts = true


Gord Dibben Microsoft Excel MVP

On Tue, 16 Aug 2011 14:43:43 -0700 (PDT), Haas C
wrote:

Hi all,

I've created a VBA program which opens another Excel workbook and
copies data from two different tabs and pastes into my current
workbook. After all that is done, I try to close the source workbook,
but get a message box stating if I want to save changes to the
workbook. I want the VBA script to say no. I used application.sendkeys
("n") to accomplish this, but it doesn't work. I want to run this
program on a daily basis unattended, but as of now, the program stops
and waits for me to click No on the message box before proceeding. Any
and all help will be greatly appreciated!

Thanks!

Saying No to Message Box prompt through VBA
 

Gord has brought this to us :
OP stated he wanted to close the source workbook and say NO to saving.

I try to close the source workbook,
but get a message box stating if I want to save changes to the
workbook. I want the VBA script to say no.

Just complying with his wishes.


Gord

So why not use the SaveChanges arg for the Close method? Surely this
would be preferable over adding code to the xls so it fires a security
warning when opened. Also, it occupies the same line of code, obviating
the need to turn DisplayAlerts off/on and so saves the extra
processing.

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc