Date conversions....Excel thinking too hard?

[Joel Wiseheart]

We have many programs (Access, SAP, etc.), that when they
try to export to Excel, Excel always tries to convert our
part numbers into dates.

For instance, we have a part number, 1944-1-5, that needs
to be exported to Excel as a text value, 1944-1-5, not
converted to a date, like 1/5/1944.

And no, putting quotes around it is not practical, since
we have thousands of part numbers.

I have successfully exported from Access using the
TransferSpreadsheet method in a macro or VBA (instead of
the standard "File | Export..." menu selection) without
the date conversion occuring. However, this is very
difficult to train to less advanced users. Also, our SAP
ERP system has no such export utility.

My question is, is there a way to turn off the date
conversions from the Excel end of things? For instance,
is there a flag in the options somewhere to turn this
functionality off?

Thank you!

 

[Ron Rosenfeld]

We have many programs (Access, SAP, etc.), that when they
try to export to Excel, Excel always tries to convert our
part numbers into dates.

For instance, we have a part number, 1944-1-5, that needs
to be exported to Excel as a text value, 1944-1-5, not
converted to a date, like 1/5/1944.

And no, putting quotes around it is not practical, since
we have thousands of part numbers.

I have successfully exported from Access using the
TransferSpreadsheet method in a macro or VBA (instead of
the standard "File | Export..." menu selection) without
the date conversion occuring. However, this is very
difficult to train to less advanced users. Also, our SAP
ERP system has no such export utility.

My question is, is there a way to turn off the date
conversions from the Excel end of things? For instance,
is there a flag in the options somewhere to turn this
functionality off?

Thank you!

The way to defeat the conversion is to import the data as TEXT and using the
Text to Columns wizard, specifying the particular column as being TEXT.

I'm not familiar with your other programs. But if you could set up your export
routine to be some kind of standard (CSV?) and then set up a macro to do your
importing into Excel, you could ensure that the file comes in as a TXT file and
that the appropriate column gets imported as TEXT rather than as General or
Date.


--ron