Update: On April 24, 2009, Microsoft released the 2007 Office Suite Service Pack 2 (SP2) which resolved this problem.
The article below still applies if you haven't or can't install the service pack.
To discuss it further, visit our FMS Development Team Blog on this topic.
This article describes three workarounds for a disturbing change in the interoperability in Microsoft Office between Excel and Access. One of the nice things about Microsoft Access is that it's easy to copy and paste data to and from Excel spreadsheets. Unfortunately, all of a sudden in December, an error appears when you try to copy data from Excel and paste it into Access.
The additional text says:
There may be an error in the Clipboard, or there may not be enough free memory. Try the operation again.
At first, you may think it's a problem with your spreadsheet data, or maybe you need to reboot. But no matter what Excel data or how much data you copy to your Clipboard, it still fails. Same for trying to close programs to free more memory.
It turns out you can still import an Excel spreadsheet into Access without a problem. So what's going on with Paste when it's been working for years?
It turns out that this behavior is due to one of two Microsoft security patches.
Most PCs automatically install these patches when Microsoft releases them. Unfortunately, this patch has side effects. All of a sudden, Excel and Access no longer work together properly.
There are two workarounds that allow you to paste Excel data into Access without uninstalling the Excel security update. (Special thanks to FMS Bloggers Jason Kelley and Kari for providing these workarounds.)
After copying the data to the clipboard, close Excel. If prompted with this message:
There is a large amount of information on the clipboard. Do you want to be able to paste this information into another program later?
Click Yes to keep the data on the clipboard. Now you can paste it into Access without problems.
To paste the data without closing Excel, you can paste from the Office clipboard. First, open the Office Clipboard from the Home ribbon:
Then paste the data from the Office clipboard:
The workarounds are useful but a hassle if you need paste data repeatedly. To fix the problem so you don't need to change your normal behavior, you can uninstall the Excel security update that is causing the problem. Make sure Excel is not running when you do this.
Uninstall the security patch from the Windows Control Panel.
In Vista, go under Programs, Uninstall a Program, and choose the "View installed updates" option on the left pane:
Select the offending patch:
and choose Uninstall. Reboot and you'll be able to cut, copy and paste your Excel data into Access.
To prevent Windows from automatically applying this patch again the next time it seeks an update, you'll need to turn off automatic updates. Unfortunately, this has security implications, so you may not be allowed to do this.
Because of problems I've encountered in the past, I've set my Windows updates to be downloaded and manually installed.
When updates are available, an icon appears in the task bar. Click that and select which updates to install while intentionally avoiding:
Yes, this is a pain but until you install SP2, you need to do this or lose the interoperability functionality.
Discuss this issue further on our blog post Security Patch for Excel Prevents Pasting into Access Tables, where additional suggestions are offered by our community.