VBA open all text files in excel in a folder and save them as excel file

[linoutandabout]

I have some daily text files in a folder (so about 30 of them each month), which in the end of month, I need to open them up in excel, format them so that I can use the information for my analysis.

I would like to create a macro, to quickly open them all up at once and save them each individually in .xls or .xlsm format.

I am new to VBA and after some research online, I was able to have the files open with the following code. but now I don't know how to proceed further to save them one by one with the same name but in .xls or .xlsm format.

Would really appreciate your help on this!

Thanks!

Sub Opentxtfiles()
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
myfile = Dir
Loop


End Sub

 

[Kemmuniemans]

Just change it like this:

Private Sub CommandButton1_Click()
 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", ".xls")
'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

 

[linoutandabout]

Thank you so much, Kemmuniemans!

It works like a charm!!!

 

[dcng2691]

Hello
I was looking a code like this on internet, but i have to do some modifications.

Every month y have different folders that contains csv and txt files.
The txt files have to be separated with the text to columns option and the csv files data comes separated with '|'.

This is what i diid with the code but I get an error saying that i cannot save the file with the same name as the original one.

Dim MyFolder As String
Dim myfile As String
Dim folderName As String

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

folderName = .SelectedItems
End If
End With

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

Do While myfile <> ""
Workbooks.OpenText Filename:=folderName & "\" & myfile, Origin:=437, StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 1), _
Array(4, 1), Array(6, 1), Array(12, 1), Array(15, 1), Array(21, 1), Array(24, 1)), TrailingMinusNumbers:=True
Call FormatoArchivosTxt


'save as excel file
ActiveWorkbook.SaveAs Filename:=folderName & "\" & Replace(myfile, ".txt", ".xlsx")
'use below 3 lines if you want to close the workbook right after saving, so you dont have a lots of workbooks opened


' ActiveWorkbook.Close
' myfile = Dir
Loop