Create txt crashes if ran second time.

[Compchro]

Hello, I have a process where users combine data in a single spreadsheet. That data then needs sent to a text file where it will imported into a server database process. I can get the process to create the text file, save changes and then close the text file with a workbook.close statement assigned to a macro button. My issue is if that macro button is clicked twice excel crashes. It appears to hit a snag on the Workbooks("Import_ETL.txt").Close SaveChanges:=True script as it seems to get confused which active workbook since the macro also creates. I've tried multiple variations of close scripts plus tried splitting it out with separate create and close macros but it's still crashing if ran multiple times. At the end of the day I'm attempting to make this user break proof so regardless if ran multiple times it will still create a text file as I only need the latest greatest version. Hope that makes sense. For full disclosure the code below was hobbled together from various forum posts. Values in TEST SALESDB ETL source file are text but any values passed should work. Macro also needs sub-folder in this files location called Import ETL as export will drop there.

Sub mcr4_1_Create_ETL_TXT()
'
' mcrCreate_ETL_TXT Macro

'Deletes existing Import ETL if one exists
Dim aFile As String
aFile = ThisWorkbook.Path & "\Import ETL\Import_ETL.txt"
If Len(Dir$(aFile)) > 0 Then
Kill aFile
End If

'Creates new Import ETL
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Worksheets("TEST SALESDB ETL").Range("A:A").Copy
Sheets.Add.Name = "TEST ETL"
Selection.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
Sheets("TEST ETL").Move
ActiveWorkbook.SaveAs Filename:= _
ThisWorkbook.Path & "\Import ETL\Import_ETL.txt", FileFormat:= _
xlText, CreateBackup:=False

'THIS IS THE BREAK, IF REMOVED IT WORKS WITH SECOND AND SUBSEQUENT BUTTON CLICK
Workbooks("Import_ETL.txt").Close SaveChanges:=True


MsgBox "ETL file has been created in the Import ETL folder"
Application.DisplayAlerts = True
Application.ScreenUpdating = True

End Sub

 

[DanteAmor]

In this line you have already saved the file

[COLOR=#333333]ActiveWorkbook.SaveAs Filename:= _[/COLOR]
[COLOR=#333333]ThisWorkbook.Path & "\Import ETL\Import_ETL.txt", FileFormat:= _[/COLOR]
[COLOR=#333333]xlText, CreateBackup:=False[/COLOR]

Then it is no longer necessary to save again. Try it like this:

[COLOR=#333333]Workbooks("Import_ETL.txt").Close SaveChanges:=[/COLOR][COLOR=#ff0000]False[/COLOR][COLOR=#333333][/COLOR]

Or

[COLOR=#333333]ActiveWorkbook.close False[/COLOR]

 

[Compchro]

Thank you DanteAmor. You are correct that the second save statement is not necessary. However, it still crashes if ran a second time using a click button tied to the macro. I'm already looking for another option as I don't think it will work with current design.

 

[DanteAmor]

Thank you DanteAmor. You are correct that the second save statement is not necessary. However, it still crashes if ran a second time using a click button tied to the macro. I'm already looking for another option as I don't think it will work with current design.

I executed the code several times and I have no problems.
But you must remove this line from the code.
' Workbooks("Import_ETL.txt").Close SaveChanges:=True

Sub mcr4_1_Create_ETL_TXT()
'
' mcrCreate_ETL_TXT Macro


'Deletes existing Import ETL if one exists
    Dim aFile As String
    aFile = ThisWorkbook.Path & "\Import ETL\Import_ETL.txt"
    If Len(Dir$(aFile)) > 0 Then
        Kill aFile
    End If
    
    'Creates new Import ETL
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    Worksheets("TEST SALESDB ETL").Range("A:A").Copy
    Sheets.Add.Name = "TEST ETL"
    Selection.PasteSpecial Paste:=xlPasteValues
    Application.CutCopyMode = False
    Sheets("TEST ETL").Move
    'ActiveWorkbook.SaveAs Filename:= _
        ThisWorkbook.Path & "\Import ETL\Import_ETL.txt", FileFormat:= _
        xlText, CreateBackup:=False
    ActiveWorkbook.SaveAs Filename:=aFile, FileFormat:= _
        xlText, CreateBackup:=False
    
    ActiveWorkbook.Close False
    
    MsgBox "ETL file has been created in the Import ETL folder"
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
End Sub