Excel stops working when I use Workbook.close to close another spreadsheet

Question

Thank you in advance those who try to help me.

I have a somewhat complex programming where I use a file to initialize 2 macros in other files.

When closing the files, I open a txt file in excel, paste the contents of my spreadsheet in this new file, save the file and close as sequence below:

Open arquivotxt.txt in excel (arquivotxt.xls) Paste content into arquivotxt.xls Save arquivotxt.xls to txt (arquivotxt.txt) Close Workbook arquivotxt.txt

At this last moment my excel closes and appears a message saying that Excel is not working, followed by a message, "We are trying to restart Excel".

Below the code where the error occurs:

Sub-closure()

Dim racffim As String
Dim nometxt As String
Dim datatratada As String
Dim final As Integer
Dim nomeplant As String
Dim b As Integer
Dim wb As Workbook
Dim Demora As Long

racffim = Environ("UserName")


Application.EnableEvents = True
Set wb = Workbooks("MTT_" & racffim & ".xlsm")
wb.Activate
wb.Save
Application.Run "MTT_" & racffim & ".xlsm!Auto_Close"
wb.Close
Application.EnableEvents = False


'Completa as atividades que não foram finalizadas com o hr de fechamento da planilha - Coment de Finalizacao Automatica
ThisWorkbook.Activate
Range("A1").Select
Do While ActiveCell.Value <> ""
    If ActiveCell.Offset(0, 2) = "" Then
        ActiveCell.Offset(0, 2).Value = Now
        ActiveCell.Offset(0, 3).Value = ActiveCell.Offset(0, 2).Value - ActiveCell.Offset(0, 1).Value
        ActiveCell.Offset(0, 5).Value = "Automatica Final"
    End If
    ActiveCell.Offset(1, 0).Select
Loop
Range("A1").Select
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

Application.DisplayAlerts = True


'Nome do arquivo de base de dados
datatratada = Left(Date, 2) & Mid(Date, 4, 2) & Right(Date, 4)
nometxt = Left(ThisWorkbook.Path, Len(ThisWorkbook.Path) - 7) +   "Base_de_Arquivos" + "\" + racffim + "_" + datatratada
Application.DisplayAlerts = False
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''


******ERRO ACONTECE DENTRO DESTE IF********** 
'Se arquivo de base de dados já existe no diretório:
If Dir(nometxt & ".txt") <> vbNullString Then

    'Copia todos os dados do timesheet
    ThisWorkbook.Activate
    Sheets("bancodados").Range("A2:F2").Select
    If ActiveCell.Offset(1, 0).Value <> "" Then
        Range(Selection, Selection.End(xlDown)).Select
    End If
    Selection.Copy

    'Abre arquivo existente de base de dados
    Workbooks.OpenText Filename:=nometxt & ".txt", Origin _
    :=xlMSDOS, StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, Tab:=True, FieldInfo:=Array(1, 1), TrailingMinusNumbers:=True

    Set bkt = ActiveWorkbook
    DoEvents

    'Encontra a ultima linha vazia do arquivo, cola as marcações, salva o arquivo e fecha (sem janela de salvar)
    Range("A1").Select
    If ActiveCell.Offset(1, 0).Value <> "" Then
        Selection.End(xlDown).Select
    End If
    ActiveCell.Offset(1, 0).Select
    ActiveSheet.Paste
    bkt.SaveAs Filename:=nometxt, FileFormat:=xlText, CreateBackup:=False
    bkt.Close False      ******O PROGRAMA TRAVA AQUI********
    Set bkt = Nothing
    ''''''''''''''''''''''''''''''''''''''''''''''''''''''

'Se arquivo não existe:
Else
    'Salva planilha bancodedados como arquivo de texto (sem janela de salvar)
    ActiveWorkbook.SaveAs Filename:=nometxt, FileFormat:=xlText, CreateBackup:=False
End If
'''''''''''''''''''''''''''''''''''''

MsgBox ("Marcações Salvas")

 Application.IgnoreRemoteRequests = False

'Fecha workbook caso existam outros workbooks abertos ou fecha a application caso seja o unico workbook aberto
    Application.DisplayAlerts = True
    ThisWorkbook.Saved = True
    'If Workbooks.Count > 1 Then
        ThisWorkbook.Close False
    'Else
        'Application.Quit
    'End If

End Sub

Does anyone have any idea what’s going on?

Thank you!

Answer 1

Hello, I usually use the command to close other sheets as follows:

First store in a variable the name of the spreadsheet I want to close:

FileName = ActiveWorkbook.Name

After performing all procedures, I ask you to close as follows:

Workbooks(FileName).Close SaveChanges:=False