Exporting from Access to Excel - numbers change

[ladyck3]

First of all, I'm not real proficient in all of this so please bear with me all you knowledgeable folks out there.

I have a database with serial numbers, when I choose to export the list from the database into excel, it is changing them into those scientific numbers with + signs and the like. However, if I open the table and paste into excel, all's well. The USER will not have the ability to open the table, they will only have the data dump option into Excel. How can I fix this? Or is there something we can do after export to correct the issue?

I've tried to convert the data into numbers (multiply by 1 via Paste Special) and it works on most of them, but the ones below are examples of the actual data, what it appears as in Excel vs what is taken directly from the table in the database.

51E000D4 51E000D4
5.10E+124 51E00123
51E02E49 51E02E49
F1BE3D4E F1BE3D4E
NO ESN I330 -1 NO ESN I330 -1
5.10E+123 51E00122
F1BE7E39 F1BE7E39
51E0006D 51E0006D


Now, please understand, I do not know VBA so I'm hoping there is a setting or something we can do. I need this as simple as possible with no confusion because the end users do not have excel knowledge... much less Access.

I'm pretty proficient in Excel up to the point of VBA / Scripting so anything that you can do to help make life simpler would be so gratefully appreciated!!!

thanks...
Laurie
aka ladyck3
ladyck3@tx.rr.com


LadyCK3
aka: Laurie

 

[SkipVought]

Hi,

Take a look at using MS Query in Excel to Query your table in Access.

If you have conversion trouble with a simple query, then you could APPEND a TIC to that field and bring the value across as TEXT, which it REALLY should be, since you will not be doing arithmetic operations on a serial number or part number.

Skip,
[sub]
[red][/red]
[/sub]

 

[ladyck3]

Thanks Skip for responding...

Not sure about MS Query and pulling "from" a database... something I've not used before... but will look into it.

So in the database, set it to automatically put the ' in front of data in the field so it will export properly. I'm not the creator of the database but will provide this as a solution.

On question on the result... if the end user would do a Find/Replace to remove the ' from the data, would it automatically convert itself again or would it stay as it should be??

Just curious if you know... at this point in time I have no way of testing this and was just curious.

Thanks!

Laurie


LadyCK3
aka: Laurie

 

[SkipVought]

Nope, not even Left(A1,1) returns a TIC.

It's interpreted as a FORMAT and NOT a character.

I really don't believe it will affect the other application.

Skip,
[sub]
[red][/red]
[/sub]

 

[ladyck3]

Thanks Skip,

I'll let you know how it turns out.... there are currently about 2000 records in the database, will this address all of them or will we have to manually enter the tic on all of the existing records? <told you I'm no expert>

Appreciate the assistance... VERY MUCH!!

Laurie

LadyCK3
aka: Laurie