VBA to convert large volume of text files (*.txt) to (*.xlsm) files

[Dojorgen]

First timer, high excel skills but low VBA skills.

I have 1000+ .txt files that i need to convert to .xlsm files on a daily basis. I have been searching for options but have not had much luck.

I have a R: drive that is holding all of these files i need a macro that can open and save as .xlsm and close.

Any help would be appreciative.

 

[alansidman]

You should be able to modify this

https://www.mrexcel.com/forum/excel...-files-excel-folder-save-them-excel-file.html

 

[Dojorgen]

Thanks i did see that i was not savvy enough to figure out how to modify it to pull from my R: drive

 

[alansidman]

I have amended it to rename the file as an .xlsm file.

The code will prompt you for the Directory. Give it a try.

Option Explicit


Sub foo()
 Dim MyFolder As String
 Dim myfile As String
 Dim folderName As String


 With Application.FileDialog(msoFileDialogFolderPicker)
 .AllowMultiSelect = False
 If .Show = -1 Then


 folderName = .SelectedItems(1)
 End If
 End With


 myfile = Dir(folderName & "\*.txt")


 Do While myfile <> ""
 Workbooks.OpenText Filename:=folderName & "\" & myfile
'save as excel file
 ActiveWorkbook.SaveAs Filename:=folderName & "\" & Replace(myfile, ".txt", ".xlsm")
'use below 3 lines if you want to close the workbook right after saving, so you dont have a lots of workbooks opened
 Application.DisplayAlerts = False
 ActiveWorkbook.Close
 Application.DisplayAlerts = True
 myfile = Dir
 Loop
End Sub

 

[Dojorgen]

Understood, thank you go it working right

Follow up question, is there a way to modify the saveas command to allow me to save myfile under different folders for example:
if file name has 2W1234 in the name it would get saved under 2018/RTP then if file name has 2W5678 in the name it would get saved under 2018/CFM, n.......?