File Save As - multiple instances of Excel

**SH14** · 09-01-2011, 01:57 PM

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

**mrice** · 09-01-2011, 02:45 PM

Try SaveCopyAs

**snb** · 09-01-2011, 02:55 PM

Sub MainModule
 open "C:\Mytxt.txt" for Input As #1
   sq=split(Input(LOF(1),#1),vbcrlf)
 Close #1
 ActiveWorkbook.SaveAs "C:\test.xlsx",activeworkbook.fileformar
End Sub

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

**SH14** · 09-02-2011, 09:13 AM

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** · 09-02-2011, 09:20 AM

'optput' is new to me....

Sub MainModule
 open "C:\Mytxt.txt" for Input As #1
  sq=split(Input(LOF(1),#1),vbcrlf)
 Close #1
 
 for j=0 to ubound(sq)
  open "C:\Mytxt" & j" &".txt" for Output As #1
   print #1, sq(j)
  Close #1
 next
End Sub