VBA SaveAs

[LFZ12]

I have a pretty simple Macro to open a txt file, clean it up and Save As a .xlsm in its original location. I recorded the macro on one file, I cant figure out how to edit it so it will pull the current file name, not the one I recorded the macro with. Can anyone help? Do I have to insert a module or procedure or can I just edit what I have? The green text is what I need to be changed. I'm new to this and still trying to teach myself. Thank you!

Sub Macro33()
'
' Macro33 Macro
'

'
    ChDir "C:\Users\GZall\Desktop\Laura's Project\Zall Stuff\2022"
    Workbooks.OpenText Filename:= _
        "C:\Users\GZall\Desktop\Laura's Project\Zall Stuff\2022\*PM*.txt", _
        Origin:=437, StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=Array( _
        Array(0, 1), Array(3, 1), Array(10, 1), Array(35, 1), Array(41, 1), Array(105, 1), Array( _
        116, 1)), TrailingMinusNumbers:=True
    Cells.Select
    Selection.AutoFilter
    ActiveSheet.Range("$A$1:$G$7285").AutoFilter Field:=1, Criteria1:=Array( _
        "BID", "SID", "TOT"), Operator:=xlFilterValues
    Selection.Delete Shift:=xlUp
    Columns("A:A").Select
    Selection.Delete Shift:=xlToLeft
    Columns("B:B").Select
    Selection.Delete Shift:=xlToLeft
    Columns("C:C").Select
    Selection.Delete Shift:=xlToLeft
    Columns("D:F").Select
    Selection.Delete Shift:=xlToLeft
    Range("D1").Select
    ActiveWorkbook.SaveAs Filename:= _
        "C:\Users\GZall\Desktop\Laura's Project\Zall Stuff\2022\PM11.12.22.xlsm", _
        FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
    Application.Left = 334
    Application.Top = 133.6
    ActiveWindow.Close
End Sub

 

[Joe4]

Right after this "Open file" command:

Workbooks.OpenText Filename:= _
"C:\Users\GZall\Desktop\Laura's Project\Zall Stuff\2022\*PM*.txt", _
Origin:=437, StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=Array( _
Array(0, 1), Array(3, 1), Array(10, 1), Array(35, 1), Array(41, 1), Array(105, 1), Array( _
116, 1)), TrailingMinusNumbers:=True

you can capture the name of the active file and change the extension from "txt" to "xlsm" like this:

    Dim fName As String
    fName = Left(ActiveWorkbook.Name, Len(ActiveWorkbook.Name) - 3) & "xlsm"
    MsgBox fName

(that MsgBox is not needed in your final code, it is just there to show you what we built).

You can then use it in your Save command, i.e.

ActiveWorkbook.SaveAs Filename:= _
"C:\Users\GZall\Desktop\Laura's Project\Zall Stuff\2022\" & fName, _
FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False

 

[LFZ12]

Right after this "Open file" command:

Workbooks.OpenText Filename:= _
"C:\Users\GZall\Desktop\Laura's Project\Zall Stuff\2022\*PM*.txt", _
Origin:=437, StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=Array( _
Array(0, 1), Array(3, 1), Array(10, 1), Array(35, 1), Array(41, 1), Array(105, 1), Array( _
116, 1)), TrailingMinusNumbers:=True

you can capture the name of the active file and change the extension from "txt" to "xlsm" like this:

    Dim fName As String
    fName = Left(ActiveWorkbook.Name, Len(ActiveWorkbook.Name) - 3) & "xlsm"
    MsgBox fName

(that MsgBox is not needed in your final code, it is just there to show you what we built).

You can then use it in your Save command, i.e.

ActiveWorkbook.SaveAs Filename:= _
"C:\Users\GZall\Desktop\Laura's Project\Zall Stuff\2022\" & fName, _
FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False

Thank You! Perfect!

 

[Joe4]

You are welcome.
Glad I was able to help!