How to Convert Text Files into Excel Using VBA - Step by Step Guide

By this vba code, you can convert all text files available in a folder into excel.

This code is very simple, just put in your code window and you are done.

Folder Location: C:\Source Folder\

Step 1: Open Excel and Press key Alt+F11. It will open Microsoft Visual Basic for Applications Window.

Step 2: Go to Insert >> Module menu.

Step 3: Copy below code and paste in the code window:

Sub Text_to_Excel()

'****************** Section 1 ******************

'Define variables

Dim wb As Workbook

Dim filename As String

Dim directory As String

'directory name

directory = "C:\Source Folder\"

'file name

filename = Dir(directory & "*.*")

'****************** Section 2 ******************

    'Loop Start here

    'It will check if there any file available

    Do While filename <> ""

       

        'It will open first file in the directory

        Set wb = Workbooks.Open(directory & filename)

            With wb

                'it will save file with new extension

                ActiveWorkbook.SaveAs wb.Name & ".xlsx"

                .Close False

            End With

        Set wb = Nothing

       

        'it will check if more files available to convert

        filename = Dir

   

    'Loop will continue to last file available in folder

    Loop

    

End Sub

***** End *****