Excel Macro - kill the clipboard!

[hoyaguru]

I'm running some Excel macros that take data from several different excel files and combine them. When the macro closes a file it gives me a prompt that there is information on the clipboard, and asks me if I want to use it later. I have never needed this information on the clipboard, EVER. So as far as I know I have two choices, sit here while the macro is running and continually click NO (up to 20 times with some of these macros), or use the following statement in the macro to close the file for me:

Application.DisplayAlerts = False
Workbooks("file.xls" & sName).Close SaveChanges:=False
Application.DisplayAlerts = True

which works fine, except it takes FOREVER to close the file (forever being anywhere from 20 seconds to over a minute). What I'd like to do is completely remove the clipboard function, or, find a different command to close a file without waiting so long. Any help?

 

[Concillian]

Instead of copying the information to the clipboard, you can read the information into variables in the VB code.

This has the bonus effect of making things run significantly faster as well.

You may or may not still have delays closing the file, but since you aren't copying information onto the clipboard any delays associated with clearing out that info will not occur. You won't get those prompts on closing the file.

 

[WW]

or insert this line before closing the file:

Application.CutCopyMode = False

or since the message only appears if 101 or more cells are copied into the clipboard, you could do this

ActiveSheet.Range("A1").Copy

 

[hoyaguru]

Originally posted by: WW
or insert this line before closing the file:

Application.CutCopyMode = False

or since the message only appears if 101 or more cells are copied into the clipboard, you could do this

ActiveSheet.Range("A1").Copy

Freakin beautiful, the ActiveSheet.Range("A1").Copy works like a charm. Just tried it in a small macro that imports from 8 different files, cut the import time down from a minute a 21 seconds to 9 seconds. THANK YOU!!!!