VBA: How to save excel file as a separate .txt file

[iand5]

Hi, I currently have the following code to save an Excel file as a .txt file.

Sub Macro1()

'Saves the Activesheet to the Desktop as a text file with a different extension
Dim newFile As String, fName As String, gName, fileSaveName
    gName = "FGM_"
    newFile = gName & Format$(Date, "dd-mm-yyyy")
    fileSaveName = Application.GetSaveAsFilename(newFile, "Text (MS-DOS) (*.txt), *.txt")
    ThisWorkbook.SaveAs fileSaveName, xlTextMSDOS
End Sub

The problem is that when the .txt file is saved, it overrides the excel file.

How can I save the .txt file so that is separate from the excel file?

Thanks

 

[JLGWhiz]

You could try the SaveCopyAs method instead of SaveAs.

 

[iand5]

I get a compiler error.

"Wrong number of arguments or invalid property assignment".

 

[JLGWhiz]

I get a compiler error.

"Wrong number of arguments or invalid property assignment".

Yep, I was just going to amend my post and say that would not work because it does not allow for a different file format. It works great for the same format though. I guess the only solution in your case is to add a line of code to open the original workbook and a line to close the new one.

 

[iand5]

Sorry, I'm new with VBA.

Could you explain a bit more detailed?

Thank you so much!

 

[JLGWhiz]

Sub Macro1()
'Saves the Activesheet to the Desktop as a text file with a different extension
Dim newFile As String, fName As String, gName As String, fileSaveName As String, oldFile As String
oldFile = ThisWorkbook.Name [COLOR=#ff0000]'capture the current file name
  [/COLOR]  gName = "FGM_"
    newFile = gName & Format$(Date, "dd-mm-yyyy")
    fileSaveName = Application.GetSaveAsFilename(newFile, "Text (MS-DOS) (*.txt), *.txt")
    ThisWorkbook.SaveAs fileSaveName, xlTextMSDOS
Workbooks.Open oldFile [COLOR=#ff0000]'Re-open original file[/COLOR]
End Sub

 

[iand5]

Awesome! Thanks for the help.

 

[JLGWhiz]

Awesome! Thanks for the help.

You're Welcome,
regards, JLg