VBA to select file in folder

Jujitsu51

New Member
Joined
Dec 14, 2020
Messages
2
Office Version
  1. 365
Platform
  1. Windows
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
 

Some videos you may like

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

Anthony47

Well-known Member
Joined
Mar 29, 2006
Messages
2,137
I guess this version will work:
VBA Code:
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
 
Solution

Jujitsu51

New Member
Joined
Dec 14, 2020
Messages
2
Office Version
  1. 365
Platform
  1. Windows
Anthony
I will give it a try tonight.
I appreciate the help
Thanks
 

Watch MrExcel Video

Forum statistics

Threads
1,127,611
Messages
5,625,833
Members
416,138
Latest member
Pizzaman22

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top