Opening all txt files in a folder and saving as excel

[Jason]

Hello folks,

I am wondering if anyone could help with the following
task.

I have a folder that contains 50 txt files. I want to
create a macro that opens all 50, and then saves them with
the same file name it currently has and capitalizes it
(i.e. save abc.txt as ABC.xls). Ideally, it should be
smart enough to finish when all the txt files are saved as
excel.

I am not sure if this is possible, but I thought I would
throw it out there anyway.

Thanks,

Jason

 

[Dick Kusleika]

Jason

Try this

Sub OpenAllTxt()

Dim FName As String
Dim sPath As String
Dim wb As Workbook

sPath = "C:\Dick\Tester\"
FName = Dir("C:\Dick\Tester\*.txt")

Do While Len(FName) > 0

Workbooks.OpenText sPath & FName, xlWindows, _
1, xlDelimited, , True, , , , , Array(1, 1)
Set wb = ActiveWorkbook
wb.SaveAs UCase(Left(sPath & FName, _
Len(sPath & FName) - 4)) & ".xls", xlWorkbookNormal
wb.Close False
FName = Dir
Loop

End Sub

You'll need to change the path and OpenText arguments to suit your
situation.