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
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