If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Date conversions....Excel thinking too hard?
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! |
#2
|
|||
|
|||
Date conversions....Excel thinking too hard?
On Thu, 24 Jun 2004 09:09:26 -0700, "Joel Wiseheart"
wrote: 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 |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Excel indicator for when a future date is reached! | glenrayel | Worksheet Functions | 0 | February 11th, 2004 11:55 AM |
How to turn off the automatic date feature in Excel 2003 | Worksheet Functions | 8 | January 13th, 2004 11:16 PM | |
lookup latest date Excel 2000 | Wanda Round | Worksheet Functions | 5 | December 28th, 2003 10:14 AM |
Excel date function | Sheela | Worksheet Functions | 2 | October 28th, 2003 10:12 AM |
Excel Functions in combination with Filters (Date Diffrance Calucaltion) | Arvi Laanemets | Worksheet Functions | 2 | September 18th, 2003 10:47 AM |