[AccessD] Not all values paste from Excel to Access

Stuart McLachlan stuart at lexacorp.com.pg
Sun Mar 18 16:05:34 CDT 2012


It's not a "bug" in Access, just the way that things work if you are using Excel Copy and 
Access Paste and  the Clipboard.  

When working within an application, the Clipboard can use "Registered Formats" and 
"Private Formats" which lets Excel store metadata about the values. 

When working between applications, the Clipboard is basically limited to "Standard Formats"  
so Access can't get that metadata and has to examine the actual text on the Clipboard and 
make assumptions about it. 

Apart from a couple of  odd formats, the clipboard is basically limited to holding images and 
chunks of text and Access can only use what Excel puts there.

It appears that Access "guesses" the format based on the first ten rows (which in your case is 
a header and 9 numeric values) the same way that it does with Import from a text file. 

-- 
Stuart

On 18 Mar 2012 at 20:15, Benson, William (GE Global Re wrote:

> I want to say from the start, I worked around this problem by changing my Excel fields to text number format, but here goes, I still think this is a bug in Access.
> 
> I set up a table called Equipment Model with 3 fields. The first was an autonumber, the next two were text. In Excel I had a table like this:
> 
> Blank Field	Equipment Code		Equipment Model
> 		CIGS				85
> 		CIGS				90
> 		CIGS				130
> 		CIGS				135
> 		CIGS				140
> 		CIGS				145
> 		CIGS				150
> 		CIGS				155
> 		CIGS				160
> 		Controller			SunIQ
> 		Inverter			700kW
> 		Inverter			714.2kW
> 		Inverter			1MW
> 		Skid (STP)			Transformer
> 
> I copied the three columns from Excel and pasted into the Access table. Only the items up to and including the Equipment Model = 160 record got pasted.
> 
> If it was a numerical field, I could understand it, but it is a text field. And it is a really small number of records, so I can hardly picture Access getting confused.
> 
> So I went back to Excel and checked NumberFormat - it was General. (At this point I am thinking "So what, there are some text entries and besides, the field is set up as text in Access").
> 
> Anyway, I changed number format in Excel to Text, and retried. They all pasted that time.
> 
> I put this down as learning to deal with Microsoft's craziness, but I am happy for someone to point out any improper expectation on my part as a user/developer!
> 
> 
> 
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
> 




More information about the AccessD mailing list