A
Anthony
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
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