Export to Excel Problem

[desperatemeasures]

Can anyone help me with this one:

When exporting a report to excel, it is converting some text fields to date fields

E.G. the field 2930-12, is being converted to 376536, which when you change the format the cell in excel to dd/mm/yyyy, becomes 12/1/2930.

Why is this happening. The format on the base table is text. I tried changing the number to 2930 12, but the same happens.

Any ideas?

 

[jebry]

Hi!

If you are doing a transferspreadsheet operation then Excel will set the data type to general usually and that is certainly what is happening here. You could use an Excel template to export to, that would allow you to have the Excel sheet preformatted the way you want it and will allow formatting changes to accomplished more easily. But it does require more coding and will take more time to make the data transfer.

hth
Jeff Bridgham
bridgham@purdue.edu

 

[desperatemeasures]

When exporting to excel, when the report is produced, I just choose export from the file menu, select Microsoft 97-2000 and then save, when opening it, it has changed the format. Im not sure if I am doing a transferspreadsheet operation. How would I go about doing the other option you mentioned?

 

[c0i0nes]

If you wish to manually force Excel to take numerics as text, you prefix the entry with a single quote : '. Can you use a query to concatenate a single quote to the beginning of the numeric text you are having problems with?

 

[desperatemeasures]

Hi

Thanks for that, I have done that in the query, and it is working fine, it is just that instead of seeing 2930-3, on the report it is '2930-3. Is there anyway of not seeing the ' ?

 

[c0i0nes]

You can strip off the leading character ( ' ) in Excel using the worksheet string handling function right(text,Len(text)-1) and formatting the resulting column as text, rather than as general