How to Convert Text Files into Excel Using VBA Script
By this vba code, you can convert all text files available in a folder into excel version.
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:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 | 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 |
Step 4: After adding the code, save the file. Go to the File menu and click on Save or press Ctrl+S.
Step 5: After saving the file, return to excel and you can run your macro from the macro options. So Go to the File menu and Click on the last option Close and Return to Microsoft Excel or press Ctrl+Q.
Note:- If you want to edit VBA code, then follow Step 1 and after editing, follow Steps 4 and 5.
0 Comments