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
Bookmarks