[ASK] 1 macro to Convert txt into excel in mass

[esteem]

Dear gurus,

I need to convert txt into excel, I have large amount of txt file that need to convert into excel which has delimiter (eg, comma, tab, or pipeline ("|").
My requirement is to have txt file on one folder and the converted file in another folder.

My idea is:
1. Declare the folder name for the txt(this one should be variable), e.g. in cell A1 "c:\test\txt\"

2. set the folder name for the xlsx (this one should be variable), eg. in cell A2 "c:\test\excel\"

3. This one is nice to have, if we could Define the delimiter (this one should be variable also), eg. in cell A3 "|", or "," or "tab"

4. Last is Play button, to run the macro.

Maybe anyone can help me to build the macro for this ?

Thank you

 

[sergioMabres]

Hi Esteem
To help you create a macro you could start with some thing like this

Sub LoopFoldersAndConvert() 'reference Microsoft Scripting Runtime under Tools > ReferencesDim fso As New FileSystemObject
Dim mFile As File
Dim mFolder As Folder
Dim folderPath, myfile, mysep, outf As String
Dim tempbook As Workbook

folderPath = ActiveWorkbook.Sheets(1).Range("A1").Value 'Your folder goes here
outf = ActiveWorkbook.Sheets(1).Range("A2").Value
mysep = ActiveWorkbook.Sheets(1).Range("A3").Value

Set fso = CreateObject("Scripting.FileSystemObject")
Set mFolder = fso.GetFolder(folderPath)
 
For Each mFile In mFolder.Files
    myfile = mFile.Path
    myoutf = Replace(myfile + ".xls", folderPath, outf)
    Set tempbook = Workbooks.OpenText(myfile, xlMSDOS, 1, xlDelimited, xlDoubleQuote, False, _
    False, False, False, False, True, mysep)
    rc = tempbook.SaveAs(myoutf, xlExcel8, "", "", False, False)
Next mFile
End Sub

Beware macro has a reference
Good luck
Cheers
Sergio