I have a routine that copies data in a worksheet pastes it in another one and closes the original.
However what I need to do is close the 1st workbook with out first pasting the copied data .
However what happens is it hangs on the close part of the routine and I cannot get to the “yes” command in the dialog box that the close command opens .
tell application "Microsoft Excel"
activate
set AnyData to first row index of (get end (last cell of column 1) direction toward the top)
if AnyData = 1 then -- only headings remain
set AnyData to 2 --Makes Sure headings are not copied
end if
select range ("A2:I" & AnyData)
copy range range ("A2:I" & AnyData)
delay 1
close every workbook saving no
select window 1
tell application "System Events" to tell process "Microsoft Excel"
click button "Yes" of window 1
end tell
end tell
What dialog box?
I’m going to guess that you mean ‘There is a large amount of information on the Clipboard.’
If this is the case, try turning off ‘display alerts’, like so:
set display alerts to false
Then close your workbook. Note that you may need to turn the alerts back on afterward. When set to false, this chooses the default option for the alert (i.e. ‘Yes’ to saving on the clipboard).
By the way, in your description, you specify that you need to [format]close workbook 1[/format] but your code closes all workbooks. Which is correct?
Perfect thanks very much.
Tried to paste copy of dialog box could not do that. But as you suspected it is the one that tells you that you have a lot of information on the clipboard.
Will set the display alerts to true.
Sorry about the confusion about what I was closing. As it happens in this case only one work book should be open.
My pleasure. The docs suggest that ‘display alerts’ will not revert automatically but I don’t recall whether that’s only within the current script. Either way, it might be a good practice to set it back to true later in the script.
The value of display alerts will not reset until you quit and restart Excel. So you should always reset it after you change it.