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.

Clipboard is damaged

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.

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

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.)

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:

Office Clipboard


Then paste the data from the Office clipboard:

Office Clipboard Paste

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

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 Update

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
  • 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 post Security Patch for Excel Prevents Pasting into Access Tables, where additional suggestions are offered by our community.


Table Design

Query Design

Form Design

Form Tips and Mistakes

Copy Command Button and Keep Picture

Module VBA to Forms and Controls

Form Navigation Caption

Resync Record in a Subform

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 6 Tips

Properties and Validation

Select First Item

Cascading Combo Boxes

Zip, City, State AutoFill

Report Design

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

Add the NoData Event

Annual Monthly Crosstab Columns

Design Environment

Add Buttons to the Quick Access Toolbar

Collapse the Office Ribbon for more space

VBA Programming

Basics: Forms and Controls

Run VBA Code from a Macro

Use Nz() to Handle Nulls

Avoid Exits in the Body of a Procedure

Shortcut Debugging Keys

Set Module Options

Math Rounding Issues

Rename a File or Folder

Avoid DoEvents in Loops

Age Calculations

Weekday Math

Send Emails with DoCmd.SendObject

Source Code Library

Microsoft Access Modules Library

Microsoft Access 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 SourceSafe

Deployment

Runtime Downloads

Simulate Runtime

Prevent Close Box

Disable Design Changes

Broken References

Remote Desktop Connection Setup

Terminal Services and RemoteApp Deployment

Reboot Remote Desktop

Missing Package & Deployment Wizard

Avoid Program Files Folder

Unavailable Mapped Drives

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

SQL Server Upsizing

Microsoft Access to SQL Server Upsizing Center

Microsoft Access to SQL Server Upsizing Center

When and How to Upsize Access to SQL Server

SQL Server Express Versions and Downloads

Cloud and Azure

Cloud Implications

MS Access and SQL Azure

Deploying MS Access Linked to SQL Azure

SQL Server Azure Usage and DTU Limits

Visual Studio LightSwitch

LightSwitch Introduction

Comparison Matrix

Additional Resources

Connect with Us

 

Free Product Catalog from FMS