DoCmd.OutputTo weirdness...

[cyberdwarf]

Using standard DoCmd.OutputTo syntax to export a simple dataset to an Excel
worksheet.

Works well, but......

we have a list of text codes (YD, 2M, 7G, etc) which come across into the
cells ok - all except codes like '11A' and '10A' and these come into Excel as eg '0.458333333' and '0.416666666666667' respectively.

Reformatting as text/general has no effect at all.

Is there something I'm missing about the output process which would cure this quirk?

Any ideas gratefully received

TIA

Steve

[NeoPa]

Do you actually export these strings in quotes?
It seems Excel will interpret the 11A & 10A as 11 or 10 times some strange value A (.0416666666666667)
I don't know what A stands for, but it is similar to Excel converting '1E2' to a value equal to 1 * E^2.
I can't for the life of me remember what E is but it's a logarithmic value used as an alternative to 10 in certain situations.
Anyway, this is an irritating by-product of Excel where it is always trying to second guess your data and makes specifying it explicitly difficult.
Try adding quotes into the data stream, hoping that Excel will process them as signifying string data rather than general data which may be string or numeric.

[cyberdwarf]

Hi, NeoPa,

Yup, works that way!

Wish my second guesses were that clever <G>

Thx

Steve

[NeoPa]

I've had problems importing into / from Excel before ;)

[Killer42]

I've had problems importing into / from Excel before ;)

Howdy NeoPa. I'd just like to clear up a technical point here. 1E2 means 1 * 10^2, not 1 * E^2.
This could cause confusion if you ever need to use it.

[NeoPa]

Hi Killer.
I know I'm vague on this one so you're probably right here - but I thought there was another logarithmic base other than 10 that's commonly used.
But when E is used it refers to base 10?

[Killer42]

Hi Killer.
I know I'm vague on this one so you're probably right here - but I thought there was another logarithmic base other than 10 that's commonly used.
But when E is used it refers to base 10?

You can do logarithms to base e (written as lowercase, I believe - not that I would claim to understand it) but in various products such as Excel they just use "E" as a short way to indicate "exponential".

This may be just a convention chosen by Microsoft, rather than a mathematical term.

[cyberdwarf]

To NeoPa et al

A short postscript...

11A is interpreted in Excel as being a time type (11AM) and this value is then translated into a decimal value.

Why? Don't ask me, but it is definitely not intuitive IMHO, especially since most of the data in the same column is clearly text type...

Ho hum!

Steve

[NeoPa]

Nice one CyberDwarf.
That's exactly the sort of thing I was expecting but didn't know the details.
Thanks for the info.

And, No - Excel can be very strange at times, but it is designed to get people going with the most basic understanding - rather than to work intuitively for people who actually know what they're doing.
It sells better that way :(.

Killer,
Thanks for that too - that reminds me of what I once knew... A senior moment ;).