I wrote a VBA to open a comma seperated .txt file in excel. I've a column of
data where the date format was changed from yyyy/mm/dd hh:mm:ss to yyyy-mm-dd
hh:mm:ss. I need to keep the original date format. How do I do this?
When I save the file back to comma seperated .txt file, the date format
changed to mm/dd/yyyy hh:mm.
Below are my code. Help to solve this issue is greatly appreciated

Private Sub OpenTxt()
'let user select the file to open
fn = Application.GetOpenFilename("Txt-files,*.txt", _
1, "Select Raw Dump File", , False)
If TypeName(fn) = "Boolean" Then Exit Sub
' the user didn't select a file
Debug.Print "Selected file: " & fn

Dim wb As Workbook

Workbooks.OpenText Filename:=fn, Origin:=437, _
StartRow:=1, DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Semicolon:=False, Comma:=True
End sub

Private Sub SaveAsTxt()

Dim strFileName As String
Dim lngLastSlash As Long
strFileName = Application.GetSaveAsFilename
lngLastSlash = InStrRev(strFileName, "\")
strFileName = Left(strFileName, lngLastSlash) & "" & _
Mid(strFileName, lngLastSlash + 1, 256)

ActiveWorkbook.SaveAs Filename:= _
strFileName, FileFormat:=xlCSV _
, CreateBackup:=False

i = 0
While InStr(i + 1, strFileName, Application.PathSeparator) > 0
i = InStr(i + 1, strFileName, Application.PathSeparator)
Wend
strFileName = Right(strFileName, Len(strFileName) - i)

Dim wb1 As Workbook
For Each wb1 In Workbooks
If wb1.Name = strFileName Then wb1.Close False 'close without save
Next

End Sub

Thanks in Advance
Anthony

A CSV file is just a text file with a differrent extension. There is no
formating information in the file. I ran your save macro and then opened the
CSV file witth notebook. The data was saved in the correct format. the
problem is with the was excel reads a CSV file. The Read in excel is
converting the date into a oknow date format.

The only solution to your problem is to re-formatt the data before or after
it is read by excel. I'm don't know how you are opening this CSV file. If
you are opening it up from windows you can put a macro (on open) into your
personal.xls file that would format the worksheet with the correctt date
format. If you open the CSV from another workbook you can add to a macro the
format.

"Anthony" wrote:

> I wrote a VBA to open a comma seperated .txt file in excel. I've a column of
> data where the date format was changed from yyyy/mm/dd hh:mm:ss to yyyy-mm-dd
> hh:mm:ss. I need to keep the original date format. How do I do this?
> When I save the file back to comma seperated .txt file, the date format
> changed to mm/dd/yyyy hh:mm.
> Below are my code. Help to solve this issue is greatly appreciated
>
> Private Sub OpenTxt()
> 'let user select the file to open
> fn = Application.GetOpenFilename("Txt-files,*.txt", _
> 1, "Select Raw Dump File", , False)
> If TypeName(fn) = "Boolean" Then Exit Sub
> ' the user didn't select a file
> Debug.Print "Selected file: " & fn
>
> Dim wb As Workbook
>
> Workbooks.OpenText Filename:=fn, Origin:=437, _
> StartRow:=1, DataType:=xlDelimited, _
> TextQualifier:=xlDoubleQuote, _
> ConsecutiveDelimiter:=False, Semicolon:=False, Comma:=True
> End sub
>
> Private Sub SaveAsTxt()
>
> Dim strFileName As String
> Dim lngLastSlash As Long
> strFileName = Application.GetSaveAsFilename
> lngLastSlash = InStrRev(strFileName, "\")
> strFileName = Left(strFileName, lngLastSlash) & "" & _
> Mid(strFileName, lngLastSlash + 1, 256)
>
> ActiveWorkbook.SaveAs Filename:= _
> strFileName, FileFormat:=xlCSV _
> , CreateBackup:=False
>
> i = 0
> While InStr(i + 1, strFileName, Application.PathSeparator) > 0
> i = InStr(i + 1, strFileName, Application.PathSeparator)
> Wend
> strFileName = Right(strFileName, Len(strFileName) - i)
>
> Dim wb1 As Workbook
> For Each wb1 In Workbooks
> If wb1.Name = strFileName Then wb1.Close False 'close without save
> Next
>
> End Sub
>
> Thanks in Advance
> Anthony