Clear Clipboard on Workbook Close Event (Excel XP)
 

Hi--

I'm working on a short macro that involves opening one Excel workbook,
copying the active sheet to a second workbook, then closing the first.

Problem: When I close this first workbook, I get a message box asking if
I want to keep what was copied to the clipboard available or not. I do
not, and I would like to cut this dialog exchange out of the program.
I've been unable to find the correct modifier to tell it to skip this.
Can someone help me? Here's the code section:

Dim Open1 As FileDialog
Set Open1 = Application.FileDialog(msoFileDialogOpen)
Open1.Show
Open1.Execute

Application.Workbooks(2).ActiveSheet.Cells.Copy
With Application.Workbooks(1).Sheets("Sheet2")
.Paste
End With

Appliation.Workbooks(2).Close

Is there a line I should add before this last telling it to clear the
clipboard? Or is there some property for .Close that will do it?

Second question: Could I accomplish the same thing using
msoFileDialogFilePicker instead of Open? I'm new at this and can't seem
to decipher whether "picking" a file lets you select a worksheet inside
it for copying or not.

Any help would be welcome.

Thanks,

Kelley

Clear Clipboard on Workbook Close Event (Excel XP)
 

Application.Cutcopymode = False

would be the first thing to try.

If that doesn't work, then

Application.DisplayAlerts = False

--
Regards,
Tom Ogilvy

"Culichi" wrote in message
...

Hi--

I'm working on a short macro that involves opening one Excel workbook,
copying the active sheet to a second workbook, then closing the first.

Problem: When I close this first workbook, I get a message box asking if
I want to keep what was copied to the clipboard available or not. I do
not, and I would like to cut this dialog exchange out of the program.
I've been unable to find the correct modifier to tell it to skip this.
Can someone help me? Here's the code section:

Dim Open1 As FileDialog
Set Open1 = Application.FileDialog(msoFileDialogOpen)
Open1.Show
Open1.Execute

Application.Workbooks(2).ActiveSheet.Cells.Copy
With Application.Workbooks(1).Sheets("Sheet2")
.Paste
End With

Appliation.Workbooks(2).Close

Is there a line I should add before this last telling it to clear the
clipboard? Or is there some property for .Close that will do it?

Second question: Could I accomplish the same thing using
msoFileDialogFilePicker instead of Open? I'm new at this and can't seem
to decipher whether "picking" a file lets you select a worksheet inside
it for copying or not.

Any help would be welcome.

Thanks,

Kelley

Clear Clipboard on Workbook Close Event (Excel XP)
 

Thanks, Tom. I tried the second one and it works great.

Kelley

Tom Ogilvy wrote:

Application.Cutcopymode = False

would be the first thing to try.

If that doesn't work, then

Application.DisplayAlerts = False