Macro to open *.dat files and save as .txt (comma delimited text files)
Hi,
Would appreciate if someone would enlighten me on how I can accomplish the following: 1. Open each of the dat files in a directory in excel. These dat files are semicolon delimited files. 2. Apply a set of actions to the opened files (which I've already saved), then save the files as the original name except for a different extension. i.e. .txt (comma delimited files). 3. How do I convert *001 in a field to display 001 instead of 1? Seems like Excel will autoconvert the format to number instead of text. 4. How do I remove the trailing spaces after a name without impacting the spaces within a name? For e.g. "Harry Lee " becomes "Harry Lee". Thanks in advance for any help rendered. YW |
Macro to open *.dat files and save as .txt (comma delimited text files)
Hello YehWei, Here is the macro to copy and convert the DAT file into TXT file (CSV). Add a VBA module to your project and place this code in it. You can then run it by selecting "*CsvToTxt*" from the Macro List (press ALT and F8 to display the list). ______________________________ Sub CsvToTxt() Dim Data As String Dim Ext As String Dim FF1 As Integer Dim FF2 As Integer Dim FileFilters As String Dim File_Name Dim FileName1 As String Dim FileName2 As String Dim I As Long Dim Msg As String 'Display the Open File dialog FileFilters = "Data Files (*.dat),*.dat,Text Files (*.txt),*.txt,All Files (*.*),*.*" File_Name = Application.GetOpenFilename(FileFilters) 'Get the file's extension I = InStr(1, File_Name, ".") If I 0 Then Ext = Right(File_Name, Len(File_Name) - I + 1) End If 'Check extension is .CSV If Ext < ".csv" Then MsgBox "Error - Source file must have .csv extension", vbCritical + vbOKOnly Exit Sub End If 'Set source and destination file names FileName1 = File_Name FileName2 = Left(File_Name, I) & "txt" On Error GoTo FileError 'Replace semicolons with commas FF1 = FreeFile Open FileName1 For Input As #FF1 FF2 = FreeFile Open FileName2 For Output As #FF2 Do While Not EOF(FF1) Line Input #FF1, Data Data = Replace(Data, ";", ",") Write #FF2, Data Loop Close #FF2 Close #FF1 Exit Sub FileError: Msg = "The following error has occurred" & vbCrLf _ & " Error #" & Err.Number & vbCrLf _ & " " & Err.Description MsgBox Msg, vbCritical + vbOKOnly End Sub ______________________________ To prevent Excel from converting the numbers, format the worksheets as TEXT. To remove the both leading and trailing spaces from a string, use TRIM(" Harry Lee "). The result is "Harry Lee". Sincerely, Leith Ross -- Leith Ross ------------------------------------------------------------------------ Leith Ross's Profile: http://www.excelforum.com/member.php...o&userid=18465 View this thread: http://www.excelforum.com/showthread...hreadid=488952 |
Macro to open *.dat files and save as .txt (comma delimited text files)
Thanks Leith!
One minor problem regarding (3). I tried to formating the worksheet as TEXT, but because the numbers have a unique form of "*011" with the asterisk infront, performing a text to column action on the selected column will automatically transform the *011 to 11 only. Would appreciate any help on this, very near to the completion of the macro. Thanks again! |
All times are GMT +1. The time now is 04:30 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com