Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Date format changed after saving to .csv
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Date format changed after saving to .csv
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
all cells in 2 workbooks have automaticlly changed to date format | Excel Worksheet Functions | |||
General Text format automatically get changed to DATE | Excel Discussion (Misc queries) | |||
Date column changed to number format while concatenating | Excel Discussion (Misc queries) | |||
Exported Date Format is Changed by Ecxcel | Excel Programming | |||
Exporting Data to Excel-Date Format Changed | Excel Discussion (Misc queries) |