I have a situation where a section of my macro code needs to be used multiple times (for different inputs), with a separate output Excel file create for each input.

See code attached.

The problem I am facing is that the macro shuts down after processing the 1st line of input. I am guessing it is because the ActiveWorkbook.SaveAs command is creating the output file in the same instance of excel.

How do I keep the original macro workbook open in order to process next line of txt file input? Do I have open a new instance of Excel for each line of input? I also need a way to automatically close the macro workbook after all lines of input have been processed.

Any help would be much appreciated. Thanks.


Sub MainModule
     Dim var1 As String, var2 As String
     open "C:\Mytxt.txt" for Input As #1
     Do While (Not EOF(1))
           Line Input #1, Data
'        Code to split Data into var1, var2
          Call Processing(var1, var2)
    Loop
    Close #1
End Sub

Sub Processing(Var1 As String, var2 As String)
       ActiveWorkbook.SaveAs Filename:= "C:\" & var1 & ".xlsx"
'       code to populate data in multiple worksheets sheets based on var1 & var2
       ActiveWorkbook.Save
       ActiveWindow.Close
End Sub