[AccessD] Not all values paste from Excel to Access

William Benson vbacreations at gmail.com
Sun Mar 18 17:04:14 CDT 2012


Bug bug bug!

Nyah.

But thanks for a logical answer Stuart.
 On Mar 18, 2012 5:06 PM, "Stuart McLachlan" <stuart at lexacorp.com.pg> wrote:

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