Excel stops working when I use workbook.close to close another worksheet

0

Thank you in advance for trying to help me.

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

At the moment of 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 follows:

Open archivotxt.txt in excel (archivotxt.xls) Paste content in archivotxt.xls Save archivotxt.xls in txt (archivotxt.txt) Close workbook archivotxt.txt

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

Below is 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!

    
asked by anonymous 19.06.2017 / 17:26

1 answer

0

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

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

FileName = ActiveWorkbook.Name

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

Workbooks(FileName).Close SaveChanges:=False
    
26.07.2017 / 15:56