Modify existing Macro to create new txt file and close the file

[btamulis]

I have a macro which works fine but needs final touch...........

at end of macro I need to do two things:

1. Copy the existing workbook as a tab delimited (txt) workbook in a different folder with a different file name.
2. Save the existing workbook (xls) in the current file path - simply overwriting the 'old' file.....end user shouldn't get any prompts - excel simply....closes

Any thoughts?

thank you in advance.......

 

[edmitchell]

Re: Modify existing Macro to create new txt file and close the file..............

Hi

how about

Sub CloseBook()
    'turn off alerts
    Application.DisplayAlerts = False
    
    'if unsaved changes present save Workbook
    If ThisWorkbook.Saved = False Then
        ThisWorkbook.Save
    End If
    
    'Now save file as .TXT
    myFileName = "C:\TEMP\Excel test file.txt"
    
    ActiveWorkbook.SaveAs Filename:=myFileName, FileFormat:=xlCurrentPlatformText
       
    'close Excel
    Application.Quit
    
End Sub

obviously you would need to change the setting of 'myFileName' to what ever folder/file name you need