Output to excel

C

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', '10A' and the come into Excel as eg
'0.458333333', '0.416666666666667'

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
 
D

Dave F

What happens if you copy the data from the data table/query and paste
directly into XL? Do you get the same error?
 
C

Cyberwolf

What data type is that field set to in your table. THat could affect the
output in excel.
 
G

Gord Dibben

11A and 10A are coming into Excel as times formatted to General.

..45833333 is 11:00AM

Can you change the data type at the source?

Could you pre-format the Excel cells as text?


Gord Dibben MS Excel MVP
 
H

Harlan Grove

Gord Dibben wrote...
....
Can you change the data type at the source?

Could you pre-format the Excel cells as text?
Click to expand...

While the OP doesn't state it explicitly, he's using Access.
Specifically, running the Access VBA statement DoCmd.OutputTo to create
Excel worksheets. That eliminates pre-formatting as an option.

As for changing the data type, if it looks like 10A or 11A in Access,
then it should already be text in Access. On my system with Access 2003
and Excel 2003, text exported by Access using DoCmd.OutputTo creates
Excel worksheet files that show the fields as text, so 10A and 11A not
converted to time values.

OP needs to provide more details, beginning with Access version and
field data type details.
 
R

Ron2006

I think I know what the problem is, but not sure what the solution will
be just yet.

I believe that your code is not reall 11A but 11 A (An 11 then a
space then A). But even if it is not that Excell is processing the code
as if it were a "Date/Time" entry.

Go into excel to a cell and type in 11 (space) A and you will see 11:00
AM and if you change that to a number you get the .46 or .458 that you
are seeing.

Now the trick is to try to fake excell out.
Maybe add a leading space or a leading single quote mark to make
Excell NOT treat it as a time. (if leading space then range that cell
or column and change all spaces to nothing.

Ron.
 
C

CyberDwarf

Sorry to take so long to respond (need to sleep sometime!!)

Yup, you guys have hit the nail on the head - Excel is assuming 11A is time
data....

AND yup, this is coming out of Access (v2003).

Have found that I can force Excel to format as text by enclosing data in
quotes on output.
Do-able, but a royal PITA!!

Thanks for all your help

Steve
 
C

CyberDwarf

Sorry to take so long to respond (need to sleep sometime!!)

Yup, you guys have hit the nail on the head - Excel is assuming 11A is time
data....

AND yup, this is coming out of Access (v2003).

Have found that I can force Excel to format as text by enclosing data in
quotes on output.
Do-able, but a royal PITA!!

Thanks for all your help

Steve
 
R

Ron2006

If you are being selective on which ones to put in quotes, then don't
forget about P for PM.

Glad we could point you in the right direction.

Ron
 
Top