Modify Macro to Select 1 .TXT File at a time, do changes on it (tab delimeter+column to text) to convert to .XLS file+save the file then select other

[yferrer]

Hi hope someone can help me.

I need help to Modify the Macro 1 or the Macro 2
below to Select 1 .TXT File at a time inside a folder, do changes on it (tab delimeter+column to text) on the conversion process (to convert) to .XLS file, then save the file, then select other file and continue. If you have other Macro that works for the same purpose that will be ok too, the importance is not to limit the location of the file to one user, each person needs to be able to run the macro without modifying the need to modify their username or user id on the location of their folder. Example on Macro 2 : "FileToOpen = "C:\Users\ID2040\Desktop\Foldername\Filename.txt" this does not allow other users on other computers to use my Macro.

Macro 1
Sub TXTtoXLS()
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


Macro 2
Sub OpenTXTFileByNameAndFormat()
Dim FileToOpen As Variant

FileToOpen = "C:\Users\E425849\Desktop\Foldername\Filename.txt"
Workbooks.OpenText _
Filename:=FileToOpen, _
DataType:=xlDelimited, _
Tab:=True
'Add Filter

Rows("1:1").Select
Selection.AutoFilter
Cells.Select
Cells.EntireColumn.AutoFit

'Add Color

Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 15773696
.TintAndShade = 0
.PatternTintAndShade = 0
End With
End Sub