Set Cell.Value to the Formated value
Hi All -
Simple one I think, but I can't connect the dots. Cell.value is a date with custom number formatting. File is saved off to tab delimited text. When reopened in Excel, Excel default formats the dates, as the format didn't stay with the file. Not what I want. How to I get around it? I'm thinking I have to change the date to a string and prepend an apostrophe. But what I'm thinking ain't working. Thanks in advance. ....best, Capt N. -- Scream and Shout and Jump for Joy! I was here before Kilroy! Sorry to spoil our little joke. I was here but my computer broke. ....Kilroy |
Set Cell.Value to the Formated value
I'm not sure where the problem lies. You need to open the CSV file with a
text editor (not excel) like using NotePad and check to see the way the data was saved. Then you can determine the correct way of fixing the problem. CSV contains no formating, the data is just text with each column seperated by a comma. Here are some typical solutions 1) If the data was not saved properly then write a macro to save the datz in the correct format. This is pretty simple. 2) If the data was save properly then change the extension of the file from CSV to TXT. when a TXT file is opened with excel a pop up Wizard will appear that lets you select options. Choose Delimited and set the delimiter character to "Comma" then you can c hoose how each column is formated using the wizard selections. 3) If the data was save properly then after the file is opened reformat the column the way you need. 4) Use a templet with the columns formated the way you want. Open the templet then open the CSV file using the templet. "Captain Nemo" wrote: Hi All - Simple one I think, but I can't connect the dots. Cell.value is a date with custom number formatting. File is saved off to tab delimited text. When reopened in Excel, Excel default formats the dates, as the format didn't stay with the file. Not what I want. How to I get around it? I'm thinking I have to change the date to a string and prepend an apostrophe. But what I'm thinking ain't working. Thanks in advance. ....best, Capt N. -- Scream and Shout and Jump for Joy! I was here before Kilroy! Sorry to spoil our little joke. I was here but my computer broke. ....Kilroy |
Set Cell.Value to the Formated value
If you want to keep it a date, then you're going to have to live with the way
excel does it. Then you can reformat it later. If you can live with the value being text, you could use what Joel suggests (rename to .txt and specify that field as text). But in any case, you'll want to make sure that the field is brought in as the correct date (or representing the correct date). If I save a field in mdy format (01/02/03) and you open that file and import the field and your windows date settings are not mdy order, you may see something that looks like a date--but it won't be the one I meant. Captain Nemo wrote: Hi All - Simple one I think, but I can't connect the dots. Cell.value is a date with custom number formatting. File is saved off to tab delimited text. When reopened in Excel, Excel default formats the dates, as the format didn't stay with the file. Not what I want. How to I get around it? I'm thinking I have to change the date to a string and prepend an apostrophe. But what I'm thinking ain't working. Thanks in advance. ...best, Capt N. -- Scream and Shout and Jump for Joy! I was here before Kilroy! Sorry to spoil our little joke. I was here but my computer broke. ...Kilroy -- Dave Peterson |
Set Cell.Value to the Formated value
In article ,
Joel wrote: Hi Joel - It's tab-delimited, not CSV, but I doubt that makes a difference. I'll keep plugging...the problem is that the file is created in Excel, but imported into Notes, and it's Notes that's particular. It leaves Excel right the first time, but wrong the second time if I have to make an update instead of an entirely new version. The question is going to be what Notes does when if gets a field with a leading apostrophe. I know how to find out ;-) Thanks. ....best, Capt N. I'm not sure where the problem lies. You need to open the CSV file with a text editor (not excel) like using NotePad and check to see the way the data was saved. Then you can determine the correct way of fixing the problem. CSV contains no formating, the data is just text with each column seperated by a comma. Here are some typical solutions 1) If the data was not saved properly then write a macro to save the datz in the correct format. This is pretty simple. 2) If the data was save properly then change the extension of the file from CSV to TXT. when a TXT file is opened with excel a pop up Wizard will appear that lets you select options. Choose Delimited and set the delimiter character to "Comma" then you can c hoose how each column is formated using the wizard selections. 3) If the data was save properly then after the file is opened reformat the column the way you need. 4) Use a templet with the columns formated the way you want. Open the templet then open the CSV file using the templet. "Captain Nemo" wrote: -- Scream and Shout and Jump for Joy! I was here before Kilroy! Sorry to spoil our little joke. I was here but my computer broke. ....Kilroy |
Set Cell.Value to the Formated value
Cp'n
I am having similar issue with dates in tab delimited file showing up in wrong formatt when excel opens. Forgive the simple question, but are you saying that in your tab delimited file you have your date value and after the date value, but before the tab seperator you add Chr(160) then tab... see example .....tab2007-01-01Chr(160)tab....... and if you had more than one date value in each line in the file you would do the same thing for each date value? Cheers, L :) -- It it''s meant to be, it won''t pass you by! "Captain Nemo" wrote: In article , Captain Nemo wrote: In article , Joel wrote: Hi Joel - It's tab-delimited, not CSV, but I doubt that makes a difference. I'll keep plugging...the problem is that the file is created in Excel, but imported into Notes, and it's Notes that's particular. It leaves Excel right the first time, but wrong the second time if I have to make an update instead of an entirely new version. The question is going to be what Notes does when if gets a field with a leading apostrophe. I know how to find out ;-) Thanks. ...best, Capt N. Found the answer. Prepended apostrophes disappear when saved off as text. Appended apostrophes do not. So, I appended Chr(160) instead and Excel and Notes are happy, and the date looks like the date needs to look. Thanks to all. ....best, Capt N. -- Scream and Shout and Jump for Joy! I was here before Kilroy! Sorry to spoil our little joke. I was here but my computer broke. ....Kilroy |
All times are GMT +1. The time now is 03:33 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com