Pasting Excel data to Access

[FonsPonsio]

When I have copied more than one cell in Excel (2007) and try to paste this
to Acess 2007 I get a message that "The data on the clipboard is damaged, or
there may not be enough free memory. Try operation again"
Trying again gives the same result, however if I paste the data to Word 2007
than copy all and paste to Acess the data copies correctly.
Thanks to Anyone who has an answer?

 

[NTC]

when you copy/paste beginning w/ Excel you copy both the presented value i.e.
25 and the underlying formula i.e. cellC1+cellC2 etc.

this is great when working within excel. but Access doesn't or at least,
may not , understand those underlying formulas. even if none of your cells
being copied have formulas - you are copying that capability inherent in the
copy process

but when you paste to Word, the MS people presume you want just the
presentation info, not the underlying formulas...so it strips that stuff
off....

thus when you go from Word to Access none of that underlying formula code is
there to cause a problem.

assuming you don't want a 2 step process - you can link to the excel sheet
and perform a query that will bring in data, you can import the excel sheet
into an access table, or you could export that excel sheet to word or txt ,
and then work with this as your manual data source

 

[FonsPonsio]

Thanks NTC. I am aware of the issues you pointed out however in prior
versions I used this procedure without any issues, since the error message
indicates that the clipboard is defective, I suspect some other issue here.
We should be able to copy data from multiple cells back and forth between
EXCEL and ACCESS, Any further Ideas?

 

[NTC]

an excel pro could tell you how to turn off the underlying formulas and just
have presentation info....you could post that question at the excel forum...

lots of potential reasons why a copy won't work but primarily they revolve
around the defined data type of the two fields i.e. text vs number (long,
double, integer, decimal???) being the most obvious....not sure that the
error message is giving the most clear indication of the real problem being
the clipboard...

sanity check is to copy access to access and excel to excel...if that works
it is doubtful that it is clipboard and more probable it is mismatching field
definition issues...

 

[George]

http://support.microsoft.com/kb/958437

 

[Simon Lloyd]

Using this line of code you can make the whole sheet just values with n
underlying formulae, but beware to only do it on a copied sheet as ther
is no going back!

Code
-------------------
ActiveSheet.UsedRange = ActiveSheet.UsedRange.Valu
-------------------

an excel pro could tell you how to turn off the underlying formulas an
jus
have presentation info....you could post that question at the exce
forum..

lots of potential reasons why a copy won't work but primarily the
revolv
around the defined data type of the two fields i.e. text vs numbe
(long
double, integer, decimal???) being the most obvious....not sure tha
th
error message is giving the most clear indication of the real proble
bein
the clipboard..

sanity check is to copy access to access and excel to excel...if tha
work
it is doubtful that it is clipboard and more probable it is mismatchin
fiel
definition issues..

--
Simon Lloy

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com

 

[FonsPonsio]

Thanks George, thought this did not fix the problem, at least I know I am not
alone and that MSis working the problem.

 

[FMS Development Team]

Thanks George, thought this did not fix the problem, at least I know I amnot
alone and that MSis working the problem.






- Show quoted text -

Here's an article I wrote a few weeks ago that offers three
workarounds for this problem. Hope it helps:
http://www.fmsinc.com/MicrosoftAccess/Errors/ExcelPaste/Clipboard.html

Good luck.

Luke Chung
FMS, Inc.
http://www.fmsinc.com

 

[FMS Development Team]

When I have copied more than one cell in Excel (2007) and try to paste this
to Acess 2007 I get a message that "Thedata on the clipboard is damaged, or
there may not be enough free memory.  Try operation again"
Trying again gives the same result, however if I paste the data to Word 2007
than copy all and paste to Acess the data copies correctly.  
Thanks to Anyone who has an answer?

Here's an article I wrote a few weeks ago that offers three
workarounds for this problem. Including options that don't require
removing the security patch. Hope it helps:
http://www.fmsinc.com/MicrosoftAccess/Errors/ExcelPaste/Clipboard.html

Good luck.

Luke Chung
FMS, Inc.
http://www.fmsinc.com

 

[David Owen Hewitt]

The issue is caused by an Update to Excel KB958437 which causes this issue,
you need to uninstall the patch by going into Control Panel then Programs and
Features, then View Installed Updates, uninstall KB958437, then go to Windows
Update, check for updates then untick KB958437 then right click it and Hide.
In Mid April 2009 KB959997 installs and you need to follow the same steps
with that one too.

 

[Curtis Stevens]

I brought this issue to Microsoft's attention back with KB958437 and spoke to
someone over there. Explained the problem and said it goes away when you
uninstall the update. Now a new one is causing it too.

Argh!!