Macro to convert TXT to CSV files

[neobavesten]

Hi,

I have a bulk of TXT files organized in same format. Is the a way to write a macro that could convert those text files to *csv files?

Text file contains multiple sentences. I would like to keep that text as one block in CSV file.

I would really appreciate if someone could help me with this.
Many thanks!

 

[diddi]

add this in the userform code area as well...

Sub DoConvert()
    Application.Cursor = xlWait
    Open SelFolder & "Combined Files.txt" For Output As #2
    For row = 0 To ListBox1.ListCount - 1
        Open SelFolder & ListBox1.List(row) For Input As #1
        res=""
        while not eof(3)
            line input #3,sdata
            if len(sdata)>18 then
                res=res & chr(9) & sdata
            end if
        wend
        print #1,res
    next row
    close
end sub

 

[neobavesten]

Many thanks for your effort. Well this is getting more and more complex.
After running macro I select the folder that contains the text files but right after that get an error: "User-defined type not defined"

It points to this part of code:

Function AllFiles(ByVal FullPath As String) As String()
    Dim oFs As New FileSystemObject

 

[diddi]

in the VBE goto tools>references and enable microsoft scripting runtime

 

[neobavesten]

Done. After compiling I get an error: "Only comments may appear after End Sub,End Function, or end Property".
It directs to this part of the code:

ErrHandler:
    Set oFs = Nothing
    Set oFolder = Nothing
    Set oFile = Nothing
End Function
End Function

 

[diddi]

youve got 'end function' twice. delete one

 

[neobavesten]

Now I get Run-time error '424' Object required. It points to:

If Right(sFiles(row), 4) = Right(ComboBox1.List(ComboBox1.ListIndex, 1), 4) Then

 

[diddi]

sorry about that. this is part of a larger project which allows selection of different file types.

    If Right(sFiles(row), 4) = ".txt" Then

ill check through for other problems...

 

[neobavesten]

Many thanks Diddi!!!

Now it load files. Results ar shown in user form (number of loaded files). It stops there. Is there anything else I need to do?

 

[diddi]

so there is your list of files!

now if you click the other commandbutton, it should process them and combine all files into 1 result called 'combined files' on C:\ and whats more it should be CSV

 

[diddi]

put this in as well

Sub userform_initialize()
    commandbutton2.visible=false
end sub