FMS Home FMS Software Development Team Blog FMS Facebook Page FMS Twitter
Jump: Search:

Microsoft Access Developer Center

Table Design

Query Design

Form Design

Form Tips and Mistakes

Form Navigation Caption

Using a RecordsetClone

Synchronize Two Subforms

Multiple OpenArgs Values

Late Bind Tab Subforms

Subform Reference to Control Rather than Field

Tab Page Reference

Shortcut Keys


Combo Box Top Tips

Properties and Validation

Select First Item

Cascading Combo Boxes

Zip, City, State AutoFill

Report Design

Suppressing Page Headers and Footers on the First Page of Your Report

Add the NoData Event

Annual Monthly Crosstab Columns

Design Environment

Adding Buttons to the Quick Access Toolbar

Collapsing the Office Ribbon for more space

VBA Programming

Using Nz() to Handle Nulls

Avoiding Exits in the Body of a Procedure

Debugging Keys

Setting Module Options

Math Rounding Issues

Rename a File or Folder

Source Code Library

Microsoft Access Module VBA Library

Royalty Free VBA Modules

VBA Error Handling

Error Handling and Debugging Techniques

Error Number and Description Reference

Basic Error Handling

Pinpointing the Error Line

Performance Tips

Linked Database

Subdatasheet Name

Visual Source Safe

Deployment

Runtime Downloads

Simulating Runtime

Prevent Close Box

Disable Design Changes

Broken References

Missing Package & Deployment Wizard

Terminal Services and RemoteApp Deployment

Avoid Program Files

Microsoft Access Front-End Deployment

System Admin

Disaster Recovery Plan

Compact Database

Compact on Close

Database Corruption

Class Not Registered Run-time Error -2147221164

Inconsistent Compile Error

Decompile Database

Bad DLL Calling Convention

Error 3045: Could Not Use

Converting ACCDB to MDB

Cloud and Azure

Cloud Implications

MS Access and SQL Azure

Deploying MS Access Linked to SQL Azure

Visual Studio LightSwitch

LightSwitch Introduction

Comparison Matrix

Additional Resources

Microsoft Access Help

MS Access Developer Programming

More Microsoft Access Tips

Technical Papers

Microsoft Access Tools

Connect with Us

Email NewsletterEmail Newsletter

FMS Development Team BlogDeveloper Team Blog

Facebook PageFacebook

Twitter with FMSTwitter

FMS Support Site Support Forum

 

 

Error Pasting Data from Microsoft Excel 2007 to Microsoft Access

Provided by: Luke Chung, President
Date: December 2008, Revised May and July 2009

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:

Error 2227: The data on the Clipboard is damaged, so Microsoft Office Access can't paste it.

The data on the Clipboard is damaged, so Microsoft Office Access can't paste it.

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?

Problem with Security Update for Microsoft Office Excel 2007 (KB958437)

It turns out that this behavior is due to one of two Microsoft security patches.

  • The first was distributed on 9 December 2008, visit KB958437.
  • The second was distributed on 24 February 2009 (KB967699)

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.

Temporary Workarounds

There are two workarounds that allow you to paste Excel data into Access without uninstalling the Excel security update. (Special thanks to FMS Blogers Jason Kelley and Kari for providing these workarounds.)

Workaround 1: Close Excel

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.

Workaround 2: Paste from the Office Clipboard

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:

Permanent Solution: Uninstall the Excel Security Update

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:

Uninstall the Security Update for Microsoft Office Excel 2007 (KB958437)

Select the offending patch:

  • Security Update for Microsoft Office Excel 2007 (KB958437)
  • Security Update for Microsoft Office Excel 2007 (KB967699)

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.

Download updates but let me choose whether to install them for Windows Updates on Vista

When updates are available, an icon appears in the task bar. Click that and select which updates to install while intentionally avoiding:

  • Security Update for Microsoft Office Excel 2007 (KB958437) dated December 9, 2008, and
  • Security Update for Microsoft Office Excel 2007 (KB967699) dated February 24, 2009

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 where additional suggestions are offered by our community.

Additional Resources

Here are some related resources:

Hope this helps.  Good luck!

View all FMS products for Microsoft Access Discover our Microsoft Access Resources

Related Products:

For data analysis beyond the power of Microsoft Access queries:

Advanced Data Analysis in Microsoft Access with Total Access Statistics


Database documentation and analysis including object cross-reference, performance optimization, and finding unused queries:

Finding unused Microsoft Access Queries with Total Access Analyzer


Well written and tested modules and classes that you can use royalty-free:

Royalty free Microsoft Access, VBA, and VB6 module code with Total Visual SourceBook


Tools to standardize and enhance your module code, including an ADO and DAO Recordset Builder:

Standardize VBA/VB6 Module Code with Total Visual CodeTools


Send personalized emails with your Access data and reports:

Email Microsoft Access Reports and Data with Total Access Emailer


Microsoft Access Developers
Need help? Experienced Access programmers and developers at your service.
www.fmsinc.com

Access to SQL Server Migration
Let us help you upsize your Microsoft Access databases to SQL Server and .NET
www.fmsinc.com

Feedback

Contact Us  l   Web questions: Webmaster   l   Copyright FMS, Inc., Vienna, Virginia
Celebrating our 27th Year of Software Excellence