File Save As - multiple instances of Excel

[SH14]

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.

Please Login or Register  to view this content.

[mrice]

Try SaveCopyAs

[snb]

Please Login or Register  to view this content.

I can't see where you get var1 & var 2 from.

[SH14]

snb - Thanks for your suggestion. Sorry if my question was not clear - I already have the code to populate var1 & var2 based on the input file.
What I am looking for is a way to keep the macro file open while creating new optput files for each line of input. My macro is closing after processing the 1st line of input.


mrice - your suggestion did not work. "I got a Compile error in hidden module:Module1" when I tried SaveCopyAs.

[snb]

'optput' is new to me....

Please Login or Register  to view this content.