VBA to select file in folder

[Jujitsu51]

Hi
I am new to this form but not new to much of what I have learned from Mr Excel videos.
My knowledge of Excel VBA is in its infancy stage and have used the recorder for my basic work.
Peirodically, a file is saved into a folder that I need to convert to xlsm.
However, the file name will change (since it is for a different period), but all else remains the same.
From a blank Excel sheet, I executed the macro below and this is what the Wizard recorded (except I changed the user name):

Sub TB_converter()
'
' TB_converter Macro
' Trial Balance converter from text to Xlsm
'
' Keyboard Shortcut: Ctrl+Shift+T
'
ChDir "C:\Users\John Doe\Desktop\GENERAL LEDGER\TB to be converted"
Workbooks.OpenText Filename:= _
"C:\Users\John Doe\Desktop\GENERAL LEDGER\TB to be converted\P01 ONLY.txt" _
, Origin:=xlWindows, StartRow:=1, DataType:=xlFixedWidth, FieldInfo:= _
Array(Array(0, 1), Array(4, 1), Array(20, 1), Array(34, 1), Array(37, 1), Array(48, 1), _
Array(69, 1), Array(71, 1), Array(72, 1), Array(90, 1), Array(91, 1), Array(110, 1), Array( _
111, 1), Array(130, 1), Array(132, 1)), TrailingMinusNumbers:=True
ActiveWorkbook.SaveAs Filename:= _
"C:\Users\John Doe\Desktop\GENERAL LEDGER\TB to be converted\P01 ONLY.xlsm" _
, FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
End Sub

The attached document is the Module that was created by the Wizard for the file that I pointed to (P01 ONLY.txt).

My goal is to execute the macro and have it go to the folder "GENERAL LEDGER\TB to be converted\" and execute the macro on the only file in that folder, then save it using the same name but saving is as an xlsm file.

Can someone help me?
Thank

 

[Anthony47]

I guess this version will work:

Sub TB_converter()
'
' TB_converter Macro
' Trial Balance converter from text to Xlsm
'
' Keyboard Shortcut: Ctrl+Shift+T
Dim myPath As String, myFile As String
'
myPath = "C:\Users\John Doe\Desktop\GENERAL LEDGER\TB to be converted\"   '** note the final \
myFile = Dir(myPath & "*.txt")
If myFile = "" Then
    MsgBox ("No files to process")
    Exit Sub
End If
Workbooks.OpenText Filename:= _
myPath & myFile _
, Origin:=xlWindows, StartRow:=1, DataType:=xlFixedWidth, FieldInfo:= _
Array(Array(0, 1), Array(4, 1), Array(20, 1), Array(34, 1), Array(37, 1), Array(48, 1), _
Array(69, 1), Array(71, 1), Array(72, 1), Array(90, 1), Array(91, 1), Array(110, 1), Array( _
111, 1), Array(130, 1), Array(132, 1)), TrailingMinusNumbers:=True
ActiveWorkbook.SaveAs Filename:= _
myPath & Replace(myFile, ".txt", ".xlsm", , , vbTextCompare) _
, FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
End Sub

Bye

 

[Jujitsu51]

Anthony
I will give it a try tonight.
I appreciate the help
Thanks